CODEWITHSUNDEEP
vbaexcel
Alec
Alec

Reputation: 643

vba better way to set all values in array to same thing

This is a matter of personal curiosity.

In VBA, if I have an array of size 2:

Dim r(1) as Variant

And I want both values in the array to be -1. I can do this:

r(0)=-1
r(1)=-1

Or I could iterate through both with a loop and set them to -1.

So my question is, is there any way I can set all the values in an array to the same thing without iterating?

Or, is there any way I can do something like:

r = array(-1,-1)

This might be a really stupid question but I can't seem to find the answer.

Upvotes: 6

Views: 16944

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

I'm not very good at drawing images. But this should give you and make clear the concepts associated with variant arrays.

variant versus variant array

Upvotes: 9

Siddharth Rout
Siddharth Rout

Reputation: 149305

Yes you can do it. But then you have to take care while declaring the array

Example

Option Explicit

Sub Sample()
    Dim r As Variant '<~~

    r = Array(-1, -1)

    Debug.Print r(0)
    Debug.Print r(1)
End Sub

FOLLOWUP

See the Excel Help File :) The Array Function returns a Variant containing an array. You cannot assign an array to another array directly. To assign an array to another array you have to use this method.

Option Explicit

Sub Sample()
    Dim r(1) As Variant 
    Dim s As Variant
    Dim i As Long

    s = Array(-1, -1)

    For i = LBound(r) To UBound(r)
       r(i) = s(i)
    Next

    Debug.Print r(0)
    Debug.Print r(1)
End Sub

Upvotes: 5

Related Questions