在 vba 模块中使用 target.row 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14132420/
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
Error when using target.row in a vba module
提问by user1944454
I am creating an excel 2007 application which uses the exact same VBA code to force uppercase letters in 2 columns. This code is used in 8 different worksheets. I tried to create a subroutine in a module so that I can call the subroutine under each of the 8 worksheets but it isn't working.
我正在创建一个 excel 2007 应用程序，它使用完全相同的 VBA 代码在 2 列中强制使用大写字母。此代码用于 8 个不同的工作表。我试图在一个模块中创建一个子例程，以便我可以在 8 个工作表中的每一个下调用子例程，但它不起作用。
When I added the following code directly under each worksheet it worked:
当我直接在每个工作表下添加以下代码时，它起作用了：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRowNumber As Integer
Dim targetColumnNumber As Integer
If (Target.Row >= 6 And Target.Row <= 500) Then
If (Not Intersect(Target, Range("F6:F500")) Is Nothing) Then
If Target.Column = 6 Then
Application.EnableEvents = False
Range("$F" & Target.Row).Value = UCase(Range("$F" & Target.Row).Value)
Application.EnableEvents = True
End If
End If
If (Not Intersect(Target, Range("K6:K500")) Is Nothing) Then
If Target.Column = 11 Then
Application.EnableEvents = False
Range("$K" & Target.Row).Value = UCase(Range("$K" & Target.Row).Value)
Application.EnableEvents = True
End If
End If
end if
end sub
But if I create a module with the same code and try to call the subroutine under each of the worksheets I get the following error: Run-time error '424': Object required.
但是，如果我使用相同的代码创建一个模块并尝试调用每个工作表下的子例程，我会收到以下错误：运行时错误“424”：需要对象。
Code in Module:
模块中的代码：
Sub convert_upper()
Dim TargetRowNumber As Integer
Dim targetColumnNumber As Integer
If (Target.Row >= 6 And Target.Row <= 500) Then
If (Not Intersect(Target, Range("F6:F500")) Is Nothing) Then
If Target.Column = 6 Then
Application.EnableEvents = False
Range("$F" & Target.Row).Value = UCase(Range("$F" & Target.Row).Value)
Application.EnableEvents = True
End If
End If
If (Not Intersect(Target, Range("K6:K500")) Is Nothing) Then
If Target.Column = 11 Then
Application.EnableEvents = False
Range("$K" & Target.Row).Value = UCase(Range("$K" & Target.Row).Value)
Application.EnableEvents = True
End If
End If
End If
End Sub
Code under each worksheet to call the subroutine:
每个工作表下调用子程序的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
convert_upper
End Sub
This is the first time that I have tried to program in vba. I have tried to find a solution online without success. I would really appreciate it if someone could help me.
这是我第一次尝试用 vba 编程。我试图在网上找到解决方案但没有成功。如果有人可以帮助我，我将不胜感激。
Thank you so much.
非常感谢。
回答by Doug Glancy
Chris has a good answer to get your individual event subroutines working.
Chris 有一个很好的答案可以让您的单个事件子例程正常工作。
However, a simpler way would be to use the Workbook-level SheetChangeevent. This works the same as a sheet-level event, except that it's triggered every time any sheet in a workbook is changed. Its arguments include Shin addition to Target, so that you can test the which sheet triggered the event. You'll want to do this, assuming you don't want every sheet in the workbook to trigger the event.
但是，更简单的方法是使用工作簿级SheetChange事件。这与工作表级事件的工作原理相同，不同之处在于每次更改工作簿中的任何工作表时都会触发它。它的参数Sh除了 之外还包括Target，以便您可以测试哪个工作表触发了事件。您会想要这样做，假设您不希望工作簿中的每个工作表都触发该事件。
Here's some code I think will work for you. I've also tightened up your logic some. Paste it into your workbook's ThisWorkbookmodule:
这是我认为对您有用的一些代码。我也加强了你的逻辑。将其粘贴到您的工作簿ThisWorkbook模块中：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RangeToUpper As Excel.Range
Dim AreaToUpper As Excel.Range
Select Case Sh.Name
Case "Sheet1", "Sheet2"
If (Not Intersect(Target, Sh.Range("F6:F500")) Is Nothing) Or _
Not Intersect(Target, Sh.Range("K6:K500")) Is Nothing Then
Set RangeToUpper = Intersect(Target, Union(Sh.Range("F6:F500"), Sh.Range("K6:K500")))
On Error GoTo Err_Handler
Application.EnableEvents = False
For Each AreaToUpper In RangeToUpper.Areas
AreaToUpper.Value = UCase(AreaToUpper.Value)
Next AreaToUpper
End If
End Select
Err_Handler:
Application.EnableEvents = True
End Sub
回答by chris neilsen
You need to think about Variable Scope
你需要考虑 Variable Scope
Change your Subdeclaration to
将您的Sub声明更改为
Sub convert_upper(Target as Range)
And call it as
并将其称为
convert_upper Target
You will also need to qualify the Rangereferences in your common Sub(otherwise the code refers to the ActiveSheet). Eg (apply to all uses of Range)
您还需要限定Range您的公共引用中的引用Sub（否则代码指的是ActiveSheet）。例如（适用于 的所有用途Range）
With Target.Parent
.Range("$F" & Target.Row).Value = UCase(.Range("$F" & Target.Row).Value)
End With
