BY CATEGORY
VBA CATEGORIES
VBA - ADVANCED VIDEOS
- 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
VBA - advanced videos | 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.
You can increase the size of the video:
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:
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.
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
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!
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
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!
No problem, happy that it helped!
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
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!
I have found the error. I was using the wrong variable.
Thanks.
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!