天道酬勤,学无止境

How can I convert a range to a string (VBA)?

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting as possible (i.e. it needs to look like a table or list, not just a string of characters). I've tried working with CStr(), as well as converting from a range to an array and then to a string, but I just get errors.

Edit: Code attempt

Dim email_answer As Integer
email_answer = MsgBox("Do you want to be emailled a copy of this schedule?", vbYesNo)
If email_answer = vbYes Then

    Dim wb As Workbook
    Dim to_send As Range
    to_send = Range("D3", "D10")

    If Val(Application.Version) < 14 Then Exit Sub

    Set wb = ActiveWorkbook
    With wb
        MailFromMacWithMail body content:=CStr(to_send), _
                    mailsubject:="Schedule", _
                    toaddress:="email address", _
                    ccaddress:="", _
                    bccaddress:="", _
                    attachment:=.FullName, _
                    displaymail:=False
    End With
    Set wb = Nothing
End If

评论

To make a comma separated list of cell values in a range:

Function RangeToString(ByVal myRange as Range) as String
    RangeToString = ""
    If Not myRange Is Nothing Then
        Dim myCell as Range
        For Each myCell in myRange
            RangeToString = RangeToString & "," & myCell.Value
        Next myCell
        'Remove extra comma
        RangeToString = Right(RangeToString, Len(RangeToString) - 1)
    End If
End Function

You could add extra functionality like inserting a semicolon instead of a comma if the row number increases.

To use this function:

Sub AnySubNameHere()
    Dim rng As Range
    Set rng = ActiveSheet.Range("A3:A10")

    Dim myString as String
    myString = RangeToString(rng)
End Sub

you could use this function:

Function Rang2String(rng As Range) As String
    Dim strng As String
    Dim myRow As Range
    With rng
        For Each myRow In .Rows
            strng = strng & Join(Application.Transpose(Application.Transpose(myRow.value)), "|") & vbLf
        Next
    End With
    Rang2String = Left(strng, Len(strng) - 1)
End Function

which would return a string with linefeed character as range rows separator and pipes ("|") as columns separator

I know this question is already almost a year old, but I found a quick solution that works for me: You do have to create a reference to Microsoft Forms 2.0 Object Library to use the DataObject.

Public Function GetStringFromRange(RNG As Range) As String
    Dim DOB As New MSForms.DataObject
    RNG.Copy
    DOB.GetFromClipboard
    GetStringFromRange = DOB.GetText
End Function

Any one of these functions will do it for you.

