VBA - advanced videos | Excel VBA Part 25 - Arrays

Posted by Andrew Gould on 12 March 2014

An array is a lot like a variable, only with an array you can store more than one value under the same variable name. This video explains how to work with arrays in VBA, including how to declare basic, fixed-size arrays, populate and read from an array and how to detect the lower and upper bounds of an array. The second half of the video demonstrates more sophisticated arrays including dynamic arrays and multi-dimensional arrays, as well as covering some techniques for speeding up calculations by using arrays. You'll also see how to resize arrays dynamically, and how to transpose an array.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 2 threads Add post
11 Jun 18 at 17:37

Andrew,

We know "usually" the default for passing arguments is by reference, so if the keyword ByRef / ByVal is omitted, the argument will be passed by reference.

In a standard module, this can (and has been) easily proved.

However, I have yet to see an example to prove the case fro a Get / Let / Set Property.

Do these properties also behave in the same way, ie the default is by reference? The reason for my query is if you create a Let Property using Insert -> Procedure -> Property, the keyworb, ByVal, is automatically added.

Does that mean for a Let Property (and I assume also for a Set Property), the default, perhaps the ONLY way to pass arguments is by value?

Is it possible to prove this by way of an example?

Also what about Get Properties? Do they pass by reference by default or by value?

Thanks

12 Jun 18 at 09:11

Good question!  And yes, you're right: passing arguments to Property Let and Property Set procedures passes ByVal, even if you specify ByRef http://excelmatters.com/2016/03/15/when-is-byref-not-byref/

You can test this using the VarPtr function https://bytecomb.com/vba-internals-getting-pointers/

You can start with an example using simple subroutines in a regular module:

Sub AssignNumber()

    Dim a As Long
    
    a = 1
    Debug.Print "a = ", a, VarPtr(a)
    
    IncreaseNumber a
    Debug.Print "a = ", a, VarPtr(a)
    
End Sub

Sub IncreaseNumber(ByVal b As Long)

    b = b + 1
    Debug.Print "b = ", b, VarPtr(b)
    
End Sub

When passing the argument ByVal, your output in the Immediate window will resemble this:

a =            1             11923696 
b =            2             11923688 
a =            1             11923696

The return value of the VarPtr function will be different for a and b.

If you change the signature of the IncreaseNumber procedure to pass ByRef:

Sub IncreaseNumber(ByRef b As Long)

The output will reseble this:

a =            1             11923696 
b =            2             11923696 
a =            2             11923696

The return value of the VarPtr function will be the same for a and b.

You can try the same test with a Property Let procedure (the example below uses a class module called MyClass although you can write the property in a normal module if you prefer):

Public Property Let MyProperty(ByVal d As Long)

    d = d + 1    
    Debug.Print "d = ", d, VarPtr(d)
    
End Property

Add another procedure in a regular module which assigns a value to this property:

Sub TestMyClass()

    Dim mc As New MyClass
    Dim c As Long
    
    c = 1
    Debug.Print "c = ", c, VarPtr(c)
    
    mc.MyProperty = c
    
    Debug.Print "c = ", c, VarPtr(c)
    
End Sub

With ByVal or ByRef, the VarPtr function will always show a different result for c and d:

c =            1             11923692 
d =            2             11923688 
c =            1             11923692

Sorry for the long answer.  Hope it helps!

07 Jul 18 at 08:47

Hi Andrew,

Further to your conclusion that Let and Set properties pass ByVal regardless, why does this article add ByRef?

http://www.informit.com/articles/article.aspx?p=1346864

Here's the code:

Option Explicit

Public Enum anlCellType
    anlCellTypeEmpty
    anlCellTypeLabel
    anlCellTypeConstant
    anlCellTypeFormula
End Enum

Private muCellType As anlCellType
Private mrngCell As Excel.Range

Property Set Cell(ByRef rngCell As Excel.Range)
    Set mrngCell = rngCell
End Property

Property Get Cell() As Excel.Range
    Set Cell = mrngCell
End Property

Property Get CellType() As anlCellType
    CellType = muCellType
End Property

Property Get DescriptiveCellType() As String
    Select Case muCellType
        Case anlCellTypeEmpty
            DescriptiveCellType = "Empty"
        Case anlCellTypeFormula
            DescriptiveCellType = "Formula"
        Case anlCellTypeConstant
            DescriptiveCellType = "Constant"
        Case anlCellTypeLabel
            DescriptiveCellType = "Label"
    End Select
End Property

Public Sub Analyze()
    If IsEmpty(mrngCell) Then
        muCellType = anlCellTypeEmpty
    ElseIf mrngCell.HasFormula Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anlCellTypeConstant
    Else
        muCellType = anlCellTypeLabel
    End If
End Sub

 

Thanks

 

 

warmday  
11 Jul 18 at 11:09

Thanks for the explanation.

Andrew G  
09 Jul 18 at 09:43

Who knows! Perhaps the authors weren't familiar with this behaviour in VBA, I'm not sure.  If you're ever unsure with this sort of thing you can always add your own code to perform a test.  For example, you could modify the Property Set Cell procedure to resemble this:

Property Set Cell(ByRef rngCell As Excel.Range)
    
    'Add these two lines
    Set rngCell = Range("B2")
    Debug.Print "Within property " & rngCell.Address
    
    Set mrngCell = rngCell
    
End Property

You can then add a test procedure in a normal module to check the behaviour:

