vba 从多单元格范围中获取格式化值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5590963/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get formatted values from a multi-cell range
提问by Thunder
Dim myText As String
myText= Range("a3").Text
Returns the formatted value in cell A3, but
返回单元格 A3 中的格式化值，但
myText= Range("a3:c7").Text
gives me an error.
给我一个错误。
How do I get strings representing formatted values from a multi-cell range, while preserving the number format? i.e. the format of the output text would be the same as if copy-pasting from the range to a text editor.
如何从多单元格范围获取表示格式化值的字符串，同时保留数字格式？即输出文本的格式与从范围复制粘贴到文本编辑器的格式相同。
采纳答案by Jean-Fran?ois Corbett
The only way to get multiple cell values into an array with one single statement (no loops) is with a Variant array.
使用单个语句（无循环）将多个单元格值放入数组的唯一方法是使用 Variant 数组。
Dim varItemName As Variant
varItemName = Range("a3:c7")
If you really absolutely need the names to be type String, then just CStrthem later when you use them.
如果您真的绝对需要将名称设为 type String，那么CStr稍后使用它们时只需使用它们。
output = FunctionRequiringStringArgument(CStr(varItemName(1,2))
EDIT: Okay, okay, you want strings with same format as in sheet.
编辑：好的，好的，您需要与工作表中格式相同的字符串。
Here's a full working example.
这是一个完整的工作示例。
Dim strMyFormat1 As String
Dim varItemName As Variant
Dim strItemName() As String
Dim strItemNameBF() As String
Dim iCol As Long
Dim iRow As Long
Dim rngMyRange As Range
Set rngMyRange = Range("A3:C7")
varItemName = rngMyRange
ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1), _
LBound(varItemName, 2) To UBound(varItemName, 2))
'// Take a sample of the format
strMyFormat1 = Range("A3").NumberFormat
'// Apply format sample to all values
For iRow = LBound(varItemName, 1) To UBound(varItemName, 1)
For iCol = LBound(varItemName, 2) To UBound(varItemName, 2)
strItemName(iRow, iCol) = Format(varItemName(iRow, iCol), strMyFormat1)
Next iCol
Next iRow
'// Can also apply to only some values -- adjust loops.
'// More loops go here if many format samples.
'// If all cells have different formats, must use brute force -- slower.
ReDim strItemNameBF(1 To rngMyRange.Rows.Count, _
1 To rngMyRange.Columns.Count)
For iRow = 1 To rngMyRange.Rows.Count
For iCol = 1 To rngMyRange.Columns.Count
strItemNameBF(iRow, iCol) = rngMyRange.Cells(iRow, iCol).Text
Next iCol
Next iRow
回答by stema
For Each c In Range("a3:c7")
ItemName = c.Text
Next c
This will give you each cell one after the other.
这将一个接一个地为您提供每个单元格。
回答by Deepak Ninnar
This is a modified version of one of the post here and it worked for me.
这是此处帖子之一的修改版本，对我有用。
Function Range2Text(ByVal my_range As Range) As String
Dim i As Integer, j As Integer
Dim v1 As Variant
Dim Txt As String
v1 = my_range
For i = 1 To UBound(v1)
For j = 1 To UBound(v1, 2)
Txt = Txt & v1(i, j)
Next j
Txt = Txt & vbCrLf
Next i
Range2Text = Txt
End Function
回答by Friedrich
Make a collection and run through all the Areas of the range and collect the text into the collection.
制作一个集合并遍历范围的所有区域并将文本收集到集合中。
回答by Jon49
dim i as integer, j as integer
Dim v1 as variant
v1=range("a3:c7")
for i=1 to ubound(v1)
for j=1 to ubound(v1,2)
debug.print v1(i,j)
next j
next i
