I have one specific DBRW that In the cell works every time - returning a decimal number. I am then using VBA to evaulate this formula by using the 'Application.Evaluate' function - in order to compare the result of the DBRW with the cell value.
Every time I do this I reveive an 'Error 2015' or 'Type mismatch' error.
I am returning the result into a variant object - so there can be no 'type mismatch' on the part of the variable I am loading the result of the 'Evaluate' into.
I have tried restarting Excel (in case this is some kind of 'Caching' issue) to no effect.
I am aware of the issue with 'Evaluate' where one or more parameters has 255 or more characters or the result of the function is a string in excess of 255 characters. However I know this is not the case in this instance.
http://dutchgemini.wordpress.com/2009/0 ... excel-vba/
I had the VBA stop before the 'Evaluate' is executed and evaluated each parameter seperatly by hand - none return an error and each is a valid and correct parameter value for the DBRW. This is not supprising since in the worksheet the cell formula evaulates without an error every time. However if I print the formula text to the debug window and then, with the code paused, manually execute the 'Evaluate' this fails with the same messsage.
One thought I did have is that two of the parameters / dimension elements resolve to what can loosely be refrered to as dates i.e. one is a month 'MAR' and one is a year '2012'. I have seen a MS article where Evaluate functions that include dates can be an issue but it does not appear to apply in this situation and it is for an older version of Excel.
http://support.microsoft.com/kb/211601
Theoretically (assuming the formula evaluates in the worksheet) there should be no issue whatsoever with the following type of function call
Code: Select all
dim varResult as variant
dim rngTargetCell as range
...
' Code to set rngTargetCell
...
varResult = Application.Evaluate(rngTargetCell.formula)