从VBA界面运行宏时Excel崩溃，但在使用按钮时有效-Java 学习之路

我有一个宏，它采用不同的范围，使用其中的值执行计算，然后将值复制到一个单元格，以便可用于工作表中的公式 . 我希望在修改某些范围内的值时运行此宏 . 我在不同的工作表中使用相同的代码，我最初放置了一个ActiveX按钮 . 问题是，当我把宏放在worksheet_calculate或Worksheet_SelectionChange时，Excel崩溃了 . 起初我假设我正在导致无限次迭代，但在使用EventEnable = FALSE和EnableCalculations = FALSE以避免该行为后Excel仍然崩溃 . 我删除了SelectionChange和Calculate中的宏，我进一步检查了这个问题，发现当从VBA界面运行宏时，Excel也会崩溃，但它可以与引用宏的工作表中的ActiveX按钮一起使用 . 我似乎无法找到代码的错误，以及为什么它在通过按钮访问时运行完美，但在从VBA界面运行时崩溃 .

这是代码，请帮助我找出错误，或者我可以尝试从VBA运行，因为调用它或从界面运行它将导致Excel崩溃 .

Sub performCalculations()
Dim revenue As Double
Dim expenses As Double
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim aConst As Double
Dim tempConst As Long
Dim bla As Range
Dim curve As Range
Dim price As Range
Dim finalOut As Range
Dim a As Double
Dim tempString As String


Set bla = ActiveWorkbook.ActiveSheet.Range("D16:D75")
Set curve = ActiveWorkbook.ActiveSheet.Range("G2:WH11")
Set price = ActiveWorkbook.ActiveSheet.Range("G162:WH164")
Set finalOut = ActiveWorkbook.ActiveSheet.Range("G83:WH141")
tempString = ActiveWorkbook.ActiveSheet.Name
tempString = Left(tempString, InStr(1, tempString, " ", vbTextCompare) - 1)
a = ActiveWorkbook.Sheets(tempString).Cells(10, 8).Value
aConst = a / 1000


For i = 1 To bla.Rows.count
    count = 1
    For j = 1 To curve.Columns.count
        If j < i Then
            finalOut.Cells(i, j) = 0
        Else
            If bla.Cells(i, 1) = 0 Then
                finalOut.Cells(i, j) = 0
            Else
                tempConst = curve.Cells(2, count) * aConst * price.Cells(2, j)
                revenue = bla.Cells(i, 1) * (curve.Cells(1, count) * price.Cells(1, j) + curve.Cells(3, count) * price.Cells(3, j))
                expenses = bla.Cells(i, 1) * curve.Cells(4, count) + bla.Cells(i, 1) * curve.Cells(5, count) + bla.Cells(i, 1) * _
                    curve.Cells(6, count) + bla.Cells(i, 1) * curve.Cells(7, count) + bla.Cells(i, 1) * curve.Cells(10, count)
                expenses = expenses + revenue * curve.Cells(9, count) + tempConst * curve.Cells(8, count)
                revenue = revenue + tempConst
                finalOut(i, j) = revenue - expenses
                count = count + 1
            End If
            If i > 1 Then
                finalOut.Cells(i, j) = finalOut.Cells(i, j) + finalOut.Cells(i - 1, j)
            End If
        End If
    Next j
Next i


End Sub

我正在运行Excel 2013和Windows 7 Professional

1 回答

1
我不确定这是否是您问题的最终答案，但有一件事可能会影响您的宏，使用ActiveWorkbook，ActiveSheet等活动对象 .

我的建议是引用你必须使用的对象，这样如果没有选择你的excel文件或你选择了错误的工作表，代码仍然可以工作 . 为此，请查看以下示例：
```
Dim workingSheet as Worksheet
Dim auxWorksheet as Worksheet

Set workingSheet = ThisWorkbook.Worksheets("The name of the worksheet here")
Set auxWorksheet = ThisWorkbook.Worksheets(tempString)

Set bla = workingSheet.Range("D16:D75")
Set curve = workingSheet.Range("G2:WH11")
Set price = workingSheet.Range("G162:WH164")
Set finalOut = workingSheet.Range("G83:WH141")
tempString = workingSheet.Name
a = auxWorksheet.Cells(10, 8).Value
```
请试一试并提供一些反馈，以便如果不起作用，我会继续努力帮助您 .
回复于 2024-05-03T06:34:52+08:00

从VBA界面运行宏时Excel崩溃，但在使用按钮时有效

1 回答

相关问题