A Variable Scope
by admin on Wednesday, September 26th, 2012 | No Comments
Key points:
A private procedure can be called from any procedure in the same module.
The default scope for a sub procedure is “Public” unless you specifically designate it as Private.
Store all your procedures and functions you plan to use in your application in “Modules”, which you can call from your other database objects.
Here is the video:
Here is a handy chart for you to use when determining VBA variable scope:
Keyword | Result |
---|---|
Dim | The variable is only available inside the procedure in which it is declared. |
Private | The variable is available to all procedures in the module in which it is declared, but not to procedures in other modules in the application. |
Public | The variable is available to all procedures in all modules in the application. |
Public Declare Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" (ByVal hwnd As Long, _ ByVal szApp As String, ByVal szOtherStuff As String, ByVal hIcon As Long) As Long Public Declare Function GetActiveWindow Lib "user32" () As Long Public m_blnCloseAccess As Boolean Public m_strControl As String Public m_strForm As String Public m_strSearchText As String Public Sub About() Dim hwnd As Long Dim windowname As String Dim nl$ Dim x nl$ = Chr$(10) + Chr$(13) hwnd = GetActiveWindow() x = ShellAbout(hwnd, "LoeblCom Services Database", nl$ + Chr$(169) + "LoeblCom Services 1999-2004, erik@loebl.com" + nl$, 0) End Sub Public Function IsLoaded(ThisForm As String) As Boolean 'is the form loaded or not If SysCmd(acSysCmdGetObjectState, acForm, ThisForm) <> 0 Then IsLoaded = True Else IsLoaded = False 'the form is closed End If End Function Public Sub OpenMainSWB() 'DoCmd.OpenForm "MainSWB" If IsLoaded("MainSWB") Then Forms("MainSWB").Visible = True End If End Sub Public Sub CloseMainSWB() 'DoCmd.Close acForm, "MainSWB" If IsLoaded("MainSWB") Then Forms("MainSWB").Visible = False End If End Sub Public Sub OpenReportSWB() 'DoCmd.OpenForm "ReportsSWB" If IsLoaded("ReportsSWB") Then Forms("ReportsSWB").Visible = True End If End Sub Public Sub CloseReportSWB() 'DoCmd.Close acForm, "ReportsSWB" If IsLoaded("ReportsSWB") Then Forms("ReportsSWB").Visible = False End If End Sub Public Function BreakString(SomeText, Delimiter, before_comma) As Variant 'Send an array back as a result to another variant in the calling proc Dim intStart As Integer Dim intNextDelim As Integer Dim strSplitText() As String Dim intCounter As Integer If Right(SomeText, 1) <> Delimiter Then 'make sure the last column gets counted SomeText = SomeText & Delimiter End If intStart = 1 'the starting position always changes, so it is a variable intNextDelim = InStr(1, SomeText, Delimiter) Do While intNextDelim >= 1 'stop when 0 (there are no more delimiters) intCounter = intCounter + 1 ReDim Preserve strSplitText(intCounter) strSplitText(intCounter) = Mid(SomeText, intStart, intNextDelim - intStart) 'store value in an array If intCounter = before_comma Then BreakString = strSplitText(intCounter) Exit Function End If intStart = intNextDelim + 1 'move the start position up one intNextDelim = InStr(intStart, SomeText, Delimiter) 'find the next delimiter Loop BreakString = strSplitText 'return the array to the variant End Function