This page contains commonly used VBA in either Microsoft Excel or Access
|
Table of Contents
|
Function
Public Function FUNCTION_NAME(ExternalVariable As String) As String
Dim InternalVariable As String
InternalVariable = ExternalVariable
FUNCTION_NAME = InternalVariable
End Function
String Function
'A: EMPTY CELL
If Not IsNull(rec("FIELDNAME")) Then
End If
'B: SEARCH STRING
If InStr(1, strVariableMain, strVariableSub, vbTextCompare) <> 0 Then
End If
Random Function
Randomize
Int(UPPERNUMBER * Rnd) + LOWERNUMBER
Class
'DECLARE PRIVATE CLASS VARIABLES
Private CLS_VARIABLE As Variant
'INPUT VARIABLE
**Public Property Let INPUT_VARIABLE(MOD_VARIABLE As Variant)
Set CLS_VARIABLE = MOD_VARIABLE
End Property
'OUTPUT VARIABLE
Public Property Get OUTPUT_VARIABLE() As Variant
OUTPUT_VARIABLE = CLS_VARIABLE
End Property
MessageBox
'MESSAGEBOX
MsgBox Prompt:="ENTER PROMPT", BUTTONSASVBMSGBOXSTYLE:=vbOKOnly, Title:="ENTER TITLE"
'INPUT MESSAGEBOX
strVariable = InputBox(Prompt:="ENTER PROMPT", Title:="ENTER TITLE")
Error Subroutine
'ERROR SUBROUTINE
On Error Resume Next
On Error GoTo 0 'Resets error
Err.Raise 32001 'Calls error with Err.Number (> 32000)
On Error GoTo Err_Subroutine_Err
'ERROR SUBROUTINE
Err_Subroutine_Exit:
Exit Sub
Err_Subroutine_Err:
Select Case Err.Number
Case Is = Number
Resume
Resume Next
Resume Err_Subroutine_Exit
Case Else
MsgBox "SubroutineName: Error Number: = " & Err.Number & vbCr & Err.Description
Resume Err_Subroutine_Exit
End Select
Menu Bar
Public Sub Show_Menu()
'DECLARE VARIABLES
Dim MenuBar As CommandBar
Dim MenuPopup As CommandBarPopup
Dim MenuButton As CommandBarButton
'REMOVE MENU BAR
On Error Resume Next
For Each MenuBar In CommandBars
If MenuBar.name = "MENUBARNAME" Then
MenuBar.Delete
End If
Next
On Error GoTo 0
'CREATE MENU BAR
Set MenuBar = CommandBars.Add(name:="MENUBARNAME", Position:=msoBarTop, MenuBar:=True, temporary:=True)
With MenuBar
.Visible = True
Set MenuButton = .Controls.Add(Type:=msoControlButton, temporary:=True)
'ADD MENUBUTTON
With MenuButton
.Style = msoButtonCaption
.Caption = "NAME"
.OnAction = "PROCEDURENAME"
End With
'ADD MENUPOPUP
Set MenuPopup = .Controls.Add(Type:=msoControlPopup, temporary:=True)
Set MenuButton = MenuPopup.Controls.Add(Type:=msoControlButton, temporary:=True)
With MenuPopup
.Caption = "NAME"
End With
With MenuButton
.Style = msoButtonCaption
.Caption = "NAME"
.OnAction = "PROCEDURENAME"
End With
Set MenuButton = MenuPopup.Controls.Add(Type:=msoControlButton, temporary:=True)
With MenuButton
.Style = msoButtonCaption
.Caption = "NAME"
.OnAction = "PROCEDURENAME"
End With
End With
End Sub
Public Sub Close_Menu()
'REMOVE MENU BAR
On Error Resume Next
For Each MenuBar In CommandBars
If MenuBar.name = "MENUBARNAME" Then
MenuBar.Delete
End If
Next
End Sub
