The EVALUATE VBA command – does anyone have any experience?

[Perception]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> I am having major problems with the EVALUATE command in Excel VBA and I am hoping someone can help.

In short, I can find no way to debug the Evaluate command. Once the command is called, there is no way of stepping into the sequence of events.

If there is a cell in a worksheet with a normal “=” sign and a function, you can use the evaluate formula auditing tool to step through the evaluation step by step. You could also put break points or stops in the function to step through the code and debug.

If you fill a spreadsheet cell with the code without "=", you can in theory use the VBA EVALUATE to read the cell and carry out the same operations as would occur on the spreadsheet. The problem is that Evaluate does not seem to allow any of these debugging practices. It does not allow you to “step into” the evaluation, nor does it even stop at stops or break points in the function being evaluated. Even worse, if there is an internal error such as “subscript out of range”, it does not show the error, but just crashes Excel.

If you Google “excel vba evaluate” , the top of the list is a posting on a forum from Aaron Blood. It has the heading “Evaluate - Most Powerful Command in VBA?”


If I could overcome this problem of debugging, I would be the fist to support the concept that EVALUATE is one of the most powerful commands in Excel VBA. However, without any debugging facilities it is probably one of the most frustrating!


Does anyone know how to overcome the problem?

Thanks in advance for any advice

Ian

 

[Colin Legg]

Hi Ian,

Evaluate is a useful tool, but is often misused. Like any other function packaged in the Excel object model, you call it and it gives a result; you can't step through it's internal code.

If I found that evaluating a formula string was returning an unexpected result, I would debug it by manually putting the formula into the worksheet and investigating it from there.

What expression are you evaluating which crashes Excel? Please post the code.

 

[RoryA]

I suspect it relates to this question at EF.

 

[Perception]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi Colin


Tanks for taking the time to reply to my question


Unfortunately, in my situation, it is not quite as simple as putting the code in the cell to try it out. This is because the “code” calls functions that access information in internal VBA arrays. The offset of those arrays depends on calculations being carried out within the VBA code.

A very simple example would be the following code in a cell being read by the VBA command Evaluate
ValX(3,1,RO(0))
There are internal arrays ValueId1 and ValueX1

The Functions being called to access these arrays are:

Function ValX(TID As Long, CID As Long, RID1 As Variant)
  Dim RID As Long
  RID = Val(RID1)
  ValX = ValueX1(ValueId1(TID, CID, RID))
  End Function
   
  Function ValueID(TID As Long, CID As Long, RID1 As Variant)
  Dim RID As Long
  RID = Val(RID1)
  ValueID = ValueId1(TID, CID, RID)
  End Function
   
  Function ValueX(VID As Long)
      ValueX = ValueX1(VID)
  End Function
   
  Function RO(ROVAL As Long)
      If ROVAL = 0 Then RO = RelRow(0) Else RO = ROVAL
  End Function

I can manually debug the code by investigating the different variables before going into the Evaluate. So I can sort out where there is a problem with, for example “subscript out of range”. But it is horrendously slow

If there is definitely no way to step into Evaluate then you have answered my question

Thanks

Ian