excel - 如何在 VBA 中有效地将数字更改为文本

Question

我正在寻找一种创建切换的方法，我可以用它来将单元格值更改为“常规”格式或“文本”格式，并使其显示为文本，而不是数字。
假设我有 3 个值:
2839283
8572839
5922038
我尝试过的(有效)是这样的:

Dim MyRng as Range
Dim MyCell as Range

Set MyRng = Selection

if MyRng.NumberFormat = "General" Then
   For each MyCell in MyRng
      MyCell.NumberFormat = "@"
      MyCell.Value2 = CStr(MyCell.Value2)
   next MyCell
Else
   For each MyCell in MyRng
      MyCell.NumberFormat = "General"
      MyCell.Value2 = MyCell.Value2
   next MyCell
End if

有什么方法可以在不使用 for 循环的情况下实现相同的目标？我正在努力提高它的效率，当这个宏应用于 20k 行时......嗯，它工作但相当慢。
我试图简单地调用:

MyRng.NumberFormat
MyRng.Value2

它适用于从文本到数字的转换，但遗憾的是它在尝试将数字转换为文本时显示不匹配错误(这就是我在那里使用循环的原因)。
有任何想法吗？

Answer 1

切换常规和文本

看看这是否有帮助或给你一个想法。

编码

Option Explicit

Sub toggleGeneralText()
   
    If TypeName(Selection) <> "Range" Then Exit Sub

    Dim rg As Range: Set rg = Selection
    
    If rg.NumberFormat = "General" Then
        Dim rCount As Long: rCount = rg.Rows.Count
        Dim cCount As Long: cCount = rg.Columns.Count
        Dim Data As Variant
        If rCount > 1 Or cCount > 1 Then
            Data = rg.Value2
        Else
            ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value2
        End If
        Dim i As Long
        Dim j As Long
        For i = 1 To rCount
            For j = 1 To cCount
                Data(i, j) = CStr(Data(i, j))
            Next j
        Next i
        rg.NumberFormat = "@"
        rg.Value2 = Data
    Else
        rg.NumberFormat = "General"
        rg.Value2 = rg.Value2
    End If

End Sub