Using VBA Internal Subroutines

14 May, 2015 PeCo Microsoft Access, 0

Here’s an Access VBA feature that I’ve never seen another developer use; the VBA Internal Subroutine GoSub and Return Statements.

Have you ever delved into VBA code and wondered why a Function or Procedure calls another Function or Procedure numerous times; and perhaps the called Function or Procedure is not called anywhere else?

There is a better way to do this, which will make your VBA code easier to read and maintain.

For example:-

Function MyPrimaryFunction

Loop Until recordset.eof Do

do whatever here

on a condition Call MySecondaryFunction

Repeat

End Function

This could be written as:-

Function MyPrimaryFunction

Loop Until recordset.eof Do

do whatever here

on a condition GoSub MyInternalSubroutine

Repeat

Exit Function

MyInternalSubroutine:

do whatever here

Return

End Function

As you can see in the second example, all of your code is contained in the one Function. Like all things, there are no absolute right and wrong ways to write VBA code. Different circumstances will require a different approach. However, you should always try to keep your code footprint as small as possible, and avoid unnecessary complexity.

How to insert old records into an Auto Numbered table Get your SQL data types right