Excel crashes when macro is run from VBA interface, but works when using a button.

mrivera94

New Member
Joined
Jun 12, 2015
Messages
2
I have a macro that takes different ranges, performs calculations with values in them, and then copies the value to a cell to be available for use to formulas in the worksheet. I want this macro to run when values in certain ranges are modified. I use this same code in different worksheets, where I initially placed an ActiveX button.
The problem is that when I put the macro in a worksheet_calculate or Worksheet_SelectionChange Excel crashes alltogether. At first I assumed I was causing infinite iterations, but after using EventEnable = FALSE and EnableCalculations = FALSE to avoid that behavior Excel still crashed. I erased the macros in SelectionChange and Calculate, and I further inspected the issue and found that when the macro is run from the VBA interface Excel also crashes, but it works with the ActiveX button in the worksheets that makes reference to the macro.
I just can't seem to find what is wrong with the code, and why it runs perfectly when accessed through the button but crashes when run from the VBA interface.

Here is the code, please help me find what is wrong, or what I can I try so that it runs from VBA because calling it or running it from the interface will crash Excel as of now.

Code:
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

I am running Excel 2013, and Windows 7 Professional
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top