VBA MS-Access: Function in a Private Sub to access Sub variables

Question

It's a simple question. I've googled around a bit, but found not much that's pertinent.

I've finished a large-ish Sub and would like to replace duplicate chunks of code with anything that takes a couple of input params and returns a result.

So basically, I'd like to cut a snippet of code and go

Private Sub Command1_Click()

  Function Calc(input) as Integer
    <insert snippet using Sub variables>
  End Function

  Dim base As Integer
  base=1337
  total = Calc(55)
  if total <100 then total = Calc(56)
End Sub

...where the variable 'base' can be used in the Function. Optimally, to also access variables set by the function without having to put them in an array and return that.

Actually, I'd be happy with a simple include or macro for this. It's just to avoid duplicate code.

Solution 1

You can't nest a function within a sub like this, it'll just show you an error when you compile.

Put the function outside the sub and pass it all the variables it needs to do the calculation:

Private Sub Command1_Click()

    Dim base As Integer

    base=1337
    total = Calc(55, base)
    if total <100 then total = Calc(56, base)
End Sub

Function Calc(input as integer, ByRef base as integer) as Integer
    <insert snippet using Sub variables>
End Function

Using the ByRef keyword means the reference for the variable is passed rather than the value, this means if you update base in the function, it changes in the sub.

Solution 2

In languages like Pascal or Modula-2, you can nest procedures and functions. This is not possible in VBA.

If you have many parameters to pass, then instead of having individual variables for the parameters, you can regroup the parameters in a user defined type like this:

Private Type CalcParameters
    number As Double
    otherNumber As Double
    percent As Long
End Type

Function Calc(params As CalcParameters) As Long
    Calc = params.percent * (params.number + params.otherNumber) / 100
End Function

Private Sub Command1_Click()
    Dim params As CalcParameters
    Dim total As Long

    params.number = 77.5
    params.otherNumber = 2.5
    params.percent = 30
    total = Calc(params)
End Sub

---

A more elaborate solution is to use an object-oriented approach, by creating a class module. Let's call it "clsCalcData":

Option Compare Database
Option Explicit

Public Number As Double
Public OtherNumber As Double
Public Percent As Long


Public Function GetTotal() As Long
    GetTotal = Percent * (Number + OtherNumber) / 100
End Function

You would use it like this:

Private Sub Command1_Click()
    Dim calcData As clsCalcData
    Dim total As Long

    Set calcData = New clsCalcData
    calcData.Number = 77.5
    calcData.OtherNumber = 2.5
    calcData.Percent = 30
    total = calcData.GetTotal()
End Sub

Note that here you do not have to pass any parameters at all, since the GetTotal function can access the values directly.

You can think of a class module like being a module that you can make copies of with the new keyword. (It copies only the variables, not the functions and sub procedures.)

Comments

[mwolfe02]

By default, all parameters in VBA are passed by reference. You need to use the ByVal keyword to explicitly pass them by value. I assume @webturner already knows this and, like me, only uses the ByRef keyword to enhance code readability. In my code, if a parameter is marked ByRef it generally means I may intentionally change the value of that parameter within my procedure with the intent that the new value will have meaning after the procedure exits.

[mwolfe02]

Having said all that, I generally try to avoid changing the value of parameters within a procedure because it is too easy to introduce hard to find bugs when you start doing that. If I want to return multiple values from a procedure, I create a function that returns a user-defined type.

[Henrik Erlandsson]

@webturner: I suspected this, but wasn't sure. Is there an in-depth article on this on the web? For example what happens if a non-variable (numerical or string constant, f.ex.) is passed as a parameter that is changed in the Function. As I take it both 'input' and 'base' in your function are byRef (according to mwolfe02)?

[Stephen Turner]

Your best bet is to try it and see, you can test functions using the 'immediate window' (ctrl-g)

[Henrik Erlandsson]

It was a theoretical question, rather than something I need to know the answer to for this application (hence asking for a good article). Another question is more important: if the calculation tries to use variables set in the caller sub (that are not modified by the function), they are Empty. Any way around that, apart from regrouping as Olivier suggests below? (Or putting them all in an array, to not to have to put them all as parameters.)

[Henrik Erlandsson]

That's a bit reminiscent of creating an array and writing to code to put them all in that. Either way, I have to manually type their names one by one in order to pass them into the Function. This includes declaring them all one by one as Globals. I'm at the end of the road, it seems. It's just that the advantage of not having to declare variables seems mutually exclusive to re-using identical code chunks in this case, and I really think it's my job as a programmer to reduce redundancy (in the eternal fight against entropy, save us it will (c) Yoda) :)

[Stephen Turner]

Sorry Henrik, there's not much depth to it but here's a link. The variables shouldn't be empty if you passed them as parameters, but that's a topic for a new question, stack overflow isn't setup for discussions.

[Olivier Jacot-Descombes]

I don't put any variables into an array. In the first example I put them into a user defined type, in the second into an object. There is no redundant code. The variables are declared only once and also the values are assigned only once.