This page has 4 threads | Add post

Video tutorials

Finding our videos useful? Please consider helping us to share more!

Training videos for learning VBA - ADVANCED

We have 15 VBA - advanced videos listed below.

Flappy Owl in Excel VBA

Posted by Andrew Gould on 11 April 2014

A quick introductory video showing off the Flappy Bird game, built entirely in Excel VBA!

Show this video

Excel VBA Part 15.2 - Find and FindNext

Posted by Andrew Gould on 13 October 2014

You can use the Find and FindNext methods in Excel VBA to find values in a worksheet - exactly as the name suggests! This video explains how the methods work, including how to make the search case-sensitive, how to find full or partial matches and how to use Do While loops to find all the instances of something that you're searching for.

Show this video

Excel VBA Part 20a - Application Events

Posted by Andrew Gould on 26 November 2015

In an earlier video in the series we've seen how to write event-handlers for workbooks and worksheets; this video teaches you how to access events of the Excel application itself! You'll learn the significance of the WithEvents keyword, a couple of the basic application events such as the new workbook event, and how to use a basic class module to keep your application event handlers neatly organised.

Show this video

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.

Show this video

Excel VBA Part 26 - Constants and Enumerations

Posted by Andrew Gould on 17 March 2014

Constants in VBA are values which don't change - there are lots of built-in examples and you can also create your own. This video teaches you how to declare and use constants in your VBA procedures. You'll also learn how to create collections of constants called Enumerations, how to reference them in your code and also how to use them as a new data type for variables and parameters of procedures.

Show this video

Excel VBA Part 33 - Creating Add Ins

Posted by Andrew Gould on 13 October 2014

Creating an Excel VBA Add-In is a great way to make your custom functions available to other Excel workbooks and VBA projects. This video teaches you how to create an add-in from scratch, including how to write VBA functions, where to save your add-in, how to enable an add-in to make it available to other workbooks and how to reference an add-in from a VBA project.

Show this video

Excel VBA - Disable Screen Updates

Posted by Andrew Gould on 26 November 2015

You can give your VBA code a quick performance boost by simply disabling screen updates while your code runs. This video explains how to achieve that, as well as how to create a basic timer system to test the results.

Show this video

Excel VBA Part 43.1 - ByRef and ByVal

Posted by Andrew Gould on 17 October 2016

Passing values to other procedures is something you'll do commonly in VBA and the ByRef and ByVal keywords control exactly what happens to those values. This video explains the theory of passing information by reference and by value, as well as covering a couple of practical examples to demonstrate how to use the ByRef and ByVal keywords effectively.

Show this video

Excel VBA Part 43.2 - ByRef and ByVal (When to use Parentheses)

Posted by Andrew Gould on 24 October 2016

When to use parentheses (round brackets) is one of the apparently annoyingly inconsistent features of VBA. There are some clearly defined rules which dictate when you should and shouldn't use parentheses and this video attempts to explain them!

Show this video

Excel VBA Part 44 - Making Excel Talk

Posted by Andrew Gould on 17 October 2016

One of Excel's more unusual features is its ability to speak to you! Not intelligently, of course, but you can make Excel vocalise a specific phrase or the contents of cells, or any other expression you can think of. This video shows you how to access the speech feature in Excel and use it to create a couple of small but fun examples.

Show this video

Excel VBA Part 45 - Finding the Last Used Row, Column and Cell

Posted by Andrew Gould on 25 October 2016

There are several techniques you can use in VBA to find the last row, column or cell in a worksheet. This video shows you a range of options including using the End and Offset properties; the CurrentRegion property of a Range and the UsedRange property of a Worksheet; the LastCell option of the SpecialCells method and, finally, the trusty Find method.

Show this video

How do I make a VBA event work in every workbook?

Posted by Andrew Gould on 31 July 2024

This video shows you how to use VBA in Excel to enable application-level events which will run in any workbook that you have open. You'll learn how to create a class module, how to declare an application variable with events and how to activate application-level events.

Show this video

How do I copy data without using copy and paste in Excel VBA?

Posted by Andrew Gould on 06 April 2021

Copying and pasting data is the standard way to move information between cells in Excel but did you know that you can transfer data without using copy and paste in Excel VBA? This video explains how to perform this feat by assigning the values of a range to another range. As a bonus, the video shows you how to loop through the worksheets collection, copying the data from each one to build a master list.

Show this video

How do I split text into multiple rows using Excel VBA?

Posted by Andrew Gould on 19 April 2021

This video shows you how to use the Split function in VBA to divide a string into multiple substrings based on a delimiter character. You'll also see how to capture the result of the Split function in an array and write the contents of the array into cells in a worksheet after inserting new rows to make space for the new values.

Show this video

Wise Owl Does Wordle

Posted by Andrew Gould on 30 January 2022

Have you played Wordle yet? You should, it's great! We're a bit obsessed with it at Wise Owl so Andrew, Andy and Sam have all been working their own versions of the game in VBA, Python and Power BI(!) respectively. Check out this quick intro to Andrew's VBA version in this video.

Show this video

You might also like to consider booking a place on one of our online or classroom VBA courses, where you could meet the owls behind the videos!

This page has 4 threads

Add a new post

From:	warmday
When:	18 Oct 21 at 15:06

On the poiint regarding adding ByRef / ByVal when passing arguments, I have noticed the VB editor automatically adds ByVal when it is the default, eg when adding a Property Get / Let.

In the Worksheet_BeforeDoubleClick event, the VB editor adds BaVal to Target but omits for Cancel, like so:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Is Cancel ByVal or ByRef?

Thanks

From:	Andrew G
When:	19 Oct 21 at 06:37

Normally when passing arguments in VBA the default is ByRef unless specified otherwise. However, see my replies to Duggie below for when this isn't always the case!

From:	warmday
When:	05 Nov 21 at 11:14

Thanks Andrew,

So If I understand you correctly, usually (ie when the VB editor omits it), it's ByRef.

In the case of Let and Set, they're ByVal (because the VB editor explicitly puts it).

Therefore in the case of Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean), Cancel must be ByRef.

From:	Andrew G
When:	08 Nov 21 at 07:13

That sounds right to me!

From:	duggie
When:	03 Jan 20 at 16:31

I came across this by accident and wondered if other readers have experienced it as well. I thought hovering the mouse over a variable only shows the value of the variable but not change it.

This is in a standard module:

Option Explicit

Sub Start()

    Dim abc As Class1

    Set abc = New Class1

    Dim a As Long

    a = 1

    Debug.Print abc.MyProperty(a)

End Sub

This is Class1:

Option Explicit

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

b = b + 1

MyProperty = b

End Property

If I run the code but put a break point here:

Debug.Print abc.MyProperty(a)

and hover the mouse over abc.MyProperty(a), I see:

abc.MyProperty(a) = 2

If I move my mouse away then back over abc.MyProperty(a), I see the value increment by 1 each time. Why is this? II thought hovering doesn't change anything.

Thanks.

From:	duggie
When:	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

From:	Andrew G
When:	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!

From:	warmday
When:	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

From:	warmday
When:	11 Jul 18 at 11:09

Thanks for the explanation.

From:	Andrew G
When:	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!

From:	duggie
When:	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

From:	Andrew G
When:	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!

From:	Andrew G
When:	13 Jun 18 at 11:34

No problem, happy that it helped!

From:	duggie
When:	13 Jun 18 at 10:07

Many thanks, I can sleep better now!

From:	ryan051788
When:	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

From:	Andrew G
When:	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!

From:	ryan051788
When:	11 Jun 18 at 08:17

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

Thanks.

From:	Andrew G
When:	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!

From:	ryan051788
When:	04 Aug 18 at 13:29

Hi Andrew,

Thanks for the help. I was not very good at VBA yet, so I had to dig in more and more, and as I examine the codes 2 or 3 times then I found errors or sometimes a more efficient way.

Thanks again.

Regards,

Ryan

Add a new post

VIDEOS BY CATEGORY

VBA CATEGORIES

VBA - ADVANCED VIDEOS

VIDEOS BY AUTHOR

Training videos for learning VBA - ADVANCED

Flappy Owl in Excel VBA

Excel VBA Part 15.2 - Find and FindNext

Excel VBA Part 20a - Application Events

Excel VBA Part 25 - Arrays

Excel VBA Part 26 - Constants and Enumerations

Excel VBA Part 33 - Creating Add Ins

Excel VBA - Disable Screen Updates

Excel VBA Part 43.1 - ByRef and ByVal

Excel VBA Part 43.2 - ByRef and ByVal (When to use Parentheses)

Excel VBA Part 44 - Making Excel Talk

Excel VBA Part 45 - Finding the Last Used Row, Column and Cell

How do I make a VBA event work in every workbook?

How do I copy data without using copy and paste in Excel VBA?

How do I split text into multiple rows using Excel VBA?

Wise Owl Does Wordle

Head office

London

Manchester