1

I want to declare a Public(?) Variable when an Access UserForm loads, and reuse those variables multiple times . I have several AfterUpdate subs that run and use a common variable i define within in the vba code. I know i can define the variable in a sub, then call that sub within the AfterUpdate subs, but i do not want half of my code just to be calling the same subs again and wish to make it cleaner.

I have tried to figure out the Public/Global Variable method, but i can not for the life of me understand it.

A simple example is below, where there are four textboxes called A,B,C, and D and i want the var and error message to be variable that are constants whenever this form loads.

Option Compare Database

'declare constant variables
Public var as String
Public error_message as String
var=5
error_message="input a lower value"

Private Sub A_AfterUpdate()
If A.Value > var Then
    MsgBox error_message
End If
End Sub

Private Sub B_AfterUpdate()
If B.Value > var Then
    MsgBox error_message
End If
End Sub

Private Sub C_AfterUpdate()
If C.Value > var Then
    MsgBox error_message
End If
End Sub

Private Sub D_AfterUpdate()
If D.Value > var Then
    MsgBox error_message
End If
End Sub

How can i change this format so it works?. Again i know i can do call a Sub where var is defined and return it, but i would rather not do that for every AfterUpdate.

Thanks.

JRob23123
  • 37
  • 4
  • you write _"How can i change this format so it works?"_ Can you tell us what does not work with your code, as presented? –  Jan 06 '19 at 14:59

1 Answers1

3

  1. You're correct - it sounds like you want a "global variable"

  2. You're also correct - the way you do this is:

    a) define the variable OUTSIDE of a subroutine or function.

    b) Mark the variable "Public".

  3. You probably DON'T want to name your variables "var" (or "A", "B" or "C") if you can avoid it. I assume those were just "examples".

  4. It's common to create a new, separate VBA module just for globals. Here, you can define the variable "Global".

  5. I also like to put "Option Explicit" in all of my modules.

You might also be interested in this link:

VBA: What is the difference between Dim, Global, Public, and Private as Modular Field Access Modifiers?

paulsm4
  • 114,292
  • 17
  • 138
  • 190