CODEWITHSUNDEEP
ms-accessvariablesvbascope
Henrik Erlandsson
Henrik Erlandsson

Reputation: 3830

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

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.

Upvotes: 2

Views: 14251

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112512

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.)

Upvotes: 3

Stephen Turner
Stephen Turner

Reputation: 7314

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.

Upvotes: 7

Related Questions