Using array instead of ranges in VBA

Question

I've only been using VBA for a couple of weeks now so bear with me.

I'm trying to change a macro so that it reads from an array instead of a range. One of the sections I need to change uses .formulaR1C1 to run a vlookup, but im having a hard time trying to work out how this can be done with the array.

An example of this is the below line:

.Range("M2:L" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet2!R1C1:R4C10, 3, 0)"

I'm not too sure on whether I can set the value of an array to a formula, as I've done above, or if I have to maybe store the value as a String and then edit the value of the cell later when printing the column back on to the worksheet.

What I have so far is below:

For i = 2 To lastrow
arr(i, 13).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R4C10,3,0)"
Next

Thanks in advance!

You can't set an array equal to a formula. You could set it to contain the formula e.g. arr(i,13) = "=VLOOKUP(RC[-1],WMSPAM!R1C1:R4C10,3,0)" Then when you write the array back to the sheet you could set the cell.FormulaR1C1 property equal to the arrays formula. — Tom
– Tom, Commented May 15, 2017 at 8:18
You could use Application.Evaluate to set the result of the formula in the array, however I'm not sure you can achieve it with non-explicit R1C1 notation (e.g. RC[-1]) — Tom
– Tom, Commented May 15, 2017 at 8:21
Why do you need this to be done with an array in the first place? — omegastripes
– omegastripes, Commented May 15, 2017 at 8:37
Hi Omega, this is only a small part of the code, and its running frequently over tens of thousands of rows, so using an array is a performance gain. — DSTL
– DSTL, Commented May 15, 2017 at 8:40

Carrosive · Accepted Answer · 2017-05-15 09:07:55Z

2

Storing the value as a string would certainly be a valid option. Simply write the formula in your array, presuming it is of Variant / String type, and when you put the data back into a worksheet you can use .FormulaR1C1 on a Cell (Range) object to apply it as a formula.

arr(i, 13) = "=VLOOKUP(RC[-1],WMSPAM!R1C1:R4C10,3,0)"
...
Range("M2").FormulaR1C1 = Arr(1,13)

I believe this approach is likely the most effective and most easily maintained for you. As you are learning and appear to be curious of what is possible, here are a couple more examples of how you could approach this, with some further explanation.

.FormulaR1C1 is a Range object method and so the only way it could be called on an Array item would be if that item was a Range object.

Dim arr(0 To 10) As Range
Set arr(0) = Range("A1")
arr(0).FormulaR1C1 = "=2+2"

Note that as Ranges are an Object (of reference type), this operation will directly effect the Range specified in the array. In the above example, the formula "=2+2" will be placed into cell A1. You can learn more about the difference between Reference and Value types here.

If your array contains only values, the other way to achieve what you need is to use the WorksheetFunction object. With this object you can access the formula functions, that you would use in worksheet, within VBA.

WorksheetFunction.VLookup(Arg1, Arg2, Arg3, Arg4)

As with anything in writing code the WorksheetFunction methods take some trial and error to get them to work how you would expect, but in my experience these specifically can be a tricky to implement, there are though some cases where they can be very useful!

You can read more about the VLookup method here.

answered May 15, 2017 at 9:07

Carrosive

9092 gold badges10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DSTL Over a year ago

Looks like I will go with the first option. This is similar to my 'workaround' I had in my head, but I wasnt sure on how to put pen to paper. Thanks for taking the time to explain!

Carrosive Over a year ago

@DMI No problem, thanks for taking the time to explain your problem and thoughts properly in your question. It's nice to answer a question that isn't just along the lines of "Here is my code, please fix it for me", which unfortunately seems all too common in the VBA tag.

omegastripes · Accepted Answer · 2017-05-15 08:51:12Z

1

You may try to use .Offset():

Sub Test()

    lastrow = 11
    With Sheets(1)
        Set Rng = .Range("M2:L" & lastrow)
    End With

    For i = 0 To Rng.Rows.Count - 1
        Rng.Offset(i, 12).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R4C10,3,0)"
    Next

End Sub

answered May 15, 2017 at 8:51

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

1 Comment

DSTL Over a year ago

Hi omega, thanks for your suggestion. The code I have using ranges works, I'm just trying to find a way to get this to work using an array

jkpieterse · Accepted Answer · 2017-05-15 09:16:50Z

0

I'm not sure what you are asking. Your first code line writes a formula to a two-column range in the most efficient way there is already, your second snippet shows the less efficient way of doing it one cell at the time. If the goal is to use the vlookup to fill cells and then dispose of the formula, one efficient way is this:

.Range("M2:L" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet2!R1C1:R4C10, 3, 0)"
.Range("M2:L" & lastrow).Value2 = .Range("M2:L" & lastrow).Value2

answered May 15, 2017 at 9:16

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

Using array instead of ranges in VBA

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related