<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

VBA: curious usage of recursion

Published on
9,231 Points
3,231 Views
Last Modified:
Approved
Community Pick
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure:
First, sum up all the individual digits in the number
If that sum value has more than one digit, then sum up all digits in that sum...
...and so on.
It was clear that a recursive solution could be used and I wrote the following function:
Function chk(ByVal x As String) As Long
Dim i As Long
For i = 1 To Len(x)
    chk = chk + Mid(x, i, 1)
Next
If chk > 9 Then chk = chk(chk)
End Function

Open in new window

I wondered that the compiler did not find an error in the expression...
chk = chk(chk)
...having "understood" that the first and the third uses of "chk" relate to the implicit local variable (the return value for the function) and the second "chk" is a call to the function.  I also appreciated that the function argument was being converted to proper type without any additional programming efforts.

Using in VBScript

Then I studied whether type declarations of variables could be avoided so that the function could be used in VBScript where all variables have Variant type.

If a function type is not declared (i.e. if it is declared as Variant by default) it leads to endless recursion because the right side of the operator...
chk = chk + Mid(x, i, 1)
...is treated as string concatenation and, after Next, chk is a string equal to x.  This can be avoided by explicit conversion of a digit:
chk = chk + CLng(Mid(x, i, 1))

Open in new window

or by implicit conversion in numeric operation:
    chk = chk - Mid(x, i, 1)
Next

chk = -chk

Open in new window

If the ByVal keyword is omitted:
Function chk(x As String) As Long 'causes compiler error

Open in new window

...then the compiler raises an error "ByRef argument type mismatch".  But if the argument type is also omitted then there is no error.

Finally, the check-digit producing function for VBScript looks like this:
Function chk(x)
Dim i
For i = 1 To Len(x)
    chk = chk + CLng(Mid(x, i, 1))
Next
If chk > 9 Then chk = chk(chk)
End Function

Open in new window

Another purpose of this article is to show once again that a function name can be used as a normal variable within the function code; i.e., there is no need to declare an auxiliary variable as some programmers do:
Function chk(x)
Dim i, aux  'aux is actually redundant'
For i = 1 To Len(x)
    aux = aux + CLng(Mid(x, i, 1))
Next
chk = aux
If chk > 9 Then chk = chk(chk)
End Function

Open in new window

0
Author:Exceleved
0 Comments

Featured Post

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
See the Basics of Office 365's Note Taking app, OneNote

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month