<

VBA: curious usage of recursion

Published on
9,205 Points
3,205 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
Comment
Author:Exceleved
0 Comments

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month