#1
|
|||
|
|||
#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. |
#2
|
|||
|
|||
Hi
You can't use a cells address as name for functions, you intend to use in the worksheet itself. Though it works in your Sub Test(), you should generally avoid using names already excel has in use. This may cause problems, or as you see in your case errors. In Versions 2007 and above ODE45 refers to the cell of column 10249, row 45 and is therefore in use of Excel itself. Your function as it is will work perfectly in Versions up to 2003. |
#3
|
|||
|
|||
Thanks, whatsup. I can't believe that I missed that. At least I now what to look for when I get a #REF! error in a function call.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a graph for Future Value function (FV function) | bmoody | Excel | 2 | 11-06-2013 10:52 AM |
Sum Function | teza2k06 | Excel | 3 | 02-06-2013 08:29 AM |
Sub or Function not defined error - GetParaIndex Word 2010 | jec1 | Word VBA | 1 | 05-24-2012 11:58 PM |
Error: "The function you are attempting to run contains macros" | Fadi | Word | 1 | 11-01-2011 05:25 AM |
Compile error: sub or function not defined.. | xena2305 | Excel Programming | 0 | 08-02-2011 10:17 AM |