Sub TestClassModule()

    Dim c As Class1
    Dim r As Range
    
    Set c = New Class1
    Set r = Range("A1")
    
    Debug.Print "Before passing to property " & r.Address
    
    Set c.Cell = r
    
    Debug.Print "After passing to property " & r.Address
    
End Sub

After running the test procedure using ByRef, the output will resemble this:

Before passing to property $A$1
Within property $B$2
After passing to property $A$1

After changing the property to use ByVal the output is the same:

Before passing to property $A$1
Within property $B$2
After passing to property $A$1

Compare this to using a pair of basic subroutines instead of a Property Set procedure:

Sub TestSub()

    Dim r As Range
    
    Set r = Range("A1")
    
    Debug.Print "Before passing to sub " & r.Address
    
    MyCell r
    
    Debug.Print "After passing to sub " & r.Address
    
End Sub


Sub MyCell(ByRef CellToUse As Range)

    Set CellToUse = Range("B2")
    
    Debug.Print "Within sub " & CellToUse.Address
    
End Sub
 

Using ByRef, the output resembles this:

Before passing to sub $A$1
Within sub $B$2
After passing to sub $B$2

And using ByVal produces a different result:

Before passing to sub $A$1
Within sub $B$2
After passing to sub $A$1

I'm not sure whether that makes things any clearer but it does demonstrate that values passed using ByRef behave differently in Property Set (and Let) procedures compared to other types of procedure.

I hope that helps!

12 Jun 18 at 10:50

Andrew,

Thanks for your detailed reply.

I can't say I am familiar with the VarPtr function but the values that the vaiables c and d returned (1, 2, 1) and (1, 2, 1), whether you pass ByVal or ByRef, was enough to convince me that no matter what you write, Let Properties always pass by value.

Can you confirm that for the Get Property too with an example?

Thanks

 

Andrew G  
13 Jun 18 at 06:58

Property Get procedures do actually honour your ByRef/ByVal setting.  You can demonstrate this in a similar way.  Here's a simple property in a class module called MyClass:

Public Property Get MyProperty(ByVal b As Long) As Long

    b = b + 1
    
    Debug.Print "b = ", b, VarPtr(b)
    
    MyProperty = b
    
End Property

Here's a test procedure in a normal module:

Sub TestMyClass()

    Dim mc As New MyClass
    Dim a As Long
    
    a = 1
    
    Debug.Print "a = ", a, VarPtr(a)
    
    Debug.Print "mc.MyProperty = ", mc.MyProperty(a)
    
    Debug.Print "a = ", a, VarPtr(a)
    
End Sub

Running the test procedure using ByVal returns something resembling this:

a =            1             45478828 
b =            2             45478816 
mc.MyProperty =              2 
a =            1             45478828 

And with ByRef:

a =            1             45478828 
b =            2             45478828 
mc.MyProperty =              2 
a =            2             45478828 

I hope that helps!

Andrew G  
13 Jun 18 at 11:34

No problem, happy that it helped!

duggie  
13 Jun 18 at 10:07

Many thanks, I can sleep better now!

10 Jun 18 at 02:40

Hi there,

I tried to do the same as what you have here on your tutorial. I just cannot find out why on the calculation of time lengths for the movies, the last row of result is showing #N/A error for me.

The file is here: https://www.dropbox.com/s/kl6yonheorqocm6/VBA%20Arrays%20sample%20with%20calculation.xlsm?dl=0

 

Can you please check my code?

Option Explicit

Sub CalculateWithArray()

    Dim FilmLengths() As Variant
    Dim Answers() As Variant
    Dim Dimension1 As Long, Counter As Long
    
    Sheet1.Activate
    
    Range("f3", Range("g3").End(xlDown)).ClearContents
    
    ' Assign values of range D to the array FilmLengths
    FilmLengths = Range("d3", Range("d3").End(xlDown))
    ' Assign the number of values of array FilmLengths to the variable Dimension1
    Dimension1 = UBound(FilmLengths, 1)
    ' Resize the dimensions of array Answers according to the number of values of Array FilmLenghts and add 2nd column or dimension
    ReDim Answers(1 To Dimension1, 1 To 2)
    ' Loop over the FilmLengths and return Answers as number of hours and remainder in minutes
    For Counter = 1 To Dimension1
            Answers(Counter, 1) = Int(FilmLengths(Counter, 1) / 60)
            Answers(Counter, 2) = FilmLengths(Counter, 1) Mod 60
    Next Counter
    ' Populate columns F and G with the Answers
    Range("f3", Range("f3").Offset(Counter - 1, 1)) = Answers
    
    Erase FilmLengths
    Erase Answers

End Sub
 

 

Thank you and regards,

 

Ryan

11 Jun 18 at 08:19

Hi Ryan,

The problem is caused by this line near the end of your code:

Range("f3", Range("f3").Offset(Counter - 1, 1)) = Answers

Try changing it to this:

Range("f3", Range("f3").Offset(Dimension1 - 1, 1)) = Answers

The reason you're encountering the problem is because the variable you use to iterate through a For Next loop has a final value that is one greater than the To value you provide.  So, if your loop looks like this:

For Counter = 1 To 3
    'do something
Next Counter

Counter will have a value of 4 when the loop has finished.

I hope that helps!

11 Jun 18 at 08:17

I have found the error. I was using the wrong variable.

Thanks.

11 Jun 18 at 08:21

Ahh you beat me to it as I was writing a reply!  Oh well, it's always more satisfying when you solve a problem by yourself!