英文:
VBA works... but crashes excel
问题
我试图使得如果在 C3 中输入信息，则在 E3 中输出 C3*H2，如果在 E3 中输入信息，则在 C3 中输出 E3/H2。我的代码运行正常，但是当我更改输入的数字并运行宏时，Excel 崩溃。
这是我的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SpotRate As Double
Dim srcSheet As Worksheet
Set srcSheet = ThisWorkbook.Worksheets("Input Data")
SpotRate = srcSheet.Range("H2").Value
If Target.Address = "$C$3" Then
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
ElseIf Target.Address = "$E$3" Then
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End If
End Sub
注意：Excel 不会立即崩溃，所以我可以编辑它。只有当我更改 C3 或 E3 中的数字时，它才会崩溃。
英文:
I am trying to make it so if information is entered into C3, it outputs C3*H2 in E3, and if information is entered into E3, it outputs E3/H2 in C3. My code works perfectly fine, but excel crashes when I change the inputted number and the macro runs.
This is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SpotRate As Double
Dim srcSheet As Worksheet
Set srcSheet = ThisWorkbook.Worksheets("Input Data")
SpotRate = srcSheet.Range("H2").Value
If Target.Address = "$C$3" Then
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
ElseIf Target.Address = "$E$3" Then
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End If
End Sub
Note: Excel doesn't crash immediately, so I am able to edit it. It only crashes when I change the numbers in C3 or E3.
答案1
得分: 1
你正在Worksheet_Change中做的更改会导致它递归调用自身，从而创建大量的srcSheet对象，直到出现“Stackoverflow”(!)。无论如何你都不需要srcSheet（假设“Input Data”是相关的工作表...），你可以关闭（然后重新打开）'events'，这意味着递归将不会发生。例如：
英文:
The changes you are making within Worksheet_Change cause it to recursively call itself which creates vast numbers of srcSheet objects until you get a "Stackoverflow"(!). You don't need the srcSheet anyway (assuming "Input Data" is the sheet concerned ...) and you can switch off (and back on) 'events' meaning the recursion will not happen. For example:
Private Sub Worksheet_Change(ByVal Target As Range)
' ensure events are always enabled when we exit this Sub, even if we have an error
On Error GoTo errExit
Application.EnableEvents = False
Dim SpotRate As Double
SpotRate = Me.Range("H2").Value ' use Me to refer to the Sheet
If Target.Address = "$C$3" Then
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
ElseIf Target.Address = "$E$3" Then
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End If
errExit:
Application.EnableEvents = True
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论