VBA 'Evaluate' function generates an 'Error 2015'

Post Reply
AlastairFM
Posts: 10
Joined: Mon Feb 28, 2011 12:13 pm
OLAP Product: TM1 / Cognos Express
Version: PAL 2.0.9.1
Excel Version: 2016

VBA 'Evaluate' function generates an 'Error 2015'

Post by AlastairFM »

I am trying to check the value on a report has not changed since the report was generated. To do this I have the calculation in Excel set to manual and I am comparing the current value of the DBRW formula with the value contained withing the formula's cell. I am using Excel 2003.

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)
Of course the workaround is to simply parse the formula string to get the DBRW parameters and then manually execute in VBA via an 'Application.Run' call - but I would really like to know what such a basic function falls over with this error as the parameters of the DBRW are all valid.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by lotsaram »

Welcome to the forum Alastair,

Basic stuff maybe but you have checked that you are connected to the server before trying to evaluate the DBRW function?
AlastairFM wrote:Of course the workaround is to simply parse the formula string to get the DBRW parameters and then manually execute in VBA via an 'Application.Run' call - but I would really like to know what such a basic function falls over with this error as the parameters of the DBRW are all valid.
Another workaround would be to copy the sheet (or just the array of cells to be evaluated) elsewhere in Excel and paste as values then refresh the original area and compare values directly. Seems to me to be easier than parsing the DBRW formula arguments.

Out of interest what is the requirement behind comparing the current cube value vs. the value in the sheet. Surely what is relevant is the current value?
AlastairFM
Posts: 10
Joined: Mon Feb 28, 2011 12:13 pm
OLAP Product: TM1 / Cognos Express
Version: PAL 2.0.9.1
Excel Version: 2016

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by AlastairFM »

Sorry lotsaram obviously my previous reply to you wasn't commited for some reason.

Yes I have checked all the basic stuff such as an active connection, the user is logged in and I have the correct security privillages. Although as it works 'in cell' in the same session these basic requirements would all have to be satisfied anyway.

Yes a workaround would be to copy the formula to another sheet, calculate and compare the two values but I already have found a workaround - I was just posting to see if anyone had encountered similar errors with the 'Evalute' function.

To explain what the requirement is, further to my origional post, I have to explain that this is a component of a reporting function is part of a very extensive VBA system that sits on the top of TM1. Each generated report can have functions performed on it's values but not necessarly in the same session as the report was created. This taken in addition to the fact that another user could update / modify the underlying cube data requres us to run a check to make sure the cube cell value is the same as the report cell value before the function is initiated.

I am currently at a lost to explain why a function which works in cell and whose parameters are all valid when seperatly evaluated would fail. Even with the code paused at the failling point I can perform an evaluate on a basic TM1 formula such as below and have a valid return

Code: Select all


dim strFormula as string

strFormula = "=DIMNM(""myserver:}cubes"",1)"

msgbox Application.Evaluate(strFormula)

User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by Steve Rowe »

It may be what you are seeing is pacularity of the DBRW function and I'd guess that your approach will work if you use a DBR?

The DBRW takes two goes to get it's results the first "pass" sends the arguements to the server and the second "pass" gets the results. I think on the first pass the value of the formula is an error (#Value, not 100% sure) and this maybe what you are getting back in your evaluate function?

Seems plausible anyway....
Technical Director
www.infocat.co.uk
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by lotsaram »

I wouldn't be that surprised by different behavior between stepping through VBA code versus what happens during runtime execution. Disappointed yes, frustrated certainly, but not surprised. I have been caught out too many times by such quirks where stepping through is OK but there are runtime errors (or the reverse for that matter).
AlastairFM
Posts: 10
Joined: Mon Feb 28, 2011 12:13 pm
OLAP Product: TM1 / Cognos Express
Version: PAL 2.0.9.1
Excel Version: 2016

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by AlastairFM »

Yes that certainly does seem like a plausable explanation.

I have, however, setup the same system (TM1 server + VBA system + same OS ) in a virtual machine and that statement does not error. If your suggestion is correct Steve I would expect it to happen all the time.

I am wondering if this is somehow enviromental. Knowing Excel of old it has a problem when it comes to interpriting cells that it thinks are dates - and with the MS article in mind I am wondering if the client's system is somehow differently configured. This may be supported by the fact that the client tested the system on another machine (of the same specification) and the error did not occur.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by rmackenzie »

I would second what the other guys said regarding the peculiarities of the DBRW and the Excel event model, but then again, you also said:
AlastairFM wrote: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.
Interestingly, the blogger you referenced makes a further post regarding the shortcomings of Application.Evaluate. The stated issue sounds much more like the real problem you are facing - he states "I was less happy with the fact that Application.Evaluate() does not support or return all the data types."
Of course the workaround is to simply parse the formula string to get the DBRW parameters and then manually execute in VBA via an 'Application.Run' call - but I would really like to know what such a basic function falls over with this error as the parameters of the DBRW are all valid.
IMO the Evaluate function isn't a 'basic' function and has a lot of complexities - which makes life difficult for Excel developers who cannot be expected to anticipate the quirks of every third-party add-in that comes along. I'd agree with your own proposed solution which is to put the Run "DBRW", etc in a function. Also, if I might say, it sounds like you could also do something a bit more robust, on the TM1 side and not Excel, for reports to understand if the reporting model has changed since the last time the report was accessed and then act accordingly. If your model is updated via regular TIs then you could have a cube cell somewhere that had a measure 'Last data update time' (or similar) that would indicate if the data was fresh.

Finally, I don't know if you are aware that TM1 has a very limited range of data-types compared to Excel. A cube cell can either contain a TM1 real or a TM1 string. There are other data types but cubes store only 2 types; a real is like a VB double and a string is a string. So, you don't need to define the 'result' as a Variant, and you may be better advised to dimension it is either a double or string depending on what you expect and then there will be less ambiguity to code around.
Robin Mackenzie
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by rmackenzie »

I see the thread moved on whilst I was typing... so I wonder what would happen if you did something like this:

Code: Select all

Dim strFormula as String

strFormula = "=DBRW(""myserver:}mycube"",""hardcoded element 1"",""hard coded element 2"",""etc"")"

MsgBox Application.Evaluate(strFormula)
This would pin down Evaluate's approach to argument-type-conversion as the guilty party.

Robin
Robin Mackenzie
AlastairFM
Posts: 10
Joined: Mon Feb 28, 2011 12:13 pm
OLAP Product: TM1 / Cognos Express
Version: PAL 2.0.9.1
Excel Version: 2016

Re: VBA 'Evaluate' function generates an 'Error 2015'

Post by AlastairFM »

Thank you rmackenzie for all you comments.

To answer your last question ; specifying the DBRW arguments as string literals works every time - which is why I suspected that Excel was 'misundertanding' the data types when it evaluated the formula. I do know about the limits to TM1's datatypes and Excels. I am of the camp that absolutly hates the use of variants in code a) becuase they take more memory b) becuase they suggest poor design, in that you have not clearly defined and planned what datatype is to be returned by / used in a function. I am using it here to try and ensure there cannot be a 'type mismatch' due to the returned type being different from that which was expected.

I have to say I only ever use 'Evaluate' (or rather the shorthand version) to easily return the value or named ranges - although in general I am from the 'old school' of software development that gives all object refereces fully qualified names (that way there is less chance for errors). I

A UDF was my first instinct but for the sake of time this 'seemed' an easy compramise - evaluate the cells formula and compare it to the cells value. When I reenable this check I will definatly be creating a reusable function to evaluate a DBRW. Clearly 'Evaluate' is a function to avoid when working with TM1.

Unfortunatly the entire 'reporting' side of the sytem is Excel based (effectivly re-creating DBRW spreadsheets) - so to try and use any TIs to verify this would be overkill.
Post Reply