none
How to monitor the value of a range while my VBA executes? RRS feed

  • Question

  • Hello,

    I often use MsgBox to monitor the value of a string variable in my VBA code as it runs.

    But how can I monitor the value of a range variable?

    ================================

    Dim sFormula As String
    Dim rFormula As Range

    sFormula = ActiveCell.Validation.Formula1

           MsgBox (sFormula)                                               ' THIS WORKS

    ----

    Set rFormula = Evaluate(ActiveCell.Validation.Formula1)

          MsgBox (rFormula)                                                'THIS DOES NOT WORK

    =====================================               

    The 1st MsgBox works because sFormula is a string.

    The 2nd MsgBox does not work because rFormula is not a string.

    So, how can I see what rFormula returns?

    In general, how can we monitor the value of any type of variables as the code executes. I am not very good at debugging.

    Thanks

    Leon

    Tuesday, October 29, 2019 10:18 AM

Answers

  • A multi-cell range is too complicated to 'monitor' easily

    You can do things like

    Debug.Print rFormula(1, 1).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Tuesday, October 29, 2019 1:20 PM
    Tuesday, October 29, 2019 1:13 PM

All replies

  • I don't understand what you want. Validation.Formula1 is a String, not a Range...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 29, 2019 12:03 PM
  • Hello, Hans

    Thanks for replying.

    This is from a sample (fully working!) add-in from the web. I am trying to analyse it to adapt it to my requirements.

    -----

    sFormula = ActiveCell.Validation.Formula1

    You are right: ActiveCell.Validation.Formula1 is a string.

    ------

    But somewhere the author converts the string into a range using the EVALUATE function:

    Set rFormula = EVALUATE(ActiveCell.Validation.Formula1)

    -----

    I used the Watch window to monitor the value of sFormula (which is a string)

    In my example, I saw this on the watch window: 

    =$A$1:$A$18

    It's OK, because this is indeed the RANGE where I put my validation list in Excel

    -----

    But I cannot use the Watch window to monitor the value of rFormula (which is a Range, not a String).

    -----

    My question is:

    Is there any way I can check (i.e. see!) what rFormula returns?

    Leon

    Tuesday, October 29, 2019 12:34 PM
  • What would you like to know about the range?

    You already know its address - it is provided by sFormula.

    The range A1:A18 doesn't have a single value - since it contains 18 cells, it has an array of 18 values. MsgBox cannot display an array.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 29, 2019 12:47 PM
  • > MsgBox cannot display an array.

    That's right!

    -----

    In my simplified example, I know what is in my range.

    But suppose the program is complex (and the add-in I am working on is really complex for me), and something goes wrong.

    And I want to debug to know what has gone wrong.

    And suppose I want to monitor the value of any variable (say X, which happens to be a range).

    Is there no way I can monitor the value of a range?

    How do professional programmers do this?

    Tuesday, October 29, 2019 1:06 PM
  • A multi-cell range is too complicated to 'monitor' easily

    You can do things like

    Debug.Print rFormula(1, 1).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Tuesday, October 29, 2019 1:20 PM
    Tuesday, October 29, 2019 1:13 PM
  • Ah! That's what I wanted!

    I can even use MsgBox now!

    Thanks a lot

    Leon

    Tuesday, October 29, 2019 1:21 PM