So far, the sample functions discussed above return only single values. A function can possibly return an array of values.
The following MySplit function accepts a string of characters as its argument and returns a zero-based, one-dimensional array containing each character in the string.
Function MySplit(MyString As String) As String() Dim tmp As String
tmp = StrConv(MyString, vbUnicode) tmp = Left(tmp, Len(tmp) - 1)
MySplit = Split(tmp, Chr(0)) End Function
Exercise 3-8: Executing the MySplit function from the Immediate window
After storing the MySplit function in a standard VBA module, enter the following statement in the Immediate window to test the function:
? Join(mysplit("Hello World!"),"|") It returns H|e|l|l|o| |W|o|r|l|d|!
Exercise 3-9: Using MySplit as a worksheet function
Since the MySplit function returns an array of string, to use the function in a worksheet formula, you need to enter the formula as an array formula.
After storing the function in a standard VBA module, execute the following steps to test the function:
1. Select a range of cells, for example the range A21:L21.
2. Type the following array formula into the formula bar and press Ctrl+Shift+Enter (not just
Enter):
=MySplit("Hello World!")
It returns individual characters of the string "Hello World!" into the range A21:L21.
3. To return individual characters into a vertical range, select a vertical range of cells, for
example the range M21:M32, and enter the following array formula into the formula bar by pressing Ctrl+Shift+Enter (not just Enter):
=TRANSPOSE(MySplit("Hello World!"))
If you need to edit an array formula, execute the following steps:
1. Select any cell in the array formula range.
2. Press F2 or click the formula bar and make changes to the formula.
3. Press Ctrl+Shift+Enter to accept the changes.
To delete an array formula, select any cell in the array formula range, press Ctrl+/ to select all the cells in the range and press Delete.
Exercise 3-10: Another way of using MySplit as a worksheet function
You can also enter an array formula into a range of cells by using VBA code. This can be done by the FormulaArray property of a Range object.
For example, after storing the MySplit function in a standard VBA module, execute the following steps:
1. Enter the following statement in the Immediate window
Range("A22:L22").FormulaArray = "=MySplit(""Hello World!"")"
2. Press Alt+F11 to activate Excel and see the result.
The next function ReturnArryDemo1 returns a 3-by-4 two-dimensional array:
Function ReturnArryDemo1() As Long() Dim L() As Long
Dim R As Long, C As Long ReDim L(1 To 3, 1 To 4)
For R = 1 To 3
For C = 1 To 4
Exercise 3-11: Returning a two-dimensional array into a range of cells
After storing the ReturnArryDemo1 function (which returns a two-dimensional array of numbers) in a standard VBA module, execute the following steps to use the function in a worksheet formula:
1. Select a range of 3-by-4 cells, for example the range A25:D27.
2. Type the following array formula into the formula bar and press Ctrl+Shift+Enter (not just
Enter):
=ReturnArryDemo1()
The function returns an array of numbers into the selected cells.
3. Select another range that is larger than 3-by-4 cells, for example the range E25:I29.
4. Type the following array formula into the formula bar and press Ctrl+Shift+Enter (not just
Enter):
=ReturnArryDemo1()
The function not only returns numbers into the selected cells, but also fills out the unused cells with #N/A errors.
You can then modify ReturnArryDemo1 to fill out the unused cells either with empty characters or numbers. The following ReturnArryDemo2 function discusses the latter: to fill out with numbers.
Function ReturnArryDemo2() As Long() Dim L() As Long
Dim R As Long, C As Long Dim nR As Long, nC As Long
'Determine the size of a selected range With
For R = 1 To nR For C = 1 To nC L(R, C) = R + C Next C
Next R
ReturnArryDemo2 = L End Function
The VBA code in the ReturnArryDemo2 function first determines the size of a selected range. Then, the dimensions of the array (that to be returned) is resized according to the size of the selected range.
Debugging
Debugging is a process of finding and fixing errors in program code. The following is a typical way of using the debugger to step through the code and identify errors:
1. Add breakpoints at particular lines of code, where you suspect errors, by clicking at the grey
left margin of the code window.
2. Press F5 to execute the VBA code.
VBE executes the code until the first breakpoint is met, putting the code into break mode. The particular line of code at the breakpoint is not executed yet.
3. Choose one of the three operations at the breakpoint:
Operation Shortcut
key Action of the debugger
Continue F5 It continues executing until the next breakpoint.
Step Into F8
If the current point beak does not have a procedure (a
Function or Sub procedure) to be called, it executes the line of code. Otherwise, it goes into the procedure. To step out of a procedure, press Ctrl+Shift+F8.
Step Over Shift+F8
If the current point beak has a procedure to be called, it treats the procedure as a single
instruction, instead of going into the procedure.
The operations listed in the table above can alternatively be accessed from the Debug menu on the menu bar.
4. As you step through the code to identify possible errors, you can hover the mouse pointer over
any variable to examine its current value. If VBE does not display the value, enable the Auto Data Tips option in the Editor tab of the VBE Options dialog box. (Choose Tools | Options from the menu bar to display the Options dialog box.)
Alternatively, you may use the Immediate window to check the current value of a variable when the code is in break mode. For example, enter the following statement in the Immediate window:
? myVariable
In addition, you may use the Locals window and the Watch window (which are the topics to be discussed next) to monitor the values of variables when debugging your VBA code.