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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 17:12:46  来源:igfitidea点击:

Convert an entire range to uppercase without looping through all the cells

excelvbarangeuppercase

提问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),)]

Indexand Upperare worksheet functions. So you can use Application.Worksheetfunction.Index()but since we don't have an equivalent of UPPERlike Application.Worksheetfunction.UPPER(), we can only write it as [cell] = [UPPER(cell)]

IndexUpper是工作表函数。所以你可以使用,Application.Worksheetfunction.Index()但由于我们没有UPPER类似的等价物Application.Worksheetfunction.UPPER(),我们只能把它写成[cell] = [UPPER(cell)]

Now with that line we are instructing VBAto return an array and this is where INDEXcomes into play. (As we are aware, there are two forms of the INDEXfunction: 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提出的优雅答案,WorksheetFunctionTranspose函数有一些老式的限制;具体而言,可以翻转的元素上限为 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属性中的单元格中受益的子过程类型。请参阅此答案以了解在选择中处理单元格的样板框架。