vba 将整个范围转换为大写而不遍历所有单元格
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/19985895/
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
Convert an entire range to uppercase without looping through all the cells
提问by dwstein
right now I'm using the following code to convert a list of ticker symbols from lowercase to upper case letters:
现在我正在使用以下代码将股票代码列表从小写字母转换为大写字母:
Dim Tickers As String
Dim n As Integer
For n = 2 To Last
Tickers = UCase(W.Cells(n, 1).Value)
W.Cells(n, 1).Value = Tickers
Next n
Is there a method I can use to convert the whole range in one line? something like:
有没有一种方法可以用来在一行中转换整个范围?就像是:
Range("A1:A20").convertouppercasesomehow
回答by Siddharth Rout
Is there a method I can use to convert the whole range in one line?
有没有一种方法可以用来在一行中转换整个范围?
Yes you can convert without looping. Try this
是的,您可以在不循环的情况下进行转换。尝试这个
Sub Sample()
[A1:A20] = [INDEX(UPPER(A1:A20),)]
End Sub
Alternatively, using a variable range, try this:
或者,使用可变范围,试试这个:
Sub Sample()
dim rng as Range
dim sAddr as string
set rng = Range("A1:A20")
sAddr = rng.Address
rng = Evaluate("index(upper(" & sAddr & "),)")
End Sub
As per your example
根据你的例子
W.Range("A1:A20") = [index(upper(A1:A20),)]
Explanation
解释
There are two parts to [A1:A20] = [INDEX(UPPER(A1:A20),)]
有两个部分 [A1:A20] = [INDEX(UPPER(A1:A20),)]
PART 1
第1部分
As shown above, [A1:A20]
is nothing but just a short way of writing Range("A1:A20")
如上所示,[A1:A20]
只是一种简短的写作方式Range("A1:A20")
PART 2
第2部分
[INDEX(UPPER(A1:A20),)]
[INDEX(UPPER(A1:A20),)]
Index
and Upper
are worksheet functions. So you can use Application.Worksheetfunction.Index()
but since we don't have an equivalent of UPPER
like Application.Worksheetfunction.UPPER()
, we can only write it as [cell] = [UPPER(cell)]
Index
和Upper
是工作表函数。所以你可以使用,Application.Worksheetfunction.Index()
但由于我们没有UPPER
类似的等价物Application.Worksheetfunction.UPPER()
,我们只能把它写成[cell] = [UPPER(cell)]
Now with that line we are instructing VBA
to return an array and this is where INDEX
comes into play. (As we are aware, there are two forms of the INDEX
function: the array form and the reference form.) By not specifying a row or a column of the array, we are simply letting Excel know that we want the whole array. (Mentioned in VBA help as well) So basically what we are doing is converting each cell in [A1:A20]
into uppercase
现在有了那一行,我们指示VBA
返回一个数组,这就是INDEX
发挥作用的地方。(正如我们所知,INDEX
函数有两种形式:数组形式和引用形式。)通过不指定数组的行或列,我们只是让 Excel 知道我们想要整个数组。(在VBA帮助中也提到)所以基本上我们正在做的是将每个单元格转换[A1:A20]
为大写
回答by Netloh
You can't do it in one line like that, butyou can do it for a given range like:
你不能像这样在一行中完成,但你可以在给定的范围内完成,例如:
Sub Test()
Dim Rng As Range
Dim c As Range
Set Rng = ActiveSheet.Range("A1:A20")
For Each c In Rng
c.Value = UCase(c.Value)
Next c
End Sub
Which is rather simple and intuitive.
这是相当简单和直观的。
回答by Patrick Lepelletier
from what i gathered from various sources:
从我从各种来源收集到的:
Function UcaseRangeAsArray(TargetRng As Range) As Variant()
Dim Arr()
Arr = Evaluate("INDEX(UPPER(" & TargetRng.Address(External:=True) & "),)")
UcaseRangeAsArray = Arr
Erase Arr
End Function
回答by Peter Albert
Here's another "one liner hack":
这是另一个“单线黑客”:
Sub UCaseRange(rng As Range)
rng = WorksheetFunction.Transpose(Split(UCase(Join( _
WorksheetFunction.Transpose(rng), vbBack)), vbBack))
End Sub
This assumes, that none of your cells contain the vbBack character (ASCII code 8).
这假设您的所有单元格都不包含 vbBack 字符(ASCII 代码 8)。
回答by Peter Albert
With respect to the elegant answer put forth by Peter Albert, the WorksheetFunction's Transposefunction has some old fashioned limits; specifically there is a ceiling of 65,535 (max unsigned integer -1) elements that can be flipped. Bulk loading a variant array, processing 'in-memory' and subsequently returning the modified values to the worksheet can overcome that limit.
关于Peter Albert提出的优雅答案,WorksheetFunction的Transpose函数有一些老式的限制;具体而言,可以翻转的元素上限为 65,535(最大无符号整数 -1)。批量加载变体数组、处理“内存中”并随后将修改后的值返回到工作表可以克服该限制。
Sub test()
With Worksheets("Sheet1")
makeUpper .Range("A2:A1000000")
End With
End Sub
Sub makeUpper(rng As Range)
Dim v As Long, vUPRs As Variant
With rng
vUPRs = .Value2
For v = LBound(vUPRs, 1) To UBound(vUPRs, 1)
vUPRs(v, 1) = UCase(vUPRs(v, 1))
Next v
.Cells = vUPRs
End With
End Sub
This processes very quickly. 100K cells of data typically takes less than half a second and 1M cells can be converted in 4-6 seconds.
这个过程非常快。100K 单元的数据通常需要不到半秒,而 1M 单元可以在 4-6 秒内转换。
This is the type of sub procedure that can benefit from working on cells in the Application.Selectionproperty. See this answerfor boilerplate framwework to process cells within the Selection.
这是可以从处理Application.Selection属性中的单元格中受益的子过程类型。请参阅此答案以了解在选择中处理单元格的样板框架。