求助：VBA代码无法实现含连字符单词去连字符并转大写

需求：将单元格中每个含连字符“-”的单词去除连字符后转为大写。

编写的VBA代码运行后无效果，核心问题是无法将拆分数组arrSP的修改同步到原数组arr中，原代码如下：

Sub Remove_hyphen_and_Change_to_UpperCase()

   Dim ws As Worksheet: Set ws = ActiveSheet
   
   Dim rng As Range, arr, arrSP, i As Long, k As Long
   
   Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)      
   
   arr = rng.Value2
  
    For i = 1 To UBound(arr, 1)
     arrSP = Split(arr(i, 1))
       For k = 0 To UBound(arrSP)
         If arrSP(k) Like "*-*" Then
             arrSP(k) = UCase(CStr(Replace(arrSP(k), "-", "")))
         End If
       Next k
    Next i

   rng.Value = arr
    
End Sub

问题分析 #

这段代码的问题很明确：拆分得到arrSP并修改其中元素后，没有把修改后的arrSP重新合并成字符串，赋值回原数组arr的对应单元格位置。原数组arr自始至终没被修改，最后写回单元格自然看不到任何效果。

修正后的代码 #

只需要在修改完arrSP的内层循环结束后，用Join函数把数组合并为字符串，赋值给arr(i, 1)即可：

Sub Remove_hyphen_and_Change_to_UpperCase()

   Dim ws As Worksheet: Set ws = ActiveSheet
   
   Dim rng As Range, arr, arrSP, i As Long, k As Long
   
   Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)      
   
   arr = rng.Value2
  
    For i = 1 To UBound(arr, 1)
     arrSP = Split(arr(i, 1))
       For k = 0 To UBound(arrSP)
         If arrSP(k) Like "*-*" Then
             arrSP(k) = UCase(CStr(Replace(arrSP(k), "-", "")))
         End If
       Next k
       ' 新增：将修改后的数组合并，赋值回原数组
       arr(i, 1) = Join(arrSP, " ")
    Next i

   rng.Value = arr
    
End Sub

结果对比 #

内容的提问来源于stack exchange，提问作者Peace