Function ConCatRange2(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
Next
ConCatRange2 = Left(sbuf, Len(sbuf) - 1)
End Function

OR

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
    If k <> 1 Then
        mergem = mergem & "," & rr.Value
    End If
    k = 2
Next
End Function

OR

Function spliceUm(r As Range) As String
spliceUm = ""
For Each rr In r
spliceUm = spliceUm & rr.Value & ";"
Next
End Function

There is a much easier way. Assuming the variable rng is a range, then writing:

rng = Left(rng,Len(rng))

will miraculously turn rng into a string.

You can use the following solution to convert a range to a string in VBA:

Sub convert()

Dim rng As Range, cell As Range

Dim filter As String

filter = ""

Set rng = Selection

For Each cell In rng

    If Not cell Is Nothing Then
        filter = """" & cell & """" & "," & filter
    End If

Next cell

End Sub

Given the apparent need to iterate the range, I'd imagine it'd be considerably quicker to copy the range to an array first, and build the string by looping the array.

This does the job, even with the Range in another Worksheet

Function RangeToString(ByVal myRange As range) As String
    RangeToString = ""
    If Not myRange Is Nothing Then
        RangeToString = "=" & myRange.Worksheet.Name & "!" & myRange.Address
    End If
End Function

No need to iterate.

Application.Textjoin(Chr(10),TRUE,Range("D3", "D10"))

or

Join(Application.Transpose(Range("D3:D10")),Chr(10))

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 如何在Excel VBA中将整数转换为字符串?(How Do I Convert an Integer to a String in Excel VBA?)
    问题 如何在Excel VBA中将整数值“ 45”转换为字符串值“ 45”? 回答1 CStr(45)就是您所需要的(“转换字符串”功能) 回答2 尝试CStr()函数 Dim myVal as String; Dim myNum as Integer; myVal = "My number is:" myVal = myVal & CStr(myNum); 回答3 大多数情况下,您无需“转换”; VBA将为您执行安全的隐式类型转换,而无需使用CStr之类的转换器。 下面的代码可以正常工作,因为该变量的类型为字符串,并且隐式类型转换会自动为您完成! Dim myVal As String Dim myNum As Integer myVal = "My number is: " myVal = myVal & myNum 结果: “我的电话是:0” 您甚至不必花哨,这也可以: Dim myString as String myString = 77 “ 77” 您将需要转换的唯一时间是当变量类型是不明确的(例如,类型变体或Cell的Value (这是变))。 即使这样,如果要与另一个String变量或常量复合,也不必使用CStr函数。 像这样: Sheet1.Range("A1").Value = "My favorite number is " & 7 “我最喜欢的号码是7”
  • 将VBA转换为VB6并创建一个.dll-操作方法-提示,技巧和风险(Convert vba to vb6 and create a .dll - how to - hints, tips and risks)
    问题 我应该将用VBA(Excel)编写的大量代码转换为VB6。 但是我真的不知道我要照顾什么或从哪里开始。 因此,很高兴从VB6专家那里获得一些提示。 我已经安装了MS Visual Studio,并玩了一些。 但是我不是VB6专家,所以我真的不知道该怎么做。 最终目标是将所有VBA代码(当前放在一个excel vba宏中)放入VB6项目中,并从中创建一个.dll。 这个.dll应该被excel引用,并且excel应该像现在一样运行:-) 例如,我必须怎么做才能将此vba代码转换为VB6。 Public Function getParameterNumberOfMaterial() As Integer 10 On Error Resume Next Dim a As String 20 a = Sheets("Parameters").name 30 If IsNumeric(Application.Worksheets(a).range("C3").Value) Then 40 If Application.Worksheets(a).range("C3").Value > 0 Then 50 getParameterNumberOfMaterial = Application.Worksheets(a).range("C3").Value 60 Else 70 MsgBox
  • 使用vba中的列号创建excel范围?(Create excel ranges using column numbers in vba?)
    问题 如何使用列号而不是字母在 vba 中创建范围? 回答1 以下是选择范围 A1 的两种解决方案。 Cells(1,1).Select '(row 1, column 1) Range("A1").Select 另请查看此链接; http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm 我们强烈建议您使用 Range 而不是 Cells 来处理单元格和单元格组。 它使您的句子更加清晰,并且您不必记住列 AE 是第 31 列。 只有当您想要选择工作表的所有单元格时,才会使用单元格。 例如: Cells.Select 要选择所有单元格,然后清空您将使用的值或公式的所有单元格: Cells.ClearContents —— “单元格”在动态设置范围和使用计数器循环范围时特别有用。 使用字母作为列号定义范围在短期内可能更透明,但它也会使您的应用程序更加严格,因为它们是“硬编码”表示 - 不是动态的。 感谢金吉森 回答2 要引用单元格范围,您可以使用 Range(Cell1,Cell2),示例: Sub RangeTest() Dim testRange As Range Dim targetWorksheet As Worksheet Set targetWorksheet = Worksheets("MySheetName")
  • 如何从Excel VBA宏生成XML?(How to generate XML from an Excel VBA macro?)
    问题 因此,我有大量以Excel电子表格形式提供给我们的内容。 我需要获取该内容并将其推送到另一个系统中。 另一个系统从XML文件获取其输入。 我可以手动完成所有操作(并相信我,管理让我做到这一点没有问题!),但是我希望有一种简单的方法来编写Excel宏,该宏将生成我需要的XML。 对于我来说,这似乎是一个更好的解决方案,因为这是一项需要定期重复的工作(我们将在Excel工作表中获得很多内容),并且有一个批处理工具可以为我们做到这一点很有意义。 但是,我以前从未尝试过从Excel电子表格生成XML。 我有一些VBA知识,但是我是XML的新手。 我猜我在谷歌搜索中遇到的问题是我什至不知道Google会做什么。 任何人都可以给我一点指导以帮助我入门吗? 我的想法听起来像是解决此问题的正确方法,还是我忽略了明显的事情? 谢谢StackOverflow! 回答1 您可能想考虑使用ADO-工作表或范围可以用作表。 Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adPersistXML = 1 Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") ''It wuld probably be better to use
  • 如何以相反的顺序显示子字符串但在 VBA 中从左到右读取(How to display sub string in reverse order but read from left to right in VBA)
    问题 我正在 VBA 中处理 .txt 文件。 在其他任务中,我需要读取一个表示日期的字符串并在 Excel 中显示实际日期。 .txt 文件中的日期字符串看起来像“190223” 这代表 23/02/2019 我的挑战是完成这项工作。 到目前为止我所做的是: ' ... loop With ActiveWorkbook.Worksheets(1) ' Other statements here ' Event date time .Range("N" & i).Value = StrReverse(Mid(.Range(keyword.Offset(0, 4).Address), 1, 2) & _ "/" & Mid(.Range(keyword.Offset(0, 4).Address), 3, 2) & _ "/" & Mid(.Range(keyword.Offset(0, 4).Address), 5, 2)) End With 但我得到了不需要的输出: 32/20/91 ' For a date string 190223 the desired output should be 23/02/19 任何帮助将非常感激。 提前致谢。 回答1 将其转换为真实日期 您必须提取该字符串的年、月和日,然后将其转换为实际日期。 然后您可以将日期格式化为您喜欢的任何日期格式。
  • 32000行后VBA宏崩溃(VBA Macro crashes after 32000 rows)
    问题 我有一个VBA宏,它基于在3列的单元格中查找值,将行从一个工作表复制到另一个工作表。 该宏有效,但到达行32767时崩溃。该行中没有公式或特殊格式。 此外,我已经将该行取出,但是在该行号上仍然崩溃。 这是excel的限制吗? 工作表中有大约43000个正在处理中 因此,我问宏有什么问题,以及如何使它到达工作表的末尾: Dim LSearchRow As Integer Dim LCopyToRow As Integer Dim wks As Worksheet On Error GoTo Err_Execute 对于工作表中的每个工作周 LSearchRow = 4 LCopyToRow = 4 ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count) Set wksCopyTo = ActiveSheet wks.Rows(3).EntireRow.Copy wksCopyTo.Rows(3) While Len(wks.Range("A" & CStr(LSearchRow)).Value) > 0 If wks.Range("AB" & CStr(LSearchRow)).Value = "Yes" And wks.Range("AK" & CStr(LSearchRow)).Value = "Yes
  • Vba convert Excel range into picture and send to Outlook, write text into body
    I would like to copy a range from protected Excel sheet and paste it into Outlook as a Picture, write some text and display it. My code below is pasting the text first of all, and then the Picture, but at the same time deleting the text. But I want both, text and under text the picture (range converted into a picture from Excel). Can anybody help me how to get it? Sub Send_Email() Dim r As Range Set r = Range("NR7:OD39") Dim outlookApp As Outlook.Application Set outlookApp = CreateObject("Outlook.Application") Dim OutMail As Outlook.MailItem Set OutMail = outlookApp.CreateItem(olMailItem) Dim
  • Excel VBA: Range to String Array in 1 step
    I know you can easily take a range of cells and slap them into a Variant Array but I want to work with a string array (because it's single-dimensional and takes less memory than a Variant array). Is there any way to automatically convert a range into a string array? Right now I am using a function that will take the range and save the values in a variant array, then convert the variant array to a string array. It works nice , but I'm looking for a way to go directly from the range to string array. Any help would be greatly appreciated. Function RangeToArray(ByVal my_range As Range) As String()
  • Excel VBA - convert range with pictures and buttons to HTML
    I wrote a function that turns an excel range into HTML for further use in an email body. The problem is that I now want to add pictures and buttons to the range and have it then taken over into the email body. How I can get excel to address objects in the range and convert them over as well? Thanks Function Range to HTML Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2013 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range
  • Excel VBA - 将用户表单中输入的字符串转换为数字(Excel VBA - Convert string entered in user form to number)
    问题 我有一个 excel 用户表单,用户可以在其中输入数字,当这些数字被输入到电子表格中时,它们会出现并通知这是一个以文本形式存储的数字。 =SUM(H6:H13) 显示零结果。 我试过NumCrtn = cLng(NumCrtn) - 不会将单元格更改为数字,公式仍然显示为零。 我试过NumCrtn = Val(NumCrtn) - 不会将单元格更改为数字,公式仍然显示为零。 我已经尝试将 copy 和 paste.special 复制到一个值,但也没有将其更改为数字。 不知道该怎么办。 帮助! 回答1 试试这个: With Range("H6:H13") .NumberFormat = "0" .Value = .Value End With 编辑: 另一种解决方案。 基于 Pradeep Kumar 的建议,该建议涉及在输入数据之前准备范围,将代码更改为这样的内容 Private Sub UserForm_Initialize() Dim aCell As Range Range("H6:H13").NumberFormat = "0" 'This is to cater for any previous values if filled in For Each aCell In Range("H6:H13") aCell.Formula = aCell.Value
  • 如何将范围 obj 变量传递给 Excel VBA(2016)中的子项[重复](How do I pass a range obj variable to a sub in Excel VBA (2016) [duplicate])
    问题 这个问题在这里已经有了答案: 数组参数必须是 ByRef (1 个回答) 4年前关闭。 鉴于以下代码:我似乎无法成功地将范围对象变量从一个子函数传递到另一个子函数。 我花了一整天的时间研究和实验,然后才放下骄傲来到这里。 请阅读下面的评论,并回答您对为什么最后两行不起作用的任何想法。 Public Sub doSomethingToRows(ROI As Range) *'do Something with the cell values within the supplied range* End Sub ' Public Sub testDoAltRows() Dim RegionOfInterest As Range 'is this an object or not? '*The following yields: Class doesn't support Automation (Error 430)* '*Set RegionOfInterest = New Worksheet 'this just gives an error* Set RegionOfInterest = Worksheets("Sheet1").Range("A1") RegionOfInterest.Value = 1234.56 '*okay, updates cell A1* Set
  • Vba将Excel范围转换为图片并发送到Outlook,将文本写入正文(Vba convert Excel range into picture and send to Outlook, write text into body)
    问题 我想从受保护的 Excel 工作表中复制一个范围并将其作为图片粘贴到 Outlook 中,编写一些文本并显示它。 我下面的代码首先粘贴文本,然后粘贴图片,但同时删除文本。 但我想要文字和文字图片(范围从 Excel 转换为图片)。 有人可以帮我如何得到它吗? Sub Send_Email() Dim r As Range Set r = Range("NR7:OD39") Dim outlookApp As Outlook.Application Set outlookApp = CreateObject("Outlook.Application") Dim OutMail As Outlook.MailItem Set OutMail = outlookApp.CreateItem(olMailItem) Dim StrFileName As String Application.DisplayAlerts = False Application.ScreenUpdating = False Sheets("table1").Select ActiveSheet.Unprotect Password:="blabla" ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8 r.Select r.Copy
  • 将列正值转换为负值 VBA(Convert column positive values to negative values VBA)
    问题 我知道这已经被问过好几次了,但我很困惑如何将我的列 L:L 的负值放入循环中。 我无法让它工作。 我已经尝试了我研究过的一切。 我很感激任何帮助。 Option Explicit Sub Importpaymentsales() Dim fpath As Variant Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim Text As String On Error GoTo terminatemsg Set wb = Excel.ActiveWorkbook Set ws = Excel.ActiveSheet fpath = Application.GetOpenFilename(Filefilter:="text Files(*.txt; *.txt), *.txt; *.txt", Title:="Open Prepayment Sales Report") If fpath = False Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Text = getTextfileData(fpath) If Len(Text) Then ProcessData Text AdjustDates
  • Convert column positive values to negative values VBA
    I know this has been asked several times but I'm quite confused on how to put the negative values for my column L:L in a loop. I can't get it to work. I've tried everything I researched. I'd appreciate any help. Option Explicit Sub Importpaymentsales() Dim fpath As Variant Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim Text As String On Error GoTo terminatemsg Set wb = Excel.ActiveWorkbook Set ws = Excel.ActiveSheet fpath = Application.GetOpenFilename(Filefilter:="text Files(*.txt; *.txt), *.txt; *.txt", Title:="Open Prepayment Sales Report") If fpath = False Then Exit Sub Application
  • 如何快速删除两个Excel工作表之间的重复项VBA(How do I delete duplicates between two excel sheets quickly vba)
    问题 我正在使用vba,我有两张纸,一张被命名为“ Do Not Call”,并且在A列中有大约80万行数据。我想使用此数据来检查第二张纸中名为“ Sheet1”的第一列。 如果找到匹配项,我希望它删除“ Sheet1”中的整个行。 我对在类似问题中发现的代码进行了定制:将Excel公式交叉引用2张纸,从一张纸中删除重复的纸并运行它,但是什么也没有发生。 我没有收到任何错误,但没有运行。 这是我目前正在尝试的代码,不知道为什么它不起作用 Option Explicit Sub CleanDupes() Dim wsA As Worksheet Dim wsB As Worksheet Dim keyColA As String Dim keyColB As String Dim rngA As Range Dim rngB As Range Dim intRowCounterA As Integer Dim intRowCounterB As Integer Dim strValueA As String keyColA = "A" keyColB = "I" intRowCounterA = 1 intRowCounterB = 1 Set wsA = Worksheets("Do Not Call") Set wsB = Worksheets("Sheet1") Dim
  • VBA 将字符串转换为日期(VBA Convert String to Date)
    问题 我在 Excel 工作表中有以下格式的数据: ItemCode DeliveryDate 5456987 24.01.2009 5456988 5456989 12.24.2009 5456990 12/24/2009 我已经将 DeliveryDate 的值存储在一个数组中。 我需要决定日期的基础知识,然后将结果打印在新的工作表中。 所以我必须将值转换为数组: Dim current as Date, highest as Date, result() as Date For Each itemDate in DeliveryDateArray current = CDate(itemDate) if current > highest then highest = current end if ' some more operations an put dates into result array Next itemDate 'After activating final sheet... Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result) 不幸的是,CDate() 函数抛出错误: 运行时错误“13”: 类型不匹配 VBA中是否有一个函数可以:
  • vba Excel 2010:将字符串数组转换为int数组(vba Excel 2010: convert string array into int array)
    问题 Dim myarray(2) as Variant myarray(0)="3" myarray(1)="4" myarray(2)="5" myarray(3)="9" 我希望它成为 myarray(0)=3 myarray(1)=4 myarray(2)=5 myarray(3)=9 有什么方法可以使它仅在1行中没有循环? 这是用于Excel 2010。 我想要这样的东西,但是不起作用: intArray = Array.ConvertAll(stringArray , Function(str) Int32.Parse(str)) 回答1 遍历数组并一一转换值足够快。 这是一段代码,显示转换循环相对于执行单元I / O的速度: Private Const I_MAX = 10000 Private Const J_MAX = 200 Private Declare Function GetTickCount Lib "kernel32.dll" () As Long Sub ticktest() Dim ticks As Long, i As Long, j As Long Dim v() As Variant Dim a() As Long 'VBA integers are internally stored as longs Dim r As Range Set
  • Convert vba to vb6 and create a .dll - how to - hints, tips and risks
    I should convert a huge load of code which was written in VBA (Excel) to VB6. But I really do not know what I have to take care of or where to begin. Therefore it would be great to get some hints here from the VB6 experts. I installed already MS Visual Studio and played a bit around. But I'm not a VB6 expert and do not really know what I have to do. The final goal is to have all the VBA code, which is currently placed in one excel vba macro into a VB6 project and create a .dll out of it. This .dll should be referenced by the excel and the excel should run like it does now :-) For example what
  • VBA 多循环匹配条件(VBA Multiple loops match conditions)
    问题 如果这是重复的,我很抱歉,因为我一直在寻找但没有找到答案。 我是 VBA 的新手以及它们如何构建循环。 我正在尝试进行搜索和比较。 我需要比较第一行中的值,看看它们是否与第二行匹配,如果不匹配,则继续移动到下一行。 请参阅下面的代码(它运行没有错误只是找不到任何确实存在的值,因为我可以手动搜索并找到它们) 这个数据集可能非常大,所以我想尽可能高效地编写它,但不确定什么样的循环结构会执行得更快。 我需要比较第 21 列中的值,看看是否有重复值,如果有,那么我需要查看相应行的第 22 列中的值是否相同,如果它们相同,那么我想去RowB 中的下一行,否则如果它们不是相同的值,那么我想检查第 4 行中都是日期的值,看看它们是否在 2 个月内。 如果他们不继续寻找。 Dim RowsCount As Integer Dim ColCount As Integer RowsCount = Cells(Rows.Count, 1).End(xlUp).Row ColCount = Cells(1, Columns.Count).End(xlToLeft).Column Dim RowA As Integer Dim RowB As Integer Dim GroupA As Variant Dim GroupB As Variant Dim CounterA As Variant Dim
  • 是否可以在 VBA 中将 Excel 表转换为 json(Is it possible in VBA convert Excel table to json)
    问题 我需要将大约 20 列和许多行的 excel 表中的数据转换为 json。 我没有在 vba 中找到用于此目的的简短代码示例。 我找到了这个 https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas ,但它非常大。 可能是一个较短的例子? 回答1 我会使用这个的修改版本:http://www.excelvbamacros.in/2015/01/export-range-in-jason-format.html 如果你想把它写到文件有一个代码: Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dim Fileout As Object Set Fileout = fso.CreateTextFile("C:\some_dir\mydata.json", True, True) Fileout.Write jsonStringFromConvertFunction Fileout.Close 回答2 所以我会将范围传递给一个 JavaScript 函数,让它遍历 Excel 对象模型并在 JavaScript 中构建数组。 然后调用 JavaScript 库将数组转换为字符串(帽子提示 Douglas