Put a VBA variable description inside a cell

Question

everybody. I have the following code in vba:

With tb.DataBodyRange
    MyRangeArray = Array(Worksheets(.Cells(1, 1).Value).Range(.Cells(1, 2).Value), _
                    Worksheets(.Cells(2, 1).Value).Range(.Cells(2, 2).Value), _
                    Worksheets(.Cells(3, 1).Value).Range(.Cells(3, 2).Value), _
                    Worksheets(.Cells(4, 1).Value).Range(.Cells(4, 2).Value), _
                    Worksheets(.Cells(5, 1).Value).Range(.Cells(5, 2).Value), _
                    Worksheets(.Cells(6, 1).Value).Range(.Cells(6, 2).Value))

End With

I am trying to set "MyRangeArray" to a cell value, like:

Sheets("Home").Visible = True
MyRangeArray = Range("E1").Value

and my range"E1" in Excel would be exactly the same text of the code.

I tried to make MyRangeArray = Range("E1").text (and .text2 and whatever) but cant make it work. Can anybody help? Thanks!

So in the cell `E1` on the sheet you store actual VBA code, `Array(...)`, and you want to execute it when you read the cell's value? — GSerg, Oct 30 '15 at 14:22
yes, is that possible? because my problem is that the array is variable, and in excel i know how to write the formula to get the desired range — Eduardo Danon, Oct 30 '15 at 15:55

score 2 · Answer 1 · edited May 23 '17 at 12:31

2

No, you cannot execute VBA code from the sheet in this way (which is a good thing).

If you know how to write a formula that points to a range, convert it to equivalent VBA statements using functions found under Application.WorksheetFunction. You will find it mostly looks the same after conversion, and you can often avoid duplicating code that way because you can store temporary results in variables.

If you cannot be bothered to convert your formulas, use Application.Evaluate. E.g.,

Dim v As Variant
v = Application.Evaluate("INDEX(D8:F10, 2, 0)")

will put into v an array of three values found in D9:F9.
Note however that the formula will be resolved relative to the active sheet. You often want to avoid that, which is why you want to convert the formula to a VBA statement.

If you cannot be bothered to type Application.Evaluate, use square brackets:

Dim v As Variant
v = [INDEX(D8:F10, 2, 0)]

edited May 23 '17 at 12:31

Community

1
1

answered Oct 30 '15 at 16:33

GSerg

76,472
17
159
346

Thanks for your answer! This was the way i considered to set a variable array. How could i do that in order to the user not have to change manually? The array would consist on the number of lines written (so, number of slide pages) – Eduardo Danon Nov 03 '15 at 11:15
I'm not sure what you're talking about @EduardoDanon. Do you want to know [how many rows a range has](http://stackoverflow.com/q/114493/11683)? – GSerg Nov 03 '15 at 12:34
Thanks for your answer! I want to change both: 1st array is the list of slides in PPT to get each range. (number of populated cells in the table tb, for example) 2nd array is the Excel range (First column gets the name of the sheet and second column the range of the sheet) My point is that i dont want the user to manually change the vba code based on the number of slides, i want the macro to detect automatically how the array will be – Eduardo Danon Nov 03 '15 at 12:49

Put a VBA variable description inside a cell

1 Answers1