CODEWITHSUNDEEP
excelvbaexcel-formula
Mary A. Marion
Mary A. Marion

Reputation: 800

how to change a macro function

I have gotten an excel file that includes reference to a function f(x,y). The file did not include the macro. I wrote a macro with a different function and saved it with an .xlsm extension. when I run the new macro I get a run time error '28' out of stack space error.

What does this mean and how do I correct? I was told I did not need visual basic to do this. Is this a special additional package? Thank you. 

Sub xplusy()
'
' xplusy Macro
' Function f(x,y) f=x+y End Function
'

'
    Application.Run "'5_3 Runge-Kutta.xlsm'!xplusy"
    Sheets("RK4").Select
    Range("C9").Select
    Application.Run "'5_3 Runge-Kutta.xlsm'!xplusy"
    Range("E8").Select
    Application.CommandBars("Help").Visible = False
    Application.Goto Reference:="xplusy"
End Sub

Upvotes: 0

Views: 115

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

Let's pare your Sub down to the very basic level:

Sub VeryBasic()
    Application.Run "VeryBasic"
    Application.Run "VeryBasic"
    Application.GoTo "Location"
End Sub

Now, see how that code calls itself? If we expand it 1 level, we get this:

Sub VeryBasic()
    'Application.Run "VeryBasic"
        Application.Run "VeryBasic"
        Application.Run "VeryBasic"
        Application.GoTo "Location"
    'Application.Run "VeryBasic"
        Application.Run "VeryBasic"
        Application.Run "VeryBasic"
        Application.GoTo "Location"
    Application.GoTo "Location"
End Sub

Expand it 2 levels:

Sub VeryBasic()
    'Application.Run "VeryBasic"
        'Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.GoTo "Location"
        'Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.GoTo "Location"
        Application.GoTo "Location"
    'Application.Run "VeryBasic"
        'Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.GoTo "Location"
        'Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.Run "VeryBasic"
            Application.GoTo "Location"
        Application.GoTo "Location"
    Application.GoTo "Location"
End Sub

Hopefully you can see that this code will keep getting bigger and bigger, without actually doing anything except adding another copy of itself to the "Stack" to run. Eventually, your stack will run out of room (or your computer will run out of memory), and you get a Stack overflow error.

Upvotes: 2

Dominique
Dominique

Reputation: 17493

You are calling the function xplusy(), which is calling the function xplusy(), which is calling the function xplusy(), which is calling the function xplusy(), ....
Each time you call a function, its reference is added to the so-called stack, so you are overloading that stack, explaining the error message.

In the original file, you should check the name of the macro, and the macro you're calling (they definitely should be different).

Upvotes: 1

Related Questions