Stepping into "Evaluate"

[Perception]

Hi

I wonder if you can help me?

I have a line of code in a macro as follows:

Trgt.Cells(a, i) = Application.Evaluate(strn)

The string strn contains a formula that calls some functions

It is part of a userform code

It was working just fine

However, now when trying to step through the code, it now comes to this line and in effect just hangs.

I need to “step into” the Evaluate calculation. But F8 does not step into Evaluate

I have tried placcing stop points in the functions that it should be calling, but that does not help.

Can anyone tell me what I need to do to move step by step whist it is trying to Evaluate?

Thanks in advance for any help you can give

Ian

[StephenR]

Ian - I suggest you post all relevant code.

[Perception]

Hi Stephen

Thanks for your interest

The two functions called by Evaluate are:

Function ValueID(TID As Long, CID As Long, RID As String)
    ValueID = ValueId1(TID, CID, Mrow)
End Function

Function ValueX(VID As Long)
    ValueX = ValueX1(VID)
End Function

The code that surrounds Evaluate is as follows

For Mrow = 2 To Totrow + 1 'Loops through available RowIDs.
    For i = 1 To UBound(ArLst) ' Loops through the attribute names and attribute codes.
        Trgt.Cells(1, i) = ArLst(i, 1)
        strn = ArLst(i, 2)
        Trgt.Cells(a, i) = Application.Evaluate(strn)
        Next i
    a = a + 1
Next Mrow

At the point it goes into Evaluate

Strn = ValueX(ValueID(3,1,"ROW"))

I don’t know if that helps?

Thanks
Ian

[StephenR]

Ian - please add code tags as per forum rules and then people can help you without fear of being sent to the e-gulag.

[Perception]

Stephen

My apologies for not setting out the code correctly

I think I have corrected it now

Thanks for your interest

Ian

[StephenR]

Ian - I think we will need more information. There are other formulae there. What is the value of Strn when it errors? Can you attach a sample workbook?

[Perception]

Stephen

Thanks again for your time

I have attached the workbook in question

You will need to go to the first sheet (Macros) and press the Read Tables button. This reads the data from the Worksheet Temporary into two arrays. One is a three-dimenional array and the other a one-dimenional array as follows

ValueId1(1 To Maxtable, 1 To Maxcol, 1 To Maxrow) As Variant
ValueX1(1 To lrow) As Variant

There are two functions that allow the data in these arrays to be available to the worksheet as follows

Function ValueID(TID As Long, CID As Long, RID As String)
    ValueID = ValueId1(TID, CID, Mrow)
End Function

Function ValueX(VID As Long)
    ValueX = ValueX1(VID)
End Function

Strn is read from cells in the “attribute code” column of the attribute worksheet, for example:

ValueX(ValueID(3,1,"ROW"))

Before the Evaluate command that hangs

Strn= “ValueX(ValueID(3,1,"ROW"))”

This piece of code is read when you press the third “Write Tables” button on the fist Macro worksheet and select the table “studentnamestarget” as the table to write.

This calls the code in frmTrgt

You can put a stop point in the frmTrgt code at

Trgt.Cells(a, i) = Application.Evaluate(strn)

At the moment, if I press F8 to step into the Evaluate command, it brings up a blank “select target table” form from which you cannot escape without bringing up Task Manager and ending the Excel process.

Although I specified the code I did not write it. I am certainly not expert in VBA.

I need to step inside the Evaluate. This is not just to see what silly mistake I have made this time. I need to step inside because in future I will be using Evaluate to work on much more complex code than this.

Thanks again for all your help

Ian

[snb]

Clicking the 'Write' button allows to select 1 of three tables.
Only the evalution strings of one of the tables (Sutudentnamestarget) are being filled in in Column D in sheet 'Attributes'. If you select one of the other tables the evaluation string will stay empty : nothing to evaluate.

But there is more : the array ValueId1 hasn't been redimensioned so that fails.
You can check this in:

Function ValueID(TID As Long, CID As Long, RID As String)
 Dim x As Long
 x = UBound(ValueId1)
    ValueID = ValueId1(TID, CID, Mrow)
End Function

I think it's only a matter of precise perception.

[Perception]

Thank you for your response

Yes it is only the Student names target that I am looking at

I must confess, I cannot spot the spelling mistake, but that name is only a heading. It is linked to the worksheet Student Names in the Tables Worksheet

However, your post did make me look at the problem more closely. It was caused by the fact that autofilter was switched on on the Worksheet Temporary. Once I removed the autofilter, it works fine.

Which leads me back to my original question. It was not just this issue I need to solve. Because this will be used a great deal on different types of code, I need to be able to "step into" the Evaluate Function

If I was using a calculation directly in a cell there is the Evaluate Formula option that allows you to step through the evaluation and see the results step by step.

What I am looking for is a way to use that tool on the Evaluate command in VBA

Thanks again for all your time

[StephenR]

I don't believe you can directly, but you could "step into" whatever you are evaluating by checking the values of the variables at execution. I'll see if I can think of an example if nobody else responds. Edit: I suppose you could enter the formula into a cell and then evaluate that. Make any sense?

Edit: the other question would be: do you have to use Evaluate?

[snb]

I don't think it's too complicated:

if strn is ValueX(ValueID(3,1,"ROW"))

you can use:

 
Function ValueID(TID As Long, CID As Long, RID As String)
  stop
  ValueID = ValueId1(TID, CID, Mrow)
End Function

Function ValueX(VID As Long)
  stop
  ValueX = ValueX1(VID)
End Function

[Perception]

Hi
Thanks for your comments

Unfornately I need to use the command Evaluate. As far as I am aware it is the only way I can allow the user to set up a form of calculation in a cell and then get VBA to carry it out as though it was coded

The only other way I am aware of is to get VBA to write VBA code dynamically, but that seems very much more complicated

I tried placing a manual stop in the functions, but it never seemed to arrive there. I do not understand why. As I said before, once I removed the autofilter on the Temporary worksheet, this particualar problem resolved itself.

Somehow I need to get the power of the Evaluate auditing tool of the speadsheet calculations - it is very frustrating to know Excel has the facilities, but I just cannot access them within VBA

Thanks

Ian