#REF! Error in calling VBA function disappears when function is copied
I have been working on converting to VBA an online MATLAB program, ode45.m, for solving systems of ordinary differential equations (ODEs) using the Dormand-Prince variant of the Runge-Kutta method.
I got an unusual result, a #REF! error, when I called the VBA function from the worksheet. This error occurred even when I placed a breakpoint on the function statement to debug the program.
After several attempts to find the source of this error, I wrote a sub to call this function with VBA variables that were transferred to and from the worksheet (using range.value). In this case the function worked as expected. (I was using a simple test case with three ODEs whose solution is known; the numerical error seemed reasonable for this test case.)
I thought to copy parts of the original function (named ode45) to a new function (named ode45z) to isolate the point in the code that caused the error. Doing this I was able to get a copy of the code that gave results when called from the worksheet. To ensure that I had not accidentally omitted the part of the function with the bug, I copied and pasted the original function and renamed it as ode45zz. This function also worked when I called it from the worksheet.
I apparently have solved my problem and now have a working function, but I am still not sure why the original function does not work. Has anybody seen a problem like this before?
For your information I am using Excel 2013 with VBA 7.1.1043 on a Gateway FX6831 with an 2.80 GHzIntel i7 CPU. I have attached a copy of the workbook that is causing the problem and a screen shot that shows the array formula for the original function with the #REF! error in cells D3:F3 in the formula bar.
Thanks for any insights you can provide.
|