Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-26-2014, 12:45 PM
lcaretto lcaretto is offline #REF! Error in calling VBA function disappears when function is copied Windows 7 64bit #REF! Error in calling VBA function disappears when function is copied Office 2013
Novice
#REF! Error in calling VBA function disappears when function is copied
 
Join Date: Oct 2013
Posts: 5
lcaretto is on a distinguished road
Default #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.
Attached Images
File Type: jpg ExcelREFerror.jpg (85.5 KB, 17 views)
Attached Files
File Type: xlsm dormandPrinceErrorReport.xlsm (46.0 KB, 13 views)
Reply With Quote
  #2  
Old 05-26-2014, 05:38 PM
whatsup whatsup is offline #REF! Error in calling VBA function disappears when function is copied Windows 7 64bit #REF! Error in calling VBA function disappears when function is copied Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 05-26-2014, 07:19 PM
lcaretto lcaretto is offline #REF! Error in calling VBA function disappears when function is copied Windows 7 64bit #REF! Error in calling VBA function disappears when function is copied Office 2013
Novice
#REF! Error in calling VBA function disappears when function is copied
 
Join Date: Oct 2013
Posts: 5
lcaretto is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
#REF! Error in calling VBA function disappears when function is copied Sum Function teza2k06 Excel 3 02-06-2013 08:29 AM
#REF! Error in calling VBA function disappears when function is copied 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:12 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft