A Variable Scope

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

<< How To Make A VBA Function | VBA Debug Part 1 >>


Leave a Reply