运行时错误：在宏中'-2147417848 (80010108)'

Question

如果我在a列中输入一个带有空格的单词(例如：L3、FMA、Nest等(，则会出现错误："运行时错误："-2147417848(80010108("：对象"Range"的方法"HorizontalAlignment"失败；。我该怎么解决这个问题？

我有这个宏：

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
Dim rng As Range, v As Variant
For Each rng In Target
v = Split(rng, " ")
If UBound(v) <> 1 Then Exit Sub
If Right(rng, 1) <> "m" Then
rng = v(1) & " " & v(0)
NumberFormat = "@"
With Target
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Target.Font
.Name = "Calibri"
.Size = 11
End With
End If
Next rng
Application.ScreenUpdating = True
End Sub

此宏将数据转换为右侧的(任意数字(m和。

例如：

L3 280M
500m FMA
Nest 475m
340m Pr6
720M uT10
etc.

转换为：

L3 280M
FMA 500m
Nest 475m
Pr6 340m
uT10 720M
etc.

Answer 1

我看到这个宏有几个问题。

首先，正如Warcupine所指出的，在运行此代码时，您肯定需要暂停事件。您更改工作表内容，这样在不挂起事件的情况下，每次更改都会得到大量递归调用。这就是这个问题最初错误的原因。

其次，如果有前导空格或多个内部空格，那么v数组的索引0和1处就没有值。一个简单的解决方法是使用工作表修剪功能。VBA修剪函数只删除前导空格和尾部空格，工作表函数也删除其他内部空格。

第三，跳转到cleanexit将停止处理目标中的任何剩余细胞。

此外，您每次都要格式化整个目标，而不仅仅是活动的rng单元格，忽略大写字母"M"和文本项以"M"结尾的单元格。您只能格式化那些需要翻转的单元格。

以下代码的一个遗留问题是，它将翻转任何有两个以"M"结尾的项的单元格。

这是我使用的测试范围：

L3 280M
500米FMA
一个567米三个
巢475米
340米Pr6
有轨电车
720M uT10
345m RLM

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim numberformat As String
If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim rng As Range, v As Variant
For Each rng In Target
'Worksheet Trim removes leading and trailing spaces and additional internal spaces
rng = WorksheetFunction.Trim(rng)
v = Split(rng, " ")
'Skip any cell with more than two terms but still process remaining targets
If UBound(v) = 1 Then
If Len(v(0)) = 4 And IsNumeric(Left(v(0), 3)) And UCase(Right(v(0), 1)) = "M" Then
rng = Trim(v(1) & " " & v(0))
End If
With rng
.numberformat = "@"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With rng.Font
.Name = "Calibri"
.Size = 11
End With
End If
Next rng
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Answer 2

任何时候使用Worksheet Change都需要禁用事件或事件触发器本身，从而导致无限循环。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim numberformat As String
If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim rng As Range, v As Variant
For Each rng In Target
v = Split(rng, " ")
If UBound(v) <> 1 Then GoTo cleanexit
If Right(rng, 1) <> "m" Then
rng = trim(v(1) & " " & v(0))
numberformat = "@"
With Target
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Target.Font
.Name = "Calibri"
.Size = 11
End With
End If
Next rng
cleanexit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub