Procedures identification

Here is a module “Invisible Basic”, writen by John C. Gunther, that identifies procedures in modules. From there on, they can be ported.

' Invisible Basic: A utility for the obfuscation of VBA code
' in Excel Workbooks. See the Invisible Basic User's Guide
' (InvisibleBasic.html) for a detailed description of why
' this is useful.
'
' Copyright (c) 2005, John C. Gunther.
' All rights reserved.
'
' Redistribution and use in source and binary forms, with
' or without modification, are permitted provided that the
' following conditions are met:
'
' - Redistributions of source code must retain the above
' copyright notice, this list of conditions and the following
' disclaimer.
'
' - Redistributions in binary form must reproduce the above
' copyright notice, this list of conditions and the following
' disclaimer in the documentation and/or other materials
' provided with the distribution
'
' - Neither the name of the Invisible Basic Consortium nor
' the names of its contributors may be used to endorse or
' promote products derived from this software without
' specific prior written permission.
'
' THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
' CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED
' WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
' WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
' PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
' COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
' INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
' DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
' SUBSTITURE GOODS OR SERVICES; LOSS OF USE, DATA, OR
' PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
' ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
' LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
' ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
' IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
'
' Note: the licence conditions above were copied from the
' BSD open source license template available at
' http://www.opensource.org.licenses/bsd-license.php.
'

Option Explicit

Public Const INVISIBLE_BASIC_VERSION As Double = 3#
Public Const INVISIBLE_BASIC_URL As String = "http://invisiblebasic.sourceforge.net"

' Token types: as a program's source code is scanned, it is
' separated into a stream of tokens each with one of these
' types:
Private Const TT_IDENTIFIER As Integer = 1 'variable names, keywords, etc.
Private Const TT_STRING As Integer = 2 ' string literal ("myString")
Private Const TT_NUMBER As Integer = 3 ' numerical literal (1.23)
Private Const TT_COMMENT As Integer = 4 ' VBA comment text
Private Const TT_WHITESPACE As Integer = 5 ' space or tab
Private Const TT_GUID As Integer = 6 ' global universal identifier
' ({C62A69F0-16DC-11CE-9E98-00AA00574A4F})
Private Const TT_OTHER As Integer = 7 ' everything else

' Name of the file that contains the list of visible (not obfuscated)
' Excel/VBA keywords, reserved Excel object model names, etc.
Private Const IB_VISIBLE_KEYWORDS_FILENAME = "visible_names.txt"

' establish classes of characters helpful in tokenization:

Private Const alphaChars As String = "abcdefghijklmnopqrstuvwxyz"
Private Const underscore As String = "_"
Private Const digits As String = "0123456789"
Private Const dQuote As String = """"
Private Const GUID_START As String = "{" ' "Global Universal ID"
Private Const GUID_END As String = "}" ' (occurs in UserForm headers)
' Note: by including underscore as whitespace, parsing of continued
' lines (ending in " _") is facilitated. VBA does not allow identifiers to begin with
' underscores, so this does not cause ambiguities with the lexical
' analysis of identifiers.
Private Const wsChars As String = " " & vbTab & vbNewLine & underscore
Private Const firstNumericChars As String = digits
Private Const numericChars As String = firstNumericChars & "."
Private Const firstCommentChar As String = "'"
Private Const doubleComment As String = firstCommentChar & firstCommentChar
Private Const line_continuation_chars As String = " " & underscore
Private Const firstIdentifierChars As String = alphaChars
Private Const identifierChars As String = alphaChars & underscore & digits
' e.g. in the event procedure myButton_Click, "_" delimits the
' control name from the event name:
Private Const userform_event_delimiter As String = underscore
' if this character preceeds an indentifier within visible_names.txt, it flags
' that identifier as a userform control attribute.
Private Const userform_control_attribute_flag As String = underscore
Private Const object_attribute_delimiter As String = "." 'object attribute delimiter (e.g. the "." in myLabel.Caption)

' These keywords, when encountered in source code, are
' recognized by Invisible Basic as directives that define if
' identifiers will be obfucated ("invisible") or retained as is
' ("visible")

Private Const VISIBLE_KEYWORD As String = "#visible" ' for single lines
' for delimiting visible blocks:
Private Const BEGIN_VISIBLE_KEYWORD As String = "#begin_visible"
Private Const END_VISIBLE_KEYWORD As String = "#end_visible"

' this is added to the end of the workbook file name to get
' the default new, obfuscated, workbook's filename (e.g.
' myWorkbook.xls becomes saved invisibly, if user accepts
' the initial default name, as myWorkbook_ib.xls):

Private Const IB_FILENAME_SUFFIX As String = "_ib.xls"
' invisible basic overwrites this file without confirmation, so using
' the .tmp (temporary file) file type is essential.
Private Const IB_SECRET_DECODER_SUFFIX As String = "_secretDecoder.tmp"

' depth of #begin_visible ... #end_visible nesting:
Private m_visible_depth As Long

' returned when a specified identifier isn't found:
Private Const NO_SUCH_ID As String = ""

' lists of names that will remain in original format
' (visible), and of those that will be obuscated (made invisible)
Private visible_names As New Collection
Private invisible_names As New Collection

' lists of userform attribute names; userform attributes are used to
' identify userform control names either 1) via their use in event
' procedure names (e.g. the Click attribute identifies myButton as a
' control name in the event procedure myButton_Click) or 2) via the
' direct use of the attribute in code (e.g., the Caption attribute
' identifies myLabel as a control name in the code line:
' myLabel.Caption = "myLable Caption"). Such control names are
' automatically declared as "visible names" by Invisible Basic,
' and not obfuscated.
'
' Why do we even need this, you ask. Unlike most VBA variables,
' UserForm control names are NOT defined in the source code; because I
' could not figure out how to change these (non-source defined) names
' programmatically, I instead must be sure they are NOT changed in the
' source code (or else names would get out of synch, breaking the
' UserForm). Hence the need for these special "visible attribute"
' rules to recognize such control names.
'
' Pre 2.0 versions didn't have this feature, and thus required manual
' user intervention to declare such control names visible.

Private userform_attribute_names As New Collection

' if True, each obfuscated final code line will be preceeded with
' a comment containing the original, unobfuscated, line
' it came from (for trouble-shooting).

Private m_interleave_original_code_as_comments As Boolean

Private Const IB_NameOfInvisibleBasicMenu As String = "Invisible&Basic"
' Excel menu on which the Invisible Basic menu is placed:
Private Const IB_NameOfExcelWorksheetMenubar As String = _
"Worksheet Menu Bar"

Private Const IB_TEMP_FILENAME_PREFIX = "InvBas_Temp_" ' example temp filename: InvBas_Temp_1.tmp

' these declarations are used only by the visit_url function,
' used by the help command to open the Help file. Help command
' only works on Windows platforms.

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Declare Function ShellExecute Lib "shell32" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWDEFAULT As Long = 10

' end of declarations for visit_url

' These constants (sans the "IB_" prefix) are defined in the
' Microsoft Visual Basic for Applications Extensibility Library.

' So that users do not have to add a reference to that library, we
' define copies of those constants here. Another reason for doing this
' is that there can be more than one of these libraries on a single
' machine, and if a reference to the wrong version is employed, the
' code breaks with a rather cryptic "type mismatch" error (the type of
' VBComponent used by Excel differs from the type used by the
' VBComponent in the Extensibility library if the extensibility
' library is from a newer version of Excel)

' If (as seems very unlikely) Microsoft ever changes these constants,
' these lines would have to be changed.

Private Const IB_vbext_ct_StdModule As Long = 1
Private Const IB_vbext_ct_ClassModule As Long = 2
Private Const IB_vbext_ct_MSForm As Long = 3
Private Const IB_vbext_ct_Document As Long = 100

' End of Microsoft VBA Extensibility library constants

' circular buffer of recently seen tokens (facilitates
' recognition of userform control names):
Private Const N_BUFFERED_TOKENS As Integer = 3
Private prevTokens(0 To N_BUFFERED_TOKENS - 1) As String
Private tokenIndex As Integer

' These are used for encoding name, value pairs into VB collections

Private Const NAME_ID As Integer = 1 ' represent the offsets into an array storing name, value pair
Private Const VALUE_ID As Integer = 2 ' (VB collections will not allow you to store user defined types)

Private Function PS() As String ' e.g. a "\" on Windows
PS = Application.PathSeparator
End Function

Public Property Get interleave_original_code_as_comments() As Boolean
interleave_original_code_as_comments = m_interleave_original_code_as_comments
End Property

Public Property Let interleave_original_code_as_comments(original_code_as_comments As Boolean)
m_interleave_original_code_as_comments = original_code_as_comments
End Property

' VBA within Excel 97 lacks "Debug.Assert". For compatibility with
' all major Excel versions, we therefore emulate it:
Private Sub assert(condition As Boolean)
If (Not condition) Then Stop
End Sub

' Does an old-style "Rem" comment begin at the specified position in the string?
'
' Note: Rem is a keyword, and thus cannot be used as a variable name
' in compilable VBA code--this fact simplifies the test below.
Private Function isRemComment(s As String, iStart As Integer) As Boolean
isRemComment = ("rem" = LCase(Trim(Mid(s, iStart, Len("rem ")))))
End Function

' Returns integer type of a token assumed to start at give
' position in the given string

' Note: Invisible Basic's lexical analysis is, by design, very simple
' and suitable only for this code obfuscation task. For example, the
' "E" in scientific notation numeric literals will be treated like a
' variable that is always visible (e.g. 1.2E10 is analyzed as the number
' 1.2, the always-visible name E, and the number 10). It all comes out OK in
' the end, but just be aware that the string of tokens seen by
' Invisible Basic is NOT the same as what Visual Basic sees.

Private Function token_type(s As String, _
iStart As Integer) As Integer
Dim c As String
Dim result As Integer
assert 1 <= iStart And iStart <= Len(s)
c = LCase(Mid(s, iStart, 1))
If (InStr(1, wsChars, c) <> 0) Then
result = TT_WHITESPACE
ElseIf (firstCommentChar = c Or isRemComment(s, iStart)) Then
' this branch must preceed identifier branch or "Rem" comments will look like identifiers
result = TT_COMMENT
ElseIf (InStr(1, firstIdentifierChars, c) <> 0) Then
result = TT_IDENTIFIER
ElseIf (c = dQuote) Then
result = TT_STRING
ElseIf (c = GUID_START) Then
result = TT_GUID
ElseIf (InStr(1, firstNumericChars, c) <> 0) Then
result = TT_NUMBER
Else
result = TT_OTHER
End If

token_type = result

End Function

' Returns the string position 1 character past the end of the
' token that starts at the given position in the given string.

Private Function end_of_token(s As String, _
iStart As Integer) As Integer
Dim iEnd As Integer
Dim tt As Integer
Dim matchChars As String
Dim invertMatch As Boolean
Dim matched As Boolean
Dim c As String

tt = token_type(s, iStart)

Select Case (tt)
Case TT_IDENTIFIER
matchChars = identifierChars
invertMatch = False
Case TT_STRING
matchChars = dQuote
invertMatch = True ' all chars until next double quote
Case TT_GUID
matchChars = GUID_END
invertMatch = True
Case TT_NUMBER
matchChars = numericChars
invertMatch = False
Case TT_COMMENT
matchChars = ""
invertMatch = True ' match everything until end of line
Case TT_WHITESPACE
matchChars = wsChars
invertMatch = False
Case TT_OTHER
' any character that can NOT be viewed as the first char of
' one of the above token types
matchChars = firstIdentifierChars & firstNumericChars & _
firstCommentChar & dQuote & wsChars & GUID_START
invertMatch = True
End Select

iEnd = iStart + 1

Do While (iEnd <= Len(s))
c = LCase(Mid(s, iEnd, 1))
matched = InStr(1, matchChars, c) <> 0
If (invertMatch) Then matched = Not matched

If (Not matched) Then Exit Do

iEnd = iEnd + 1

Loop

' end of string or GUID should include the closing double
' quote or end of GUID character (close curley brace), so
' increase by one to include these final characters.

' Note: improperly terminated quotes or GUIDs should be impossible
' in "compilable" VBA source code. In the event that the closing
' character is missing, iEnd will already be one past the last
' character of the input line/string, so no need to advance it.

If ((tt = TT_STRING And c = dQuote) Or _
(tt = TT_GUID And c = GUID_END)) Then
iEnd = iEnd + 1
End If

end_of_token = iEnd

End Function

 

' returns a meaningless, sequential, variable name (that is
' also reasonably short).

Private Function invisible_variable_name(var_id As Long) As String
Dim result As String
Dim i As Long
Dim L1 As Integer
Dim L2 As Integer

assert var_id > 0
result = ""
'
' this algorithm obtains a valid, short, and meaningless identifier by
' expressing the given integer variable id as a "mixed base"
' number whose "digits" are the characters valid in an identifier.

' Specifically, if you think of the variable id integer as expressed as:

' var_id = i0 + L1* (i1 + L2*i2 + L2^2*i3 + L2^3*i4 + ... )

' (by a slight generalization of the basic ideas of "base X" numbers
' you can show that any positive integer can be expressed in such a
' "mixed L1/L2 base" form)

' where L1 is the length of the valid initial identifier characters
' string; L2 is the length of the valid non-initial identifier
' character string; i0 is an integer index (0..L1-1) into the initial
' identifier char string, and i1, i2, ... are indexes (0...L2-1) into
' the non-initial identifier char string. Then the chars associated
' with these indexes determine the chars in a valid identifier (variable
' name) uniquely determined by var_id.

i = var_id
L1 = Len(firstIdentifierChars)
L2 = Len(identifierChars)
result = Mid(firstIdentifierChars, 1 + i Mod L1, 1)
i = Fix(i / L1)
Do While (i > 0)
result = result & Mid(identifierChars, 1 + i Mod L2, 1)
i = Fix(i / L2)
Loop

invisible_variable_name = result

End Function

' Associates an appropriate obfuscated name with each member
' of the invisible names collection
'
' Also excludes names from the invisible names collection
' that are also on the visible names collection.

Private Sub define_obfuscated_names()
Dim iName As Long
Dim vName As String
Dim cNew As New Collection
Dim iObfuscated_Name As Long

iObfuscated_Name = 1
For iName = 1 To invisible_names.Count
If (lookup_identifier(visible_names, CStr(invisible_names.Item(iName)(NAME_ID))) _
= NO_SUCH_ID) Then
Do ' keep looking until we get a name that is not on either
' the visible or invisible list; this loop executes once, on
' average, because collisions are unlikely Note: Assuring the
' new name isn't on the invisible list is required to avoid
' errors when renaming module, class and userform names.
vName = invisible_variable_name(iObfuscated_Name)
iObfuscated_Name = iObfuscated_Name + 1
Loop Until _
lookup_identifier(visible_names, vName) = NO_SUCH_ID And _
lookup_identifier(invisible_names, vName) = NO_SUCH_ID
add_identifier cNew, CStr(invisible_names.Item(iName)(NAME_ID)), vName
'else identifier is on visible list, so elide it from invisible list
End If
Next iName

Set invisible_names = cNew

End Sub

' returns the (possibly obfuscated, transformed) variable
' name given the original variable name

Private Function var_name(plaintextVarname As String) As String
Dim result As String
result = lookup_identifier(invisible_names, LCase(plaintextVarname))
If (result = NO_SUCH_ID) Then
' just keep the original name except converted to lowercase
result = LCase(plaintextVarname)
End If
var_name = result
End Function

' clears all of the elements in the lookup table
Private Sub reset_lookup_table(lookup_table As Collection)
Set lookup_table = New Collection
End Sub

' returns the value associated with given name, or NO_SUCH_ID if there
' is not such a (name, value) pair in the collection.
Private Function lookup_identifier(c As Collection, sName As String) As String
Dim result As String
On Error GoTo not_found
result = c.Item(LCase(sName))(VALUE_ID)
GoTo end_of_function
not_found:
result = NO_SUCH_ID
end_of_function:
lookup_identifier = result
End Function

Private Sub remove_identifier(c As Collection, sName As String)
If (lookup_identifier(c, sName) <> NO_SUCH_ID) Then
c.Remove LCase(sName)
End If
End Sub

' adds the name, value pair to the collection if the name is
' not already on the collection.

Private Sub add_identifier(c As Collection, sName As String, sValue As String)
Dim name_value_pair(NAME_ID To VALUE_ID) As String

If (NO_SUCH_ID = lookup_identifier(c, sName)) Then
name_value_pair(NAME_ID) = sName
name_value_pair(VALUE_ID) = LCase(sValue)
c.Add name_value_pair, LCase(sName)
End If

End Sub

' location of the last substring within the given string, or 0 if
' substring doesn't occur within given string.

Private Function last_substring_position(s As String, subS As String) As Integer
Dim iFound As Integer
Dim iNext As Integer

iFound = 0
iNext = InStr(1, s, subS)
Do While (iNext > 0)
iFound = iNext
iNext = InStr(iFound + 1, s, subS)
Loop

last_substring_position = iFound

End Function

' location of the event delimiter ("_") within the token, or 0 if none.

Private Function event_delimiter_position(token As String) As Integer
event_delimiter_position = last_substring_position(token, userform_event_delimiter)
End Function

' Returns the part of an event procedure token associated with the
' name of an event. For example, with an event procedure token of
' "myButton_Click", returns "Click"

' if the token isn't in the general format of an event procedure name,
' (e.g. it doesn't contain an underscore) it returns NO_SUCH_ID

Private Function event_part(token As String) As String
Dim iPosition As Integer
Dim result As String

iPosition = event_delimiter_position(token)
If (iPosition = 0) Then
result = NO_SUCH_ID
Else
result = Right(token, Len(token) - (iPosition + Len(userform_event_delimiter) - 1))
End If

event_part = result

End Function

' returns the part of an event procedure name associated with the
' name of the object (e.g. myButton_Click as token would return myButton)

Private Function object_part(token As String) As String
Dim iPosition As Integer
Dim result As String

iPosition = event_delimiter_position(token)
If (iPosition = 0) Then
result = NO_SUCH_ID
Else
result = Left(token, iPosition - 1)
End If

object_part = result

End Function

' does the token represent an event procedure name (e.g. myButton_Click) ?
Private Function is_event_procedure(token As String) As Boolean
Dim sEvent As String
Dim result As String
sEvent = event_part(token)
If (sEvent = NO_SUCH_ID) Then
result = False
ElseIf (NO_SUCH_ID = lookup_identifier(userform_attribute_names, sEvent)) Then
result = False
Else
result = True
End If
is_event_procedure = result
End Function

' does the given string begin with the specified prefix?
Private Function has_prefix(s As String, prefix As String) As Boolean
has_prefix = (Left(s, Len(prefix)) = prefix)
End Function

' does the given string end with the specified suffix?
Private Function has_suffix(s As String, suffix As String) As Boolean
has_suffix = (Right(s, Len(suffix)) = suffix)
End Function

' sets token buffer to the default, "do nothing", token sequence
Private Sub reset_token_buffer()
Dim i As Integer
For i = LBound(prevTokens) To UBound(prevTokens)
prevTokens(i) = " " ' use whitespace because leading whitespace cannot change how a program is parsed
Next i ' (the default "" isn't a valid token and can therefore cause problems)
tokenIndex = LBound(prevTokens)
End Sub

' write the token into the circular token buffer
Private Sub remember_token(token As String)
tokenIndex = (tokenIndex + 1) Mod N_BUFFERED_TOKENS
prevTokens(tokenIndex) = token
End Sub

' returns the last token stored in the token buffer
Private Function last_token() As String
last_token = prevTokens(tokenIndex)
End Function

' returns next-to-the-last token stored in the token buffer
Private Function next_to_last_token() As String
Dim result As String
If (tokenIndex = LBound(prevTokens)) Then
result = prevTokens(UBound(prevTokens)) ' wrap-around to last element
Else
result = prevTokens(tokenIndex - 1) ' no-wrap-around needed
End If
next_to_last_token = result
End Function

' does the token represent an attribute (event or property) of a
' control contained on a userform?
Private Function is_userform_attribute(token As String) As Boolean
is_userform_attribute = (NO_SUCH_ID <> lookup_identifier(userform_attribute_names, token))
End Function

' Is the token one that, when it preceeds another token (separated
' only by whitespace) indicates that that token represents an
' explicitly declared name.
'
' Examples (the variable x is explicitly declared because it is
' preceeded by Dim, Private, or Function):
'
' dim x as Double
' private x as Variant
' private function x()

Private Function preceeds_declared_name(token As String) As Boolean
Dim result As Boolean

Select Case LCase(token)

Case "friend", "enum", "declare", "static", "byref", "byval", "get", "let", "set", "dim", _
"function", "sub", "type", "const", "private", "public", "global", "paramarray", _
"optional", "property"

result = True
Case Else
result = False
End Select

preceeds_declared_name = result
End Function
' is the token one that, when it follows another token separated only
' by whitespace, implies that token is an explicitly declared name?

' Example (the name x is explicitly declared because it is followed
' by "as"):
'
' type myType
' x as Integer
' end type

Private Function follows_declared_name(token As String) As Boolean
Dim result As Boolean
Select Case LCase(token)
Case "as", "lib"
result = True
Case Else
result = False
End Select
follows_declared_name = result
End Function

' adds ids contained in the string (representing a single, though possibly
' continued, line of input source text) to appropriate lookup tables
' used to determine which variable names remain unchanged, and which
' are obfuscated (replaced with variable names meaningless to humans).

Private Sub register_ids(s As String)
Dim iStart As Integer
Dim iEnd As Integer
Dim visible As Boolean
Dim obfuscated_id As Long
Dim token As String

reset_token_buffer ' cross-source-statement token sequences are not of interest

If InStr(1, LCase(s), BEGIN_VISIBLE_KEYWORD) <> 0 Then
m_visible_depth = m_visible_depth + 1
End If
If InStr(1, LCase(s), END_VISIBLE_KEYWORD) <> 0 Then
m_visible_depth = m_visible_depth - 1
End If

If InStr(1, LCase(s), VISIBLE_KEYWORD) > 0 Then
' single line #visible keyword makes ids on this line visible, no
' matter what our visible depth is
visible = True
Else
' no line specific keyword, so based on if we are within
' a #begin_visible ... #end_visible bracketed region
visible = m_visible_depth > 0
End If

iStart = 1
Do While (iStart <= Len(s))
iEnd = end_of_token(s, iStart)
token = LCase(Mid(s, iStart, iEnd - iStart))

If (token_type(token, 1) = TT_IDENTIFIER) Then
If (last_token() = userform_control_attribute_flag) Then
' token is flagged as representing a userform-related event,
' such as Click (or control property such as Caption)
'
' Example token sequence: "_" followed by "Click" will
' register "Click" as a userform attribute. Note that "_Click"
' isn't processed as a single token because "_" isn't a valid
' first character of a variable name in VBA.
add_identifier userform_attribute_names, token, token
add_identifier visible_names, token, token
ElseIf (is_event_procedure(token)) Then
' example token: myButton_Click will make itself and myButton visible if _Click is listed in visible_names.txt
add_identifier visible_names, token, token
add_identifier visible_names, object_part(token), object_part(token)
ElseIf (is_userform_attribute(token) And _
last_token() = object_attribute_delimiter And token_type(next_to_last_token(), 1) = TT_IDENTIFIER) Then
' example: myLabel.Caption will make myLabel a visible name if
' "_Caption" is listed in visible_names.txt (the leading _
' flags Caption as a userform control attribute (event or property))
add_identifier visible_names, next_to_last_token(), next_to_last_token()
ElseIf (visible) Then
add_identifier visible_names, token, token
Else
' note: if an identifier gets added to both visible and
' invisible lists, it will considered visible (and get removed
' from the invisible list in a separate step later on).
If (token_type(last_token(), 1) = TT_WHITESPACE) Then
If (preceeds_declared_name(next_to_last_token())) Then
add_identifier invisible_names, token, token
End If

If (follows_declared_name(token) And _
token_type(next_to_last_token(), 1) = TT_IDENTIFIER) Then
add_identifier invisible_names, next_to_last_token(), next_to_last_token()
End If
End If
End If
' else not an identifier, so it can never be added to lookup tables
' used to determine token visibility.
End If

remember_token token ' stores last few token in a circular buffer for easier parsing

iStart = iEnd
Loop

End Sub

' the length of a string, excluding and leading/trailing double quotes
Private Function length_sans_quotes(s As String) As Integer
Dim result As Integer
result = Len(s)
If (has_prefix(s, dQuote)) Then result = result - Len(dQuote)
If (has_suffix(s, dQuote)) Then result = result - Len(dQuote)
length_sans_quotes = result
End Function

' length of the given prefix within a specified string, or 0 if that
' prefix is not at the beginning of the specified string
Private Function length_of_prefix(s As String, prefix As String) As Integer
Dim result As Integer
If (has_prefix(s, prefix)) Then
result = Len(prefix)
Else
result = 0
End If
length_of_prefix = result
End Function
' strips leading, trailing, double quotes from a given string
' (if no such quotes present, returns original string)
Private Function NQ(s As String) As String
NQ = Mid(s, 1 + length_of_prefix(s, dQuote), length_sans_quotes(s))
End Function

' adds double quotes around the given string
Private Function Q(s As String) As String
Q = dQuote & s & dQuote
End Function

' returns an obfuscated, functionally equivalent, source code line
' for the given source code line
Private Function obfuscated_line(s As String) As String
Dim result As String
Dim iStart As Integer
Dim iEnd As Integer
Dim token As String

result = ""

iStart = 1

Do While (iStart <= Len(s)) iEnd = end_of_token(s, iStart) token = Mid(s, iStart, iEnd - iStart) Select Case (token_type(token, 1)) Case TT_IDENTIFIER result = result & var_name(token) Case TT_WHITESPACE If (InStr(token, line_continuation_chars & vbNewLine) > 0) Then
' line continuation characters and newlines are analyzed as
' part of whitespace tokens, but they need to be preserved
' because VBA has line length constraints that could break code
' if long continued lines were collapsed into a single line.
result = result & line_continuation_chars & vbNewLine
Else
result = result & " "
End If
Case TT_NUMBER
result = result & token
Case TT_STRING
result = result & token
Case TT_COMMENT
If (has_prefix(token, doubleComment)) Then
' double comments are retained (for copywrite notices, etc.)
result = result & Right(token, Len(token) - Len(firstCommentChar))
' else just ignore/elide the comment
End If
Case TT_GUID
result = result & token
Case TT_OTHER
result = result & token
Case Else
assert False ' should have been type "other"
End Select
iStart = iEnd
Loop

' trim to drop any leading whitespace (makes lines all flush left)
obfuscated_line = Trim(result)

End Function

' reads each line from the specified sourcecode file, and
' registers any identifiers contained in the file on the
' appropriate (visible or invisible) lookup table.

Private Sub register_identifiers(fName As String)
Dim fid As Integer
Dim sLine As String
Dim errNo As Long
On Error GoTo error_exit

' open file for reading
fid = freefile()
Open fName For Input As #fid

' read each (possibly continued) line, registering its ids
Do While Not EOF(fid)
sLine = get_continued_line(fid)
register_ids sLine
Loop

Close fid
GoTo end_of_sub
error_exit:
errNo = Err.Number
On Error Resume Next
Close fid
Err.Raise errNo

end_of_sub:
End Sub

' is the line one that is continued on the next line (ends in
' the VBA line continuation character sequence, " _")
Private Function is_continued_line(sLine As String) As Boolean
is_continued_line = has_suffix(sLine, line_continuation_chars)
End Function

 

' adds another line to an existing series of "vbNewLine
' separated" lines, returning the so-extended series of lines.

Private Function add_line(sOld As String, sNew As String) As String
Dim result As String
If (sOld = "") Then
result = sNew
Else
result = sOld & vbNewLine & sNew
End If
add_line = result
End Function

' returns a (possibly continued) source code line from the given
' input file.
Private Function get_continued_line(f_in As Integer) As String
Dim result As String
Dim sTmp As String
result = ""
Do ' read & concatenate continued lines
Line Input #f_in, sTmp
result = add_line(result, sTmp)
Loop Until EOF(f_in) Or Not is_continued_line(sTmp)

get_continued_line = result

End Function

 

' obfuscates the given sourcecode file by removing comments,
' replacing meaningful names with meaningless names, etc.

' A side benefit: it tends to reduce the size of the source code
' files, due to comment elimination and the fact that
' obfuscated names are usually substantially shorter than
' the original names.

Private Sub obfuscate_sourcecode_file( _
f_plain As String, f_obfuscated As String)
Dim f_in As Integer
Dim f_out As Integer
Dim sLine As String
Dim sObfuscated As String
Dim errNo As Long
On Error GoTo error_exit

f_in = freefile()
Open f_plain For Input As #f_in
f_out = freefile()
Open f_obfuscated For Output As #f_out

' obfuscate, and then write, each original input source code
' file line into the obfuscated source code output file
Do While Not EOF(f_in)
sLine = get_continued_line(f_in)
sObfuscated = obfuscated_line(sLine)
If (m_interleave_original_code_as_comments) Then
Print #f_out, firstCommentChar & sLine
Print #f_out, sObfuscated ' empty obfuscated lines retained--helpful when debugging.
ElseIf (sObfuscated <> "") Then
Print #f_out, sObfuscated
' else elide lines that are empty after obfuscation
End If

Loop

Close f_in
Close f_out
GoTo end_of_sub
error_exit:
errNo = Err.Number
On Error Resume Next
Close f_in
On Error Resume Next
Close f_out
Err.Raise errNo

end_of_sub:

End Sub

' returns a temporary file name given a file number
Private Function temp_file_name(wb As Workbook, _
iFile As Integer, Optional extension = ".tmp") As String
temp_file_name = wb.Path & PS() & IB_TEMP_FILENAME_PREFIX & _
CStr(iFile) & extension
End Function

' returns a random module name suitable for use as a VBA code module
Private Function random_module_name() As String
' highly unlike this name will conflict with any existing names
random_module_name = "qzx" & _
Format(10 ^ 6 * Rnd(), "000000") & Format(10 ^ 6 * Rnd(), "000000")
End Function

' writes source code in a given VBComponent into a specified file
' (overwrites any existing file contents)

Private Sub write_component_code(vbc As Object, f As String)
Dim f_out As Integer
Dim iLine As Long
Dim errNo As Long
On Error GoTo error_exit

f_out = freefile()
Open f For Output As #f_out

For iLine = 1 To vbc.CodeModule.CountOfLines
Print #f_out, vbc.CodeModule.Lines(startLine:=iLine, Count:=1)
Next iLine

Close f_out
GoTo end_of_sub
error_exit:
errNo = Err.Number
On Error Resume Next
Close f_out
Err.Raise errNo

end_of_sub:

End Sub

' reads source code in a given file into the specified component
' (overwrites any existing code in the component)

Private Sub read_component_code(vbc As Object, f As String)
Dim f_in As Integer
Dim sLine As String
Dim iLine As Long
Dim errNo As Long
On Error GoTo error_exit

vbc.CodeModule.DeleteLines startLine:=1, Count:=vbc.CodeModule.CountOfLines

' vbc.CodeModule.AddFromFile has unpleasant side-effects related to module name
' changes, so we just add the lines one at a time instead:
f_in = freefile()
Open f For Input As #f_in
iLine = 1
Do While Not EOF(f_in) ' read each source code line and insert into component
Line Input #f_in, sLine
vbc.CodeModule.InsertLines iLine, sLine
iLine = iLine + 1
Loop

Close f_in
GoTo end_of_sub
error_exit:
errNo = Err.Number
On Error Resume Next
Close f_in
Err.Raise errNo

end_of_sub:

End Sub

' Writes out a "cheat sheet" that gives you the original name of each
' obfuscated name in an obfuscated workbook.
'
' The cheat sheet is helpful in debugging obfuscated programs (allows
' you to translate the names that appear on a single obfuscated line,
' etc.).
'
' Assumes that invisible_names list is fully populated.
'

Private Sub write_invisible_names(wb As Workbook, fName As String)
Dim iPair As Long ' index of name, value pair on invisible names list
Dim f_out As Integer
Dim errNo As Long
On Error GoTo error_exit

f_out = freefile()
Open fName For Output As #f_out

Print #f_out, "Hidden" & vbTab & "Original"
For iPair = 1 To invisible_names.Count ' for each name, value pair on invisible names list
Print #f_out, invisible_names(iPair)(VALUE_ID) & vbTab & invisible_names(iPair)(NAME_ID)
Next iPair

Close f_out
GoTo end_of_sub
error_exit:
errNo = Err.Number
On Error Resume Next
Close f_out
Err.Raise errNo

end_of_sub:

End Sub

' obfuscates all VBA source code modules, classes and UserForms

Private Sub obfuscate_workbook(wb As Workbook)
Dim vbc As Object
Dim iFile As Integer
Dim tmpFile As String
Dim old_display_status_bar As Boolean
Dim newName As String

old_display_status_bar = Application.DisplayStatusBar
Application.DisplayStatusBar = True

Application.StatusBar = "Saving Invisibly: initializing..."
' start with empty variable name identifier tables
reset_lookup_table visible_names
reset_lookup_table invisible_names
reset_lookup_table userform_attribute_names
reset_token_buffer
' the E "identifier" appears within numeric literals
' expressed in scientific notation, and thus must never be
' obfuscated (this "non-obfuscation of e" is needed because
' our lexical analysis of numbers is otherwise too simple to
' get numeric literals expressed in scientific notation right).
register_ids "e '#visible"

' register all built-in visible identifiers stored in
' a special text file shipped with the application
' (Excel/VBA keywords and user defined universal keywords)

assert Dir(ThisWorkbook.Path & PS() & IB_VISIBLE_KEYWORDS_FILENAME) <> ""
m_visible_depth = 1
register_identifiers ThisWorkbook.Path & PS() & IB_VISIBLE_KEYWORDS_FILENAME
m_visible_depth = 0

' first pass: store each code module in a temp file,
' register that file's visible identifiers, and then delete
' the code component.

For iFile = 1 To wb.VBProject.VBComponents.Count
Set vbc = wb.VBProject.VBComponents(iFile)
Select Case vbc.Type
Case IB_vbext_ct_StdModule, IB_vbext_ct_ClassModule, IB_vbext_ct_MSForm
' the name of a module, class, or userform is obfuscated
' (normal case). Register the name as "invisible"
m_visible_depth = 0
register_ids "Dim " & vbc.Name ' Dim makes it look like name is "user declared"
Case IB_vbext_ct_Document
' document (e.g. Worksheet) code names remain visible because
' there isn't an easy way to RELIABLY change them
' programmatically (surprisingly, setting vbc.Name doesn't do it)
register_ids vbc.Name & " '#visible"
Case Else
' if Microsoft adds a new type, play it safe by keeping
' names unchanged ("visible").
register_ids vbc.Name & " '#visible"
End Select
Application.StatusBar = "Saving Invisibly: Pass 1 of 2, VBComponent " & CStr(iFile) & " of " & CStr(wb.VBProject.VBComponents.Count)
write_component_code vbc, temp_file_name(wb, iFile)
m_visible_depth = 0 ' invisible unless otherwise noted
reset_token_buffer
register_identifiers temp_file_name(wb, iFile)
Next iFile

define_obfuscated_names 'choose obscure ids for invisible names

' second pass obfuscates by replacing registered, non-visible
' variable ids with meaningless ids, stripping comments, etc, and
' then reading the so-obfuscated code back into each component.

tmpFile = temp_file_name(wb, wb.VBProject.VBComponents.Count + 1)
For iFile = 1 To wb.VBProject.VBComponents.Count
Set vbc = wb.VBProject.VBComponents(iFile)

newName = obfuscated_line(vbc.Name)
' this "if" (to prevent changing name when name isn't obfuscated)
' was added because I don't trust that name changes in such cases, even to the same
' name, are reliable.
If (LCase(newName) <> LCase(vbc.Name)) Then vbc.Name = newName

Application.StatusBar = "Saving Invisibly: Pass 2 of 2, VBComponent " & CStr(iFile) & " of " & CStr(wb.VBProject.VBComponents.Count)
reset_token_buffer
obfuscate_sourcecode_file temp_file_name(wb, iFile), tmpFile
read_component_code vbc, tmpFile
Kill tmpFile
Kill temp_file_name(wb, iFile)
Next iFile

Application.StatusBar = _
"Writing ""secret decoder"" file: " & ib_suffixed_filename(wb, IB_SECRET_DECODER_SUFFIX) & "..."
write_invisible_names wb, ib_suffixed_filename(wb, IB_SECRET_DECODER_SUFFIX)

Application.StatusBar = False ' restore status bar status quo
Application.DisplayStatusBar = old_display_status_bar

End Sub

' If this function returns True, the two strings are guaranteed
' to represent different physical files (regardless of what
' default paths might be added to any file name strings that do not
' have explicitly specified full pathnames)

Private Function are_different_files(f1_in As String, f2_in As String) As Boolean
Dim f1 As String
Dim f2 As String
Dim result As Boolean

f1 = Trim(LCase(f1_in))
f2 = Trim(LCase(f2_in))

' making each filename start with path separator simplifies the tests:
If (Not has_prefix(f1, PS())) Then f1 = PS() & f1
If (Not has_prefix(f2, PS())) Then f2 = PS() & f2

' if the last half of either filename string equals the other,
' the filename COULD represent the same physical file
If (has_suffix(f1, f2) Or has_suffix(f2, f1)) Then
result = False
Else
' filenames definitely represent different files
result = True
End If
are_different_files = result
End Function

' obfuscates the given workbook, saving it into the specified file

Public Sub obfuscate_workbook_as(wb As Workbook, fileName As String)

assert are_different_files(wb.fullName, fileName)

' saving under a new name breaks connection with original file,
' assuring that original unobfuscated workbook isn't damaged.
' (even if we crash and user then accidentally saves the
' so-damaged workbook, originally named file is still safe)
wb.SaveAs fileName
obfuscate_workbook wb
Application.DisplayAlerts = False
wb.SaveAs fileName ' save again under the new name
Application.DisplayAlerts = True

End Sub

' default filename in which to store "invisible" version
Private Function ib_suffixed_filename(wb As Workbook, suffix As String) As String
Dim dot_position As Integer
Dim result As String
dot_position = last_substring_position(wb.Name, ".")
If (dot_position = 0) Then
result = wb.Path & PS() & wb.Name & suffix
Else
result = wb.Path & PS() & Left(wb.Name, dot_position - 1) & suffix
End If
ib_suffixed_filename = result
End Function

' Save the active workbook invisibly in a user-selected workbook
Private Sub ib_save_invisibly_as()
Dim fileName As String
Dim wb As Workbook
On Error GoTo error_exit

Set wb = ActiveWorkbook
If (Not wb.saved) Then
MsgBox "Workbook """ & ActiveWorkbook.Name & """ has unsaved changes. " & _
"To help prevent accidental source code losses, workbooks " & _
"with unsaved changes cannot be saved invisibly. " & vbNewLine & vbNewLine & _
"Save your original workbook, then try again. ", _
vbCritical, "Workbooks with unsaved changes cannot be saved invisibly."
GoTo end_of_sub
End If
' present a "save as" type filename dialog
fileName = Application.GetSaveAsFilename( _
InitialFilename:=ib_suffixed_filename(wb, IB_FILENAME_SUFFIX), _
FileFilter:="Microsoft Excel Workbook (*.xls),*.xls,All Files (*.*),*.*", _
Title:="Select file into which workbook will be saved invisibly")

' Because there is too much potential for total code loss, we do not
' allow user to overwrite the original workbook with the obfuscated
' workbook:

If (Not are_different_files(wb.fullName, fileName)) Then
MsgBox "The selected filename (" & fileName & _
") must be clearly different from the current workbook's filename (" & wb.fullName & _
"). Try again, next time choosing a different name.", _
vbCritical, "Save Invisibly As Filename Must Differ from Original Filename"
ElseIf (fileName <> "False") Then
obfuscate_workbook_as wb, fileName
End If

GoTo end_of_sub
error_exit:
Application.DisplayAlerts = True
Application.StatusBar = False ' resume default status bar behavior
MsgBox "Error #" & CStr(Err.Number) & " during ""Save Invisibly As"": " & Err.Description, _
vbCritical, "Invisible Basic Save Invisibly As Error"
end_of_sub:
End Sub

' Top level "Save Invisibly As..." command

Public Sub invisible_basic_save_invisibly_as() '#visible
m_interleave_original_code_as_comments = False
ib_save_invisibly_as
End Sub

' Top level "Debugging Save Invisibly As..." command

Public Sub invisible_basic_debugging_save_invisibly_as() '#visible
m_interleave_original_code_as_comments = True
ib_save_invisibly_as
End Sub

Private Sub visit_url(url As String)
ShellExecute GetDesktopWindow(), "Open", url, 0, 0, SW_SHOWMAXIMIZED
End Sub

' Just shows the HTML file that contains the InvisibleBasic help file
Public Sub invisible_basic_show_help() '#visible
visit_url ThisWorkbook.Path & PS() & "InvisibleBasic.html"
End Sub

Public Sub invisible_basic_web_site() '#visible
visit_url INVISIBLE_BASIC_URL
End Sub

Public Sub invisible_basic_about() '#visible

MsgBox "Invisible Basic Version " & CStr(INVISIBLE_BASIC_VERSION) & vbNewLine & _
"A Source Code Obfuscator for Excel/VBA" & vbNewLine & _
"Share you spreadsheets. Not your source code." & vbNewLine & _
vbNewLine & _
"Copyright 2006, John C. Gunther. All Rights Reserved." & vbNewLine & _
"Distributed under the terms of the BSD open source license." & vbNewLine & _
vbNewLine & _
"Web Site: " & INVISIBLE_BASIC_URL & vbNewLine _
, vbOKOnly, "About Invisible Basic"

End Sub

' adds or updates the Invisible Basic menu within Excel
Public Sub invisible_basic_add_menu()
Dim cbp As CommandBarPopup ' new invisible basic menu bar
Dim cbb As CommandBarButton ' new menu item added to this bar

Call invisible_basic_remove_menu ' to prevent adding menu twice

Set cbp = Application.CommandBars(IB_NameOfExcelWorksheetMenubar).Controls.Add( _
Type:=msoControlPopup)

cbp.caption = IB_NameOfInvisibleBasicMenu
cbp.tooltiptext = _
"Source code obfuscation utility for Excel/VBA applications."

Set cbb = cbp.Controls.Add(Type:=msoControlButton)
cbb.caption = "&Save Invisibly As..."
cbb.DescriptionText = "Saves copy of workbook whose VBA code is replaced with equivalant, but hard-to-read, code."
cbb.onAction = "invisible_basic_save_invisibly_as"

Set cbb = cbp.Controls.Add(Type:=msoControlButton)
cbb.caption = "&Debugging Save Invisibly As..."
cbb.DescriptionText = "Same as Save Invisibly As except interleaves original source code as comments (for debugging)."
cbb.onAction = "invisible_basic_debugging_save_invisibly_as"

Set cbb = cbp.Controls.Add(Type:=msoControlButton)
cbb.caption = "&Help..."
cbb.DescriptionText = "Invisible Basic Help"
cbb.onAction = "invisible_basic_show_help"

Set cbb = cbp.Controls.Add(Type:=msoControlButton)
cbb.caption = "Invisible Basic &Web Site"
cbb.DescriptionText = "Invisible Basic Web Site"
cbb.onAction = "invisible_basic_web_site"

Set cbb = cbp.Controls.Add(Type:=msoControlButton)
cbb.caption = "&About Invisible Basic..."
cbb.DescriptionText = "About Invisible Basic"
cbb.onAction = "invisible_basic_about"

End Sub

' removes the Invisible Basic menu from Excel
Public Sub invisible_basic_remove_menu()
On Error Resume Next
Application.CommandBars(IB_NameOfExcelWorksheetMenubar).Controls( _
IB_NameOfInvisibleBasicMenu).Delete
End Sub

' Simple test of Invisible Basic. Test requires that the test
' workbook, IB_Test.xls, be in the same folder as the Add-in is.
'
' The test makes the test workbook invisible, then runs a test
' routine within the (then obfuscated) test workbook.
'
' You may see two "OK to overwrite" prompts (answer Yes)
' and you should see "Hello Invisible Basic" (four times)
' if the test passes. If you don't see "Hello Visible Basic",
' four times, the test has failed.
'
Public Sub ib_test()
Dim wb As Workbook
Dim fTest As String
Dim fObf As String
Dim iPass As Integer

assert event_part("myButton_Click") = "Click"
assert object_part("myButton_Click") = "myButton"
assert event_part("myButtonClick") = ""
assert event_part("myButton_20_Click") = "Click"
assert object_part("myButton_20_Click") = "myButton_20"

For iPass = 1 To 2
If (iPass = 1) Then
invisiblebasic.interleave_original_code_as_comments = False
Else
invisiblebasic.interleave_original_code_as_comments = True
End If

fTest = ThisWorkbook.Path & PS() & "IB_Test.xls"
fObf = ThisWorkbook.Path & PS() & "IB_Test_Obf.xls"
' Open the test workbook
Workbooks.Open fTest
Set wb = Workbooks(Workbooks.Count)

' Save it invisibly as a new workbook
obfuscate_workbook_as wb, fObf

' the test module exercies code in the obfuscated modules in IB_Test
' and compares results with expected results.
Evaluate "IBTest.testModule.ibt_test()"
wb.close SaveChanges:=False
Next iPass
End Sub

 

VBA reserved words

Keywords

Keywords are special words that are reserved, because they are used by the compiler to determine the structure of your code, so they cannot be used for variables, or subroutine or user defined function (UDF) names. Also, reserved words are displayed in [Blue] color by default in the Code editor, but not VBA globals, which stay black color.

This table was composed from the VBE object explorer, and broaden with more-info links of two sources, bettersolutions and  excelfunctions blogs. I reordered and grouped the items by their function, so it has a comprehensible structure.

If you want the table I’d created to automate the generation of the needed VBA code, you can grab from this pCloud file.

Word Description
Option Used to define module level settings.
Option_Base Used when changing the default lower bound of an array,
Option Base 1.
Option_Compare_Binary (Advanced) Used to change the string comparison settings,
Option Compare Binary.
Option_Compare_Database (Advanced) Used to change the string comparison settings,
Option Compare Database.
Option_Explicit Used to force variables to be declared before they can be
used, Option Explicit.
Option_Text Used to change the string comparison settings, Option
Compare Text.
Global Can be used to declare a
Public variable that is visible from all the code modules.
Private Used to declare a subroutine that is only visible in that
code module.
Private_Const Used to define symbolic constants.
Private_Enum
Private_Function
Private_Function_Friend
Private_Function_Friend_Static
Private_Function_Static
Private_Property
Private_Property_Get (Advanced) Used with the Property keyword when creating
objects.
Private_Property_Let (Advanced) Used with the Property keyword when creating
objects.
Private_Property_Set (Advanced) Used with the Property keyword when creating
objects.
Private_Sub
Private_Sub_Friend
Private_Sub_Friend_Static
Private_Sub_Static
Private_Type
Private_Declare_Function Used to declare a block of code that can return a value.
Private_Declare_Sub Used to declare a block of code that does not return a
value.
Private_WithEvents (Advanced) Used in class modules to define a variable
that can receive events.
Public Used to declare a subroutine that is visible from all the
code modules.
Public_Const Used to define symbolic constants.
Public_Enum
Public_Function
Public_Function_Friend
Public_Function_Friend_Static
Public_Function_Static
Public_Property
Public_Property_Get (Advanced) Used with the Property keyword when creating
objects.
Public_Property_Let (Advanced) Used with the Property keyword when creating
objects.
Public_Property_Set (Advanced) Used with the Property keyword when creating
objects.
Public_Sub
Public_Sub_Friend
Public_Sub_Friend_Static
Public_Sub_Static
Public_Type
Public_Declare_Function Used to declare a block of code that can return a value.
Public_Declare_Sub Used to declare a block of code that does not return a
value.
Public_WithEvents (Advanced) Used in class modules to define a variable
that can receive events.
Friend (Advanced) Used in class modules to prevent subroutines
from being accessed from external projects.
Friend_Function
Friend_Sub
Friend_Property
Friend_Property_Get (Advanced) Used with the Property keyword when creating
objects.
Friend_Property_Let (Advanced) Used with the Property keyword when creating
objects.
Friend_Property_Set (Advanced) Used with the Property keyword when creating
objects.
Const Used to define symbolic constants.
#Const (Advanced) Used with conditional compilation arguments.
Enum Used to define a user defined enumeration.
Property (Advanced) Used with the Class keyword when creating
objects.
Property_Get (Advanced) Used with the Property keyword when creating
objects.
Property_Let (Advanced) Used with the Property keyword when creating
objects.
Property_Set (Advanced) Used with the Property keyword when creating
objects.
Function Used to declare a block of code that can return a value.
Sub Used to declare a block of code that does not return a
value.
Type (Advanced) Used to define a user defined data structure.
WithEvents (Advanced) Used in class modules to define a variable
that can receive events.
With (Advanced) Used to perform multiple operations on a
single object.
As Used when defining the data type of a variable or
argument.
Byte (Data Type) Used to hold any positive number between 0
and 255.
Boolean (Data Type) Used to hold either the value True or False.
Integer Used to hold any whole number between -32,768 and 32,767.
Long (Data Type) Used to hold any whole number between
-2,147,483,648 and 2,147,486,647.
LongLong (Advanced, Data Type) Used to hold large whole numbers on
a 64 bit system.
Single (Data Type) Used to hold single precision floating point
numbers.
Double (Data Type) Used to hold double precision floating point
numbers.
Currency (Data Type) Used to hold numbers when you do not want any
rounding errors.
String (Data Type) Used to hold string variables that are fixed
length or variable length.
Object (Data Type) Used to contain a reference (or address) to
an actual object.
Variant (Data Type) Used to hold any type of data except
fixed-length strings and user defined types.
ByRef Used to pass variables in and out of subroutines and
functions.
ByVal Used to pass variables into subroutines and functions.
Optional Used to indicate that a variable passed to a subroutine
or function is optional.
Optional_ByRef Used to pass variables in and out of subroutines and
functions.
Optional_ByVal Used to pass variables into subroutines and functions.
ParamArray (Advanced) Used to allow a dynamic number of arguments to
be passed to a subroutine or function.
Declare (Advanced) Used when calling
windows API functionality.
Declare_Function Used to declare a block of code that can return a value.
Declare_Sub Used to declare a block of code that does not return a
value.
Alias (Advanced) Used when declaring an external procedure in a
DLL that has the same name and something else.
Lib (Advanced) Used when calling windows API functionality.
Call Used to allow arguments to be passed in parentheses when
execution moves inside a subroutine or function.
Dim Used when declaring one or more variables.
Static (Advanced, Variables) Used to indicate that a variable
will be preserved between calls.
Static_Function Used to declare a block of code that can return a value.
Static_Sub Used to declare a block of code that does not return a
value.
ReDim (Advanced, Function) Used to initialise or resize an
array.
ReDim_Preserve (Advanced) Used to preserve the items in an array when it
is being resized.
Erase (Advanced) Used to reinitialize the elements in an array.
Me (Advanced) Used as an implicitly declared variable inside
a class module or userform.
End Used to terminate a subroutine, function or property.
End_If
End_Select
End_Sub
End_Function
End_With
End_Property
End_Type
End_Enum
#If (Advanced) Used with conditional compilation arguments.
#Else (Advanced) Used with conditional compilation arguments.
#ElseIf (Advanced) Used with conditional compilation arguments.
#End (Advanced) Used with conditional compilation arguments.
If Used with the Then keyword to allow conditional
branching.
Else Used with the If keyword when using conditional
branching.
ElseIf Used with the If keyword when using conditional
branching.
Then Used with the If keyword in conjunction with conditional
branching.
On Used with the Error keyword when using error handling.
On_Error (Statement) Used to generate an error message.
GoTo
Event (Statement) Used to declare a user defined event.
Resume (Advanced) Used with the On Error keywords when using
error handling.
Resume_Next
RaiseEvent (Advanced) Used to trigger a class module user defined
event.
Return (Advanced) Used with the GoSub keyword to return
execution back to the original line.
Exit Used to exit a subroutine or function early before it
reaches the end.
Exit_Do
Exit_For
Exit_Function
Exit_Property
Exit_Sub
Do Used with the Until or Loop keywords when repeating one
or more statements.
Do_Until Used with the Do keyword when repeating one or more
statements.
Do_While Used with the Do keyword when repeating one or more
statements.
Loop Used with the Do keyword when repeating one or more
statements.
Loop_Until Used with the Do keyword when repeating one or more
statements.
Loop_While Used with the Do keyword when repeating one or more
statements.
While Used with the Do keyword when
repeating one or more statements.
Wend Used with the While keyword when repeating one or more
statements.
For Used with the Next keyword when
repeating one or more statement.
For_Each Used with the For keyword to access the individual
elements in a collection.
Step Used with the For keyword to provide additional
increments and decrements.
Next Used with the For keyword when repeating one or more
statements.
DoEvents
Select Used with the Case keyword in conjunction with
conditional branching.
Select_Case Used with the Select keyword when using conditional
branching.
Case Used with the Select keyword when using conditional
branching.
Stop (Advanced) Used to allow you to save a breakpoint in your
file.
False Used to represent the value 0.
True Used to represent the value -1.
Nothing Used as the default value when an object has not been
initialised.
Empty Used with a Variant data type when a value has not been
assigned.
Null (Advanced, Variant) Used to explicitly indicate an
invalid value or error.
LBound (Advanced, Function) Used to return the lower limit of an
array dimension.
UBound (Advanced, Function) Used to return the upper limit of an
array dimension.
Array Creates an array, containing a supplied set of values.
Filter Returns a subset of a supplied string array, based on
supplied criteria.
Join Joins a number of substrings into a single string.
Split Splits a Text String into a Number of Substrings.
To Used with the For keyword when repeating one or more
statements.
Implements (Advanced) Used with the Class keyword when creating
objects.
Is Compares two object reference variables.
Like Used to compare two strings and provide pattern matching.
LSet (Advanced, Statement) Used to left align a string within
a string variable.
RSet (Advanced, Statement) Used to right align a string within
a string variable.
Mod (Operator) Used to divide two numbers and return the
remainder.
New (Advanced) Used to create a new instance of an object.
And (Operator) Used as the logical ‘AND’ operator.
Or (Operator) Used an the logical ‘OR’ operator.
Not (Operator) Used as the logical ‘NOT’ operator.
TypeOf (Operator) Used to return the data type of an object.
DefBool (Advanced) Used to define certain variables to have a
Boolean data type.
DefByte (Advanced) Used to define certain variables to have a
Byte data type.
DefDate (Advanced) Used to define certain variables to have a
Date data type.
DefDec (Advanced) Used to define certain variables to have a
Variant/Decimal data type.
DefDouble (Advanced) Used to define certain variables to have a
Double data type.
DefInt (Advanced) Used to define certain variables to have a
Integer data type.
DefLng (Advanced) Used to define certain variables to have a
Long data type.
DefLngLng (Added in Office 2010) Used to define certain variables
to have a LongLong data type.
DefLngPtr (Added in Office 2010) Used to define certain variables
to have a LongPtr data type.
DefObj (Advanced) Used to define certain variables to have a
Object data type.
DefSng (Advanced) Used to define certain variables to have a
Single data type.
DefStr (Advanced) Used to define certain variables to have a
String data type.
CBool (Data Type Conversion) Used to
convert an expression to a Boolean.
CByte (Data Type Conversion) Used to convert an expression to a
Byte.
CCur (Data Type Conversion) Used to convert an expression to a
Currency.
CDec (Data Type Conversion) Used to convert an expression to a
Decimal.
CDate (Data Type Conversion) Used to convert an expression to a
Date.
CDbl (Data Type Conversion) Used to convert an expression to a
Double.
CInt (Data Type Conversion) Used to convert an expression to
an Integer.
CLng (Data Type Conversion) Used to convert an expression to a
Long.
CLngLng (Data Type Conversion) Used to convert an expression to a
LongLong.
CLngPtr (Data Type Conversion) Used to convert an expression to a
LongPtr.
CSng (Data Type Conversion) Used to convert an expression to a
Single.
CStr (Data Type Conversion) Used to convert an expression to a
String.
CVar (Data Type Conversion) Used to convert an expression to a
Variant.
Format Applies a format to an expression
and returns the result as a string.
Format$
InStr Returns the position of a substring within a string.
InStrRev Returns the position of a substring within a string,
searching from right to left.
InStrB
Left Returns a substring from the start of a supplied string.
Left$
LeftB
LeftB$
Len Returns the length of a supplied string.
LenB
LCase Converts a
supplied string to lower case text.
Lcase$
LTrim Removes leading spaces from a supplied string.
Ltrim$
Mid Returns a substring from the middle of a supplied string.
Mid$
MidB
MidB$
Replace Replaces a substring within a supplied text string.
Right Returns a substring from the end of a supplied string.
Right$
RightB
RightB$
RTrim Removes trailing spaces from a supplied string.
Rtrim$
Space Creates a string consisting of a specified number of
spaces.
Space$
StrComp Compares two strings and returns an integer representing
the result of the comparison.
StrConv Converts a string into a specified format.
String Creates a string consisting of a number of repeated
characters.
String$
StrReverse Reverses a supplied string.
Trim Removes leading and trailing spaces from a supplied
string.
Trim$
UCase Converts a supplied string to upper case text.
Ucase$
Asc Returns an integer representing the code for a supplied
character.
AscB
AscW
Chr Returns the character corresponding to a supplied
character code.
Chr$
ChrB
ChrB$
ChrW
ChrW$
IsArray Tests if a supplied variable is an array.
IsDate Tests if a supplied expression is a date.
IsEmpty Tests if a supplied variant is Empty.
IsError Tests if a supplied expression represents an error.
IsMissing Tests if an optional argument to a procedure is missing.
IsNull Tests if a supplied expression is Null.
IsNumeric Tests if a supplied expression is numeric.
IsObject Tests if a supplied variable represents an object
variable.
CVErr Produces an Error data type for a
supplied error code.
Error Returns the error message corresponding to a supplied
error code.
Erl
Err
Error$
Choose Selects a value from a list of arguments.
IIf Evaluates an expression and returns one of two values,
depending on whether the expression evaluates to True or False.
Switch Evaluates a list of Boolean expressions and returns a
value associated with the first true expression.
FormatCurrency Applies a currency format to an
expression and returns the result as a string.
FormatDateTime Applies a date/time format to an expression and returns
the result as a string.
FormatNumber Applies a number format to an expression and returns the
result as a string.
FormatPercent Applies a percentage format to an expression and returns
the result as a string.
Hex Converts a numeric value to hexadecimal notation and
returns the result as a string.
Hex$
Oct Converts a numeric value to octal notation and returns
the result as a string.
Oct$
Str Converts a numeric value to a string.
Str$
Val Converts a string to a numeric value.
Date Returns the current date.
Date$
DateAdd Adds a time interval to a date and/or time.
DateDiff Returns the number of intervals between two dates and/or
times.
DatePart Returns a part (day, month, year, etc.) of a supplied
date/time.
DateSerial Returns a Date from a supplied year, month and day
number.
DateValue Returns a Date from a String representation of a
date/time.
CVDate
Day Returns the day number (from 1 to 31) of a supplied date.
Hour Returns the hour component of a supplied time.
Minute Returns the minute component of a supplied time.
Month Returns the month number (from 1 to 12) of a supplied
date.
MonthName Returns the month name for a supplied month number (from
1 to 12).
Now Returns the current date and time.
Second Returns the second component of a supplied time.
Time Returns the current time.
Time$
Timer Returns the number of seconds that have elapsed since
midnight.
TimeSerial Returns a Time from a supplied hour, minute and second.
TimeValue Returns a Time from a String representation of a
date/time.
Weekday Returns an integer (from 1 to 7), representing the
weekday of a supplied date.
WeekdayName Returns the weekday name for a supplied integer (from 1
to 7).
Year Returns the year of a supplied date.
Abs Returns the absolute value of a
number.
Atn Calculates the arctangent of a supplied number.
Cos Calculates the cosine of a supplied angle.
Exp Calculates the value of ex for a supplied value of x.
Fix Truncates a number to an integer (rounding negative
numbers towards zero).
Int Returns the integer portion of a number (rounding
negative numbers away from zero).
Log Calculates the natural logarithm of a supplied number.
Rnd Generates a random number between 0 and 1.
Randomize
Round Rounds a number to a specified number of decimal places.
Sgn Returns an integer representing the arithmetic sign of a
number.
Sin Calculates the sine of a supplied angle.
Sqr Returns the square root of a number.
Tan Calculates the tangent of a supplied angle.
DDB Calculates the depreciation of an
asset during a specified period, using the Double Declining Balance Method.
FV Calculates the future value of a loan or investment.
IPmt Calculates the interest part of a payment, during a
specific period, for a loan or investment.
IRR Calculates the internal rate of return for a series of
periodic cash flows.
MIRR Calculates the modified internal rate of return for a
series of periodic cash flows.
NPer Calculates the number of periods for a loan or
investment.
NPV Calculates the net present value of an investment.
Pmt Calculates the constant periodic payments for a loan or
investment.
PPmt Calculates the principal part of a payment, during a
specific period, for a loan or investment.
PV Calculates the present value of a loan or investment.
Rate Calculates the interest rate per period for a loan or
investment.
SLN Calculates the straight line depreciation of an asset for
a single period.
SYD Calculates the sum-of-years’ digits depreciation for a
specified period in the lifetime of an asset.
CurDir Returns the current path, as a
string.
CurDir$
Dir Returns the first file or directory name that matches a
specified pattern and attributes.
ChDir
ChDrive
RmDir
MkDir
FreeFile
FileAttr Returns the mode of a file that has been opened using the
Open statement.
FileDateTime Returns the last modified date and time of a supplied
file, directory or folder.
FileLen Returns the length of a supplied file, directory or
folder.
GetAttr Returns an integer, representing the attributes of a
supplied file, directory or folder.
Input
Input$
InputB
InputB$
Seek
EOF
LOF
FileCopy
Kill
CreateObject
GetObject
CallByName
Command
Command$
Shell
InputBox Displays a dialog box prompting the user for input.
MsgBox Displays a modal message box.
Beep
DeleteSetting
GetAIISettings
GetSetting
SaveSetting
Environ
Environ$
IMEStatus
AppActivate
Calendar
Load
Loc
MacID
MacScript
Partition
QBColor
Reset
RGB
SendKeys
SetAttr
Unload
UserForms
ObjPtr
StrPtr
VarPtr
TypeName
VarType
vb3DDKShadow
vb3DFace
vb3DHighlight
vb3DLight
vb3DShadow
vbAbort
vbAbortRetryIgnore
vbActiveBorder
vbActiveTitleBar
vbAlias
vbApplicationModal
vbApplicationWorkspace
vbAppTaskManager
vbAppWindows
vbArchive
vbArray
vbBack
vbBinaryCompare
vbBlack
vbBlue
vbBoolean
vbButtonFace
vbButtonShadow
vbButtonText
vbByte
vbCalGreg
vbCalHijri
vbCancel
vbCr
vbCritical
vbCrLf
vbCurrency
vbCyan
vbDatabaseCompare
vbDataObject
vbDate
vbDecimal
vbDefaultButton1
vbDefaultButton2
vbDefaultButton3
vbDefaultButton4
vbDesktop
vbDirectory
vbDouble
vbEmpty
vbError
vbExclamation
vbFalse
vbFirstFourDays
vbFirstFullWeek
vbFirstJan1
vbFormCode
vbFormControlMenu
vbFormFeed
vbFormMDIForm
vbFriday
vbFromUnicode
vbGeneralDate
vbGet
vbGrayText
vbGreen
vbHidden
vbHide
vbHighlight
vbHighlightText
vbHiragana
vbIgnore
vbIMEAlphaDbl
vbIMEAlphaSng
vbIMEDisable
vbIMEHiragana
vbIMEKatakanaDbl
vbIMEKatakanaSng
vbIMEModeAlpha
vbIMEModeAlphaFull
vbIMEModeDisable
vbIMEModeHangul
vbIMEModeHangulFull
vbIMEModeHiragana
vbIMEModeKatakana
vbIMEModeKatakanaHalf
vbIMEModeNoControl
vbIMEModeOff
vbIMEModeOn
vbIMENoOp
vbIMEOff
vbIMEOn
vbInactiveBorder
vbInactiveCaptionText
vbInactiveTitleBar
vbInfoBackground
vbInformation
vbInfoText
vbInteger
vbKatakana
vbKey0
vbKey1
vbKey2
vbKey3
vbKey4
vbKey5
vbKey6
vbKey7
vbKey8
vbKey9
vbKeyA
vbKeyAdd
vbKeyB
vbKeyBack
vbKeyC
vbKeyCancel
vbKeyCapital
vbKeyClear
vbKeyControl
vbKeyD
vbKeyDecimal
vbKeyDelete
vbKeyDivide
vbKeyDown
vbKeyE
vbKeyEnd
vbKeyEscape
vbKeyExecute
vbKeyF
vbKeyF1
vbKeyF10
vbKeyF11
vbKeyF12
vbKeyF13
vbKeyF14
vbKeyF15
vbKeyF16
vbKeyF2
vbKeyF3
vbKeyF4
vbKeyF5
vbKeyF6
vbKeyF7
vbKeyF8
vbKeyF9
vbKeyG
vbKeyH
vbKeyHelp
vbKeyHome
vbKeyI
vbKeyInsert
vbKeyJ
vbKeyK
vbKeyL
vbKeyLButton
vbKeyLeft
vbKeyM
vbKeyMButton
vbKeyMenu
vbKeyMultiply
vbKeyN
vbKeyNumlock
vbKeyNumpad0
vbKeyNumpad1
vbKeyNumpad2
vbKeyNumpad3
vbKeyNumpad4
vbKeyNumpad5
vbKeyNumpad6
vbKeyNumpad7
vbKeyNumpad8
vbKeyNumpad9
vbKeyO
vbKeyP
vbKeyPageDown
vbKeyPageUp
vbKeyPause
vbKeyPrint
vbKeyQ
vbKeyR
vbKeyRButton
vbKeyReturn
vbKeyRight
vbKeyS
vbKeySelect
vbKeySeparator
vbKeyShift
vbKeySnapshot
vbKeySpace
vbKeySubtract
vbKeyT
vbKeyTab
vbKeyU
vbKeyUp
vbKeyV
vbKeyW
vbKeyX
vbKeyY
vbKeyZ
vbLet
vbLf
vbLong
vbLongDate
vbLongTime
vbLowerCase
vbMagenta
vbMaximizedFocus
vbMenuBar
vbMenuText
vbMethod
vbMinimizedFocus
vbMinimizedNoFocus
vbModal
vbModeless
vbMonday
vbMsgBox
vbMsgBoxHelpButton
vbMsgBoxRight
vbMsgBoxRtlReading
vbMsgBoxSetForeground
vbMsgBoxText
vbNarrow
vbNewLine
vbNo
vbNormal
vbNormalFocus
vbNormalNoFocus
vbNull
vbNullChar
vbNullString
vbObject
vbObjectError
vbOK
vbOKCancel
vbOKOnly
vbProperCase
vbQuestion
vbReadOnly
vbRed
vbRetry
vbRetryCancel
vbSaturday
vbScrollBars
vbSet
vbShortDate
vbShortTime
vbSingle
vbString
vbSunday
vbSystem
vbSystemModal
vbTab
vbTextCompare
vbThursday
vbTitleBarText
vbTrue
vbTuesday
vbUnicode
vbUpperCase
vbUseDefault
vbUserDefinedType
vbUseSystem
vbUseSystemDayOfWeek
vbVariant
vbVerticalTab
vbVolume
vbWednesday
vbWhite
vbWide
vbWindowBackground
vbWindowFrame
vbWindowText
vbYellow
vbYes
vbYesNo
vbYesNoCancel

This is a non exclusive list with Excel reserved words:

 Abs
 Accelerator
 Access
 AccessMode
 Action
 Activate
 ActivateMicrosoftApp
 ActivateNext
 ActivatePrevious
 ActiveCell
 ActiveChart
 ActiveDialog
 ActiveMenuBar
 ActivePane
 ActivePrinter
 ActiveSheet
 ActiveWindow
 ActiveWorkbook
 Add
 AddChartAutoFormat
 AddCustomList
 AddFields
 AddIn
 AddIndent
 AddIns
 AddItem
 AddMenu
 AddReplacement
 Address
 AddressLocal
 AddToTable
 AddVertex
 AdvancedFilter
 After
 AlertBeforeOverwriting
 Alias
 Alignment
 AltStartupPath
 AlwaysSuggest
 Amount
 And
 Any
 App
 AppActivate
 Appearance
 Append
 AppendLast
 Application
 ApplyDataLabels
 ApplyNames
 ApplyOutlineStyles
 Arc
 Arcs
 Area
 Area3DGroup
 AreaGroup
 AreaGroups
 Areas
 Arg0
 Arg1
 Arg10
 Arg11
 Arg12
 Arg13
 Arg14
 Arg15
 Arg16
 Arg17
 Arg18
 Arg19
 Arg2
 Arg20
 Arg21
 Arg22
 Arg23
 Arg24
 Arg25
 Arg26
 Arg27
 Arg28
 Arg29
 Arg3
 Arg30
 Arg31
 Arg4
 Arg5
 Arg6
 Arg7
 Arg8
 Arg9
 ArgName
 Arrange
 ArrangeStyle
 Array
 ArrowHeadLength
 ArrowHeadStyle
 ArrowHeadWidth
 ArrowNumber
 As
 Asc
 ascb
 ascw
 AskToUpdateLinks
 Atn
 attribute
 Attributes
 Author
 AutoComplete
 AutoCorrect
 AutoFill
 AutoFilter
 AutoFilterMode
 AutoFit
 AutoFormat
 AutoLoad
 Automatic
 AutomaticStyles
 AutoOutline
 AutoPage
 AutoScaling
 AutoSize
 AutoText
 AutoUpdate
 Axes
 Axis
 AxisBetweenCategories
 AxisGroup
 AxisObj
 AxisTitle
 B
 Background
 Backward
 Bar3DGroup
 BarGroup
 BarGroups
 Base
 BasedOn
 BaseField
 BaseItem
 BasicCode
 BCCRecipients
 Beep
 Before
 begin
 BF
 Binary
 BlackAndWhite
 Blue
 Bold
 Boolean
 Border
 BorderAround
 Borders
 Bottom
 BottomMargin
 BottomRightCell
 BringToFront
 Build
 BuiltIn
 BuiltinDocumentProperties
 BuiltInFace
 Button
 Buttons
 ButtonText
 By
 ByRef
 ByRow
 ByVal
 Calculate
 CalculateBeforeSave
 Calculation
 Call
 Caller
 Cancel
 CancelButton
 CanPlaySounds
 CanRecordSounds
 CapitalizeNamesOfDays
 Caption
 caption
 Case
 Category
 CategoryLabels
 CategoryLocal
 CategoryNames
 CategoryTitle
 CBool
 cbyte
 CCRecipients
 CCur
 CDate
 CDbl
 cdec
 CDecl
 Cell
 Cell1
 Cell2
 CellDragAndDrop
 Cells
 CenterFooter
 CenterHeader
 CenterHorizontally
 CenterVertically
 Centimeters
 CentimetersToPoints
 ChangeFileAccess
 ChangeLink
 ChangeScenario
 ChangingCell
 ChangingCells
 Channel
 Character
 Characters
 CharCode
 Chart
 ChartArea
 ChartGroup
 ChartGroups
 ChartObject
 ChartObjects
 Charts
 ChartSize
 ChartTitle
 ChartWizard
 ChartWizardDisplay
 ChDir
 ChDrive
 CheckBox
 CheckBoxes
 Checked
 CheckSpelling
 ChildField
 ChildItems
 choose
 Chr
 chrb
 chrw
 CInt
 CircularReference
 Class
 ClassType
 Clear
 ClearArrows
 ClearContents
 ClearFormats
 ClearNotes
 ClearOutline
 clientheight
 clientleft
 clienttop
 clientwidth
 ClipboardFormats
 CLng
 Close
 Closed
 Collate
 Color
 ColorButtons
 ColorIndex
 ColorPalette
 Colors
 Column
 Column3DGroup
 ColumnAbsolute
 ColumnDifferences
 ColumnFields
 ColumnGrand
 ColumnGroup
 ColumnGroups
 ColumnIndex
 ColumnInput
 ColumnLevels
 ColumnOffset
 ColumnRange
 Columns
 ColumnSize
 ColumnWidth
 Comma
 command
 CommandUnderlines
 Comments
 Compare
 Comparison
 ConflictResolution
 ConsecutiveDelimiter
 Consolidate
 ConsolidationFunction
 ConsolidationOptions
 ConsolidationSources
 Const
 Constant
 Constants
 ConstrainNumeric
 Container
 ContainsBIFF
 ContainsPICT
 ContainsRTF
 ContainsVALU
 Contents
 Context
 Conversion
 Convert
 Converter
 ConvertFormula
 Copies
 Copy
 CopyFace
 CopyFile
 CopyFromRecordset
 CopyObjectsWithCells
 CopyPicture
 CopyToRange
 Corners
 Cos
 Count
 CreateBackup
 CreateLinks
 CreateNames
 CreateObject
 CreatePublisher
 CreateSummary
 Creator
 Criteria1
 Criteria2
 CriteriaRange
 Crosses
 CrossesAt
 CrtBorder
 CrtInterior
 CSng
 CStr
 CurDir
 Currency
 CurrentArray
 CurrentPage
 CurrentRegion
 Cursor
 CustomDictionary
 CustomDocumentProperties
 CustomListCount
 Cut
 CutCopyMode
 CVar
 CVDate
 CVErr
 Data
 DataBodyRange
 DataEntryMode
 DataFields
 DataLabel
 DataLabelRange
 DataLabels
 DataRange
 DataSeries
 DataSeriesIn
 DataSheet
 DataType
 Date
 Date1904
 dateadd
 datediff
 datepart
 DateSerial
 DateValue
 Day
 ddb
 DDEAppReturnCode
 DDEExecute
 DDEInitiate
 DDEPoke
 DDERequest
 DDETerminate
 Debug
 Declare
 Default
 DefaultButton
 DefaultFilePath
 DefBool
 defbyte
 DefCur
 DefDate
 DefDbl
 defdec
 DefInt
 DefLng
 DefObj
 DefSng
 DefStr
 DefVar
 Delete
 DeleteChartAutoFormat
 DeleteCustomList
 DeleteNumberFormat
 DeleteReplacement
 deletesetting
 Delimiter
 Delivery
 Dependents
 DepthPercent
 Description
 Deselect
 Destination
 DestName
 Dialog
 DialogBox
 DialogFrame
 Dialogs
 DialogSheet
 DialogSheets
 Dim
 Dir
 DirectDependents
 Direction
 DirectPrecedents
 DismissButton
 Display3DShading
 DisplayActiveCell
 DisplayAlerts
 DisplayAsIcon
 DisplayAutomaticPageBreaks
 DisplayBlanksAs
 DisplayClipboardWindow
 DisplayDrawingObjects
 DisplayEquation
 DisplayExcel4Menus
 DisplayFormat
 DisplayFormula
 DisplayFormulaBar
 DisplayFormulas
 DisplayFullScreen
 DisplayGridlines
 DisplayHeadings
 DisplayHorizontalScrollBar
 DisplayInfoWindow
 DisplayNames
 DisplayNote
 DisplayNoteIndicator
 DisplayOutline
 DisplayProtection
 DisplayRecentFiles
 DisplayRightToLeft
 DisplayRSquared
 DisplayScrollBars
 DisplayStatusBar
 DisplayVerticalScrollBar
 DisplayWorkbookTabs
 DisplayZeros
 Do
 DoEvents
 Double
 DoubleClick
 DoughnutGroup
 DoughnutGroups
 DoughnutHoleSize
 Down
 DownBars
 DownloadNewMail
 Draft
 Drawing
 DrawingObject
 DrawingObjects
 Drawings
 Drive
 DropDown
 DropDownLines
 DropDowns
 DropLines
 Duplicate
 Each
 EarliestTime
 EchoOn
 Edit
 Editable
 EditBox
 EditBoxes
 EditDirectlyInCell
 Edition
 EditionOptions
 EditionRef
 Elevation
 Else
 ElseIf
 Empty
 EnableAnimations
 EnableAutoComplete
 EnableAutoFilter
 EnableCancelKey
 Enabled
 EnableOutlining
 EnablePivotTable
 EnableTipWizard
 Enclosures
 End
 end
 EndIf
 EndStyle
 EntireColumn
 EntireRow
 environ
 EOF
 Eqv
 Erase
 Erl
 Err
 Error
 ErrorBar
 ErrorBars
 eval
 Evaluate
 Events
 Excel4IntlMacroSheet
 Excel4IntlMacroSheets
 Excel4MacroSheet
 Excel4MacroSheets
 ExclusiveAccess
 execute
 ExecuteExcel4Macro
 Exit
 Exp
 Explicit
 Explosion
 Expression
 Extend
 External
 ExtraTitle
 F
 False
 Field
 FieldInfo
 File
 FileAttr
 FileConverters
 FileCopy
 FileDateTime
 FileFilter
 FileFormat
 FileLen
 Filename
 FileNumber
 FillAcrossSheets
 FillDown
 FillLeft
 FillRight
 FillUp
 FilterIndex
 FilterMode
 Find
 FindFile
 FindNext
 FindPrevious
 FirstPageNumber
 FirstSliceAngle
 FitToPagesTall
 FitToPagesWide
 Fix
 FixedDecimal
 FixedDecimalPlaces
 Floor
 Focus
 Font
 FontStyle
 FooterMargin
 For
 Format
 FormatName
 Formula
 FormulaArray
 FormulaHidden
 FormulaLocal
 FormulaR1C1
 FormulaR1C1Local
 Formulas
 Forward
 ForwardMailer
 FreeFile
 FreezePanes
 From
 FromReferenceStyle
 FullName
 Function
 FunctionWizard
 fv
 Gallery
 GapDepth
 GapWidth
 Get
 getallsettings
 GetAttr
 GetCustomListContents
 GetCustomListNum
 GetObject
 GetOpenFilename
 GetSaveAsFilename
 getsetting
 Global
 Goal
 GoalSeek
 GoSub
 Goto
 Graph
 Green
 GridlineColor
 GridlineColorIndex
 Gridlines
 Group
 GroupBox
 GroupBoxes
 GroupBy
 GroupLevel
 GroupObject
 GroupObjects
 Groups
 HasArray
 HasAutoFormat
 HasAxis
 HasDataLabel
 HasDataLabels
 HasDropLines
 HasErrorBars
 HasFormula
 HasHiLoLines
 HasLegend
 HasLinks
 HasMailer
 HasMajorGridlines
 HasMenu
 HasMinorGridlines
 HasPassword
 HasRadarAxisLabels
 HasRoutingSlip
 HasSeriesLines
 HasTitle
 HasUpDownBars
 Header
 HeaderMargin
 Height
 HeightPercent
 Help
 HelpButton
 HelpContextID
 HelpFile
 Hex
 Hidden
 HiddenFields
 HiddenItems
 Hide
 HiLoLines
 HorizontalAlignment
 Hour
 IconFileName
 IconIndex
 IconLabel
 Id
 If
 IgnoreReadOnlyRecommended
 IgnoreRelativeAbsolute
 IgnoreRemoteRequests
 IgnoreUppercase
 iif
 IMEStatus
 Imp
 Import
 ImportChart
 ImportData
 In
 Inches
 InchesToPoints
 Include
 IncludeAlignment
 IncludeBorder
 IncludeFont
 IncludeNumber
 IncludePatterns
 IncludeProtection
 Index
 IndexLocal
 InitialFilename
 InnerDetail
 Input
 InputB
 InputBox
 InputType
 Insert
 InsertFile
 Installed
 InStr
 InStrB
 Int
 Integer
 Interactive
 Intercept
 InterceptIsAuto
 Interior
 International
 Intersect
 InvertIfNegative
 ipmt
 irr
 Is
 IsArray
 IsDate
 IsEmpty
 IsError
 IsGap
 IsMissing
 IsNull
 IsNumeric
 IsObject
 Italic
 Item
 Iteration
 Justify
 Key
 Key1
 Key2
 Key3
 Keys
 Keywords
 Kill
 Label
 LabelRange
 Labels
 LargeButtons
 LargeChange
 LargeScroll
 LatestEdition
 LatestTime
 Launch
 LBound
 LCase
 Left
 LeftB
 LeftColumn
 LeftFooter
 LeftHeader
 LeftMargin
 Legend
 LegendEntries
 LegendEntry
 LegendKey
 Len
 LenB
 Length
 Let
 Lib
 LibraryPath
 Like
 Line
 Line3DGroup
 LineGroup
 LineGroups
 Lines
 LineStyle
 Link
 LinkCombo
 LinkedCell
 LinkedObject
 LinkInfo
 LinkNumber
 Links
 LinkSources
 List
 ListArray
 ListBox
 ListBoxes
 ListCount
 ListFillRange
 ListHeaderRows
 ListIndex
 ListNames
 ListNum
 load
 Loc
 Local
 LocationInTable
 Lock
 Locked
 LockedText
 LOF
 Log
 Long
 LookAt
 LookIn
 Loop
 LSet
 LTrim
 MacID
 Macro
 MacroOptions
 MacroType
 MacScript
 Mailer
 MailLogoff
 MailLogon
 MailSession
 MailSystem
 MajorGridlines
 MajorTickMark
 MajorUnit
 MajorUnitIsAuto
 MajorVersion
 MarkerBackgroundColor
 MarkerBackgroundColorIndex
 MarkerForegroundColor
 MarkerForegroundColorIndex
 MarkerStyle
 MatchByte
 MatchCase
 MathCoprocessorAvailable
 Max
 MaxChange
 MaxColumns
 MaximumScale
 MaximumScaleIsAuto
 MaxIterations
 MaxRows
 me
 MemoryFree
 MemoryTotal
 MemoryUsed
 Menu
 MenuBar
 MenuBars
 MenuItem
 MenuItems
 Menus
 MenuText
 Merge
 Message
 Mid
 MidB
 Min
 MinimumScale
 MinimumScaleIsAuto
 MinorGridlines
 MinorTickMark
 MinorUnit
 MinorUnitIsAuto
 MinorVersion
 MinusValues
 Minute
 mirr
 MkDir
 Mod
 Mode
 Module
 Modules
 Month
 MouseAvailable
 Move
 MoveAfterReturn
 MoveAfterReturnDirection
 MsgBox
 MultiLine
 MultiSelect
 MultiUse
 MultiUserEditing
 Name
 NameIsAuto
 NameLocal
 Names
 NavigateArrow
 NetworkTemplatesPath
 new
 NewEnum
 NewName
 NewSeries
 NewWindow
 Next
 NextLetter
 Not
 Note
 NoteText
 Nothing
 Notify
 Now
 nper
 npv
 Null
 Number
 NumberFormat
 NumberFormatLinked
 NumberFormatLocal
 NumCategoryLabels
 NumSeriesLabels
 Object
 Oct
 Of
 Offset
 OLEObject
 oleobjectblob
 OLEObjects
 OLEType
 OmitBackground
 OmitColumn
 OmitRow
 On
 OnAction
 OnCalculate
 OnData
 OnDoubleClick
 OnEntry
 OnKey
 OnRepeat
 OnSave
 OnSheetActivate
 OnSheetDeactivate
 OnTime
 OnUndo
 OnWindow
 Open
 OpenLinks
 OpenText
 OperatingSystem
 Operation
 Operator
 Option
 Optional
 OptionButton
 OptionButtons
 Or
 Order
 Order1
 Order2
 Order3
 OrderCustom
 OrganizationName
 Orientation
 Origin
 Other
 OtherChar
 Outline
 OutlineFont
 OutlineLevel
 Output
 Oval
 Ovals
 Overlap
 PageBreak
 PageBreaks
 PageField
 PageFields
 PageRange
 PageSetup
 Pane
 Panes
 PaperSize
 ParamArray
 Parent
 ParentField
 ParentItem
 ParentItems
 ParentShowDetail
 ParentWorksheet
 Parse
 ParseLine
 partition
 Password
 PasswordEdit
 Paste
 PasteFace
 PasteSpecial
 Path
 PathName
 PathSeparator
 Pattern
 PatternColor
 PatternColorIndex
 Period
 Periods
 Perspective
 PhoneticAccelerator
 Picture
 Pictures
 PictureType
 PictureUnit
 Pie3DGroup
 PieGroup
 PieGroups
 PivotField
 PivotFields
 PivotItem
 PivotItems
 PivotTable
 PivotTables
 PivotTableWizard
 Placement
 Play
 PlotArea
 PlotBy
 PlotOrder
 PlotVisibleOnly
 pmt
 Point
 Points
 Position
 Post
 ppmt
 Precedents
 PrecisionAsDisplayed
 PrefixCharacter
 Preserve
 Preview
 Previous
 PreviousSelections
 Print
 PrintArea
 PrintGridlines
 PrintHeadings
 PrintNotes
 PrintObject
 PrintOut
 PrintPreview
 PrintQuality
 PrintTitleColumns
 PrintTitleRows
 PrintToFile
 Priority
 Private
 Procedure
 Prompt
 PromptForSummaryInfo
 Property
 Protect
 ProtectContents
 ProtectDrawingObjects
 Protection
 ProtectionMode
 ProtectScenarios
 ProtectStructure
 ProtectWindows
 Public
 Pushed
 Put
 pv
 qbcolor
 Quit
 R1C1
 RadarAxisLabels
 RadarGroup
 RadarGroups
 Random
 Randomize
 Range
 Range1
 Range2
 RangeSelection
 rate
 Read
 ReadOnly
 ReadOnlyRecommended
 Received
 Recipients
 Record
 RecordMacro
 RecordRelative
 Rectangle
 Rectangles
 Red
 ReDim
 Reference
 ReferenceStyle
 RefersTo
 RefersToLocal
 RefersToR1C1
 RefersToR1C1Local
 RefersToRange
 RefreshDate
 RefreshName
 RefreshTable
 RegisteredFunctions
 RegisterXLL
 RelativeTo
 Rem
 Remove
 RemoveAllItems
 RemoveItem
 RemoveSubtotal
 Repeat
 Replace
 Replacement
 ReplacementList
 ReplaceText
 Reply
 ReplyAll
 ReportType
 Reserved
 Reset
 ResetTipWizard
 Reshape
 Resize
 Resource
 Restore
 ResultCells
 Resume
 Return
 ReturnReceipt
 ReturnType
 ReturnWhenDone
 ReversePlotOrder
 RevisionNumber
 RGB
 Right
 RightAngleAxes
 RightB
 RightFooter
 RightHeader
 RightMargin
 RmDir
 Rnd
 Root
 Rotation
 RoundedCorners
 Route
 Routed
 RouteWorkbook
 RoutingSlip
 Row
 RowAbsolute
 Rowcol
 RowDifferences
 RowFields
 RowGrand
 RowHeight
 RowIndex
 RowInput
 RowLevels
 RowOffset
 RowRange
 Rows
 RowSize
 RSet
 RTrim
 Run
 RunAutoMacros
 Save
 SaveAs
 SaveAsOldFileFormat
 SaveChanges
 SaveCopyAs
 Saved
 SaveData
 SaveLinkValues
 savesetting
 ScaleType
 Scenario
 Scenarios
 Schedule
 ScreenUpdating
 Script
 scriptengine
 Scroll
 ScrollBar
 ScrollBars
 ScrollColumn
 ScrollRow
 ScrollWorkbookTabs
 SearchDirection
 SearchOrder
 Second
 Seek
 Select
 Selected
 SelectedSheets
 Selection
 Semicolon
 SendDateTime
 Sender
 SendKeys
 SendMail
 SendMailer
 SendToBack
 Series
 SeriesCollection
 SeriesLabels
 SeriesLines
 Set
 SetAttr
 SetBackgroundPicture
 SetDefaultChart
 SetEchoOn
 SetInfoDisplay
 SetLinkOnData
 Sgn
 Shadow
 Shared
 Sheet
 SheetBackground
 Sheets
 SheetsInNewWorkbook
 Shell
 Shift
 ShortcutKey
 ShortcutMenus
 Show
 ShowAllData
 ShowConflictHistory
 ShowDataForm
 ShowDependents
 ShowDetail
 ShowErrors
 ShowLegendKey
 ShowLevels
 ShowPages
 ShowPrecedents
 ShowRevisionHistory
 ShowToolTips
 Sin
 Single
 Size
 SizeWithWindow
 SkipBlanks
 sln
 SmallChange
 SmallScroll
 Smooth
 Sort
 SortMethod
 SortSpecial
 SoundNote
 Source
 SourceData
 SourceName
 Sources
 SourceType
 Space
 Spc
 SpecialCells
 Spinner
 Spinners
 Split
 SplitColumn
 SplitHorizontal
 SplitRow
 SplitVertical
 Sqr
 StandardFont
 StandardFontSize
 StandardHeight
 StandardWidth
 Start
 StartRow
 StartupPath
 startupposition
 Static
 Status
 StatusBar
 Step
 Stop
 Str
 StrComp
 StrConv
 Strict
 Strikethrough
 String
 Structure
 Style
 Styles
 Sub
 Subject
 SubscribeTo
 Subscript
 Subtotal
 Subtotals
 SubType
 Summary
 SummaryBelowData
 SummaryColumn
 SummaryRow
 Superscript
 SurfaceGroup
 switch
 syd
 SyncHorizontal
 SyncVertical
 Tab
 Table
 TableDestination
 TableName
 TableRange1
 TableRange2
 TabRatio
 Tan
 Template
 TemplatesPath
 Text
 TextBox
 TextBoxes
 TextLocal
 TextQualifier
 TextToColumns
 Then
 ThisWorkbook
 TickLabelPosition
 TickLabels
 TickLabelSpacing
 TickMarkSpacing
 Time
 Timer
 TimeSerial
 TimeValue
 Title
 To
 ToAbsolute
 ToLeft
 Toolbar
 ToolbarButton
 ToolbarButtons
 Toolbars
 Top
 Topic
 TopLeftCell
 TopMargin
 TopRow
 ToRecipients
 ToReferenceStyle
 ToRight
 TotalLevels
 TotalList
 TowardPrecedent
 TrackStatus
 TransitionExpEval
 TransitionFormEntry
 TransitionMenuKey
 TransitionMenuKeyAction
 TransitionNavigKeys
 Transpose
 Trend
 Trendline
 Trendlines
 Trim
 True
 TwoInitialCapitals
 Type
 TypeName
 typeof
 UBound
 UCase
 Underline
 Undo
 Ungroup
 Union
 Unique
 Unknown
 unload
 Unlock
 Unprotect
 Until
 Up
 UpBars
 Update
 UpdateFromFile
 UpdateLink
 UpdateLinks
 UpdateRemoteReferences
 UsableHeight
 UsableWidth
 UsedRange
 UserInterfaceOnly
 UserName
 UseRowColumnNames
 UserStatus
 UseStandardHeight
 UseStandardWidth
 Val
 Value
 Values
 ValueTitle
 Variant
 VarName
 VarType
 VaryByCategories
 vb_creatable
 vb_exposed
 vb_name
 vb_predeclaredid
 vbAbort
 vbAbortRetryIgnore
 vbApplicationModal
 vbArchive
 vbArray
 vbBoolean
 vbByte
 vbCancel
 vbCritical
 vbCurrency
 vbDataObject
 vbDate
 vbDecimal
 vbDefaultButton1
 vbDefaultButton2
 vbDefaultButton3
 vbDirectory
 vbDouble
 vbEmpty
 vbError
 vbExclamation
 vbHidden
 vbHiragana
 vbIgnore
 vbInformation
 vbInteger
 vbKatakana
 vbLong
 vbLowerCase
 vbNarrow
 vbNo
 vbNormal
 vbNull
 vbObject
 vbOK
 vbOKCancel
 vbOKOnly
 vbProperCase
 vbQuestion
 vbReadOnly
 vbRetry
 vbRetryCancel
 vbSingle
 vbString
 vbSystem
 vbSystemModal
 vbUpperCase
 vbUserDefinedType
 vbVariant
 vbVolume
 vbWide
 vbYes
 vbYesNo
 vbYesNoCancel
 Verb
 Version
 version
 Vertex
 VerticalAlignment
 Vertices
 Visible
 VisibleFields
 VisibleItems
 VisibleRange
 Volatile
 Wait
 Walls
 WallsAndGridlines2D
 WeekDay
 Weight
 Wend
 What
 Where
 Which
 While
 Whole
 Width
 Window
 WindowNumber
 Windows
 WindowsForPens
 WindowState
 WindowStyle
 With
 withevents
 Word
 Workbook
 Workbooks
 Worksheet
 Worksheets
 WrapText
 Write
 WritePassword
 WriteReserved
 WriteReservedBy
 WriteResPassword
 X1
 X2
 xl24HourClock
 xl3DArea
 xl3DBar
 xl3DColumn
 xl3DEffects1
 xl3DEffects2
 xl3DLine
 xl3DPie
 xl3DSurface
 xl4DigitYears
 xlA1
 xlAbove
 xlAbsolute
 xlAbsRowRelColumn
 xlAccounting1
 xlAccounting2
 xlAccounting3
 xlAccounting4
 xlAdd
 xlAddIn
 xlAll
 xlAllAtOnce
 xlAllExceptBorders
 xlAlternateArraySeparator
 xlAnd
 xlArea
 xlAscending
 xlAutoActivate
 xlAutoClose
 xlAutoDeactivate
 xlAutoFill
 xlAutomatic
 xlAutomaticUpdate
 xlAutoOpen
 xlAverage
 xlAxis
 xlBar
 xlBelow
 xlBIFF
 xlBitmap
 xlBlanks
 xlBMP
 xlBoth
 xlBottom
 xlBottom10Items
 xlBottom10Percent
 xlBuiltIn
 xlButton
 xlByColumns
 xlByRows
 xlCancel
 xlCap
 xlCascade
 xlCategory
 xlCenter
 xlCenterAcrossSelection
 xlCGM
 xlChangeAttributes
 xlChart
 xlChart4
 xlChartAsWindow
 xlChartInPlace
 xlChartSeries
 xlChartShort
 xlChartTitles
 xlChecker
 xlChronological
 xlCircle
 xlClassic1
 xlClassic2
 xlClassic3
 xlClipboard
 xlClipboardFormatBIFF
 xlClipboardFormatBIFF2
 xlClipboardFormatBIFF3
 xlClipboardFormatBIFF4
 xlClipboardFormatBinary
 xlClipboardFormatBitmap
 xlClipboardFormatCGM
 xlClipboardFormatCSV
 xlClipboardFormatDIF
 xlClipboardFormatDspText
 xlClipboardFormatEmbeddedObject
 xlClipboardFormatEmbedSource
 xlClipboardFormatLink
 xlClipboardFormatLinkSource
 xlClipboardFormatLinkSourceDesc
 xlClipboardFormatMovie
 xlClipboardFormatNative
 xlClipboardFormatObjectDesc
 xlClipboardFormatObjectLink
 xlClipboardFormatOwnerLink
 xlClipboardFormatPICT
 xlClipboardFormatPrintPICT
 xlClipboardFormatRTF
 xlClipboardFormatScreenPICT
 xlClipboardFormatStandardFont
 xlClipboardFormatStandardScale
 xlClipboardFormatSYLK
 xlClipboardFormatTable
 xlClipboardFormatText
 xlClipboardFormatToolFace
 xlClipboardFormatToolFacePICT
 xlClipboardFormatVALU
 xlClipboardFormatWK1
 xlClosed
 xlCodePage
 xlColor1
 xlColor2
 xlColor3
 xlColumn
 xlColumnField
 xlColumnHeader
 xlColumnItem
 xlColumns
 xlColumnSeparator
 xlColumnThenRow
 xlCombination
 xlCommand
 xlConsolidation
 xlConstants
 xlContents
 xlContinuous
 xlCopy
 xlCorner
 xlCount
 xlCountNums
 xlCountryCode
 xlCountrySetting
 xlCrissCross
 xlCross
 xlCSV
 xlCSVMac
 xlCSVMSDOS
 xlCSVWindows
 xlCurrencyBefore
 xlCurrencyCode
 xlCurrencyDigits
 xlCurrencyLeadingZeros
 xlCurrencyMinusSign
 xlCurrencyNegative
 xlCurrencySpaceBefore
 xlCurrencyTrailingZeros
 xlCustom
 xlCut
 xlDash
 xlDashDot
 xlDashDotDot
 xlDatabase
 xlDataField
 xlDataHeader
 xlDataItem
 xlDate
 xlDateOrder
 xlDateSeparator
 xlDay
 xlDayCode
 xlDayLeadingZero
 xlDBF2
 xlDBF3
 xlDBF4
 xlDebugCodePane
 xlDecimalSeparator
 xlDefaultAutoFormat
 xlDelimited
 xlDescending
 xlDesktop
 xlDialogActivate
 xlDialogActiveCellFont
 xlDialogAddChartAutoformat
 xlDialogAddinManager
 xlDialogAlignment
 xlDialogApplyNames
 xlDialogApplyStyle
 xlDialogAppMove
 xlDialogAppSize
 xlDialogArrangeAll
 xlDialogAssignToObject
 xlDialogAssignToTool
 xlDialogAttachText
 xlDialogAttachToolbars
 xlDialogAutoCorrect
 xlDialogAxes
 xlDialogBorder
 xlDialogCalculation
 xlDialogCellProtection
 xlDialogChangeLink
 xlDialogChartAddData
 xlDialogChartTrend
 xlDialogChartWizard
 xlDialogCheckboxProperties
 xlDialogClear
 xlDialogColorPalette
 xlDialogColumnWidth
 xlDialogCombination
 xlDialogConsolidate
 xlDialogCopyChart
 xlDialogCopyPicture
 xlDialogCreateNames
 xlDialogCreatePublisher
 xlDialogCustomizeToolbar
 xlDialogDataDelete
 xlDialogDataLabel
 xlDialogDataSeries
 xlDialogDefineName
 xlDialogDefineStyle
 xlDialogDeleteFormat
 xlDialogDeleteName
 xlDialogDemote
 xlDialogDisplay
 xlDialogEditboxProperties
 xlDialogEditColor
 xlDialogEditDelete
 xlDialogEditionOptions
 xlDialogEditSeries
 xlDialogErrorbarX
 xlDialogErrorbarY
 xlDialogExtract
 xlDialogFileDelete
 xlDialogFileSharing
 xlDialogFillGroup
 xlDialogFillWorkgroup
 xlDialogFilter
 xlDialogFilterAdvanced
 xlDialogFindFile
 xlDialogFont
 xlDialogFontProperties
 xlDialogFormatAuto
 xlDialogFormatChart
 xlDialogFormatCharttype
 xlDialogFormatFont
 xlDialogFormatLegend
 xlDialogFormatMain
 xlDialogFormatMove
 xlDialogFormatNumber
 xlDialogFormatOverlay
 xlDialogFormatSize
 xlDialogFormatText
 xlDialogFormulaFind
 xlDialogFormulaGoto
 xlDialogFormulaReplace
 xlDialogFunctionWizard
 xlDialogGallery3dArea
 xlDialogGallery3dBar
 xlDialogGallery3dColumn
 xlDialogGallery3dLine
 xlDialogGallery3dPie
 xlDialogGallery3dSurface
 xlDialogGalleryArea
 xlDialogGalleryBar
 xlDialogGalleryColumn
 xlDialogGalleryCustom
 xlDialogGalleryDoughnut
 xlDialogGalleryLine
 xlDialogGalleryPie
 xlDialogGalleryRadar
 xlDialogGalleryScatter
 xlDialogGoalSeek
 xlDialogGridlines
 xlDialogInsert
 xlDialogInsertObject
 xlDialogInsertPicture
 xlDialogInsertTitle
 xlDialogLabelProperties
 xlDialogListboxProperties
 xlDialogMacroOptions
 xlDialogMailLogon
 xlDialogMailNextLetter
 xlDialogMainChart
 xlDialogMainChartType
 xlDialogMenuEditor
 xlDialogMove
 xlDialogNew
 xlDialogNote
 xlDialogObjectProperties
 xlDialogObjectProtection
 xlDialogOpen
 xlDialogOpenLinks
 xlDialogOpenMail
 xlDialogOpenText
 xlDialogOptionsCalculation
 xlDialogOptionsChart
 xlDialogOptionsEdit
 xlDialogOptionsGeneral
 xlDialogOptionsListsAdd
 xlDialogOptionsTransition
 xlDialogOptionsView
 xlDialogOutline
 xlDialogOverlay
 xlDialogOverlayChartType
 xlDialogPageSetup
 xlDialogParse
 xlDialogPasteSpecial
 xlDialogPatterns
 xlDialogPivotFieldGroup
 xlDialogPivotFieldProperties
 xlDialogPivotFieldUngroup
 xlDialogPivotShowPages
 xlDialogPivotTableWizard
 xlDialogPlacement
 xlDialogPrint
 xlDialogPrinterSetup
 xlDialogPrintPreview
 xlDialogPromote
 xlDialogProperties
 xlDialogProtectDocument
 xlDialogPushbuttonProperties
 xlDialogReplaceFont
 xlDialogRoutingSlip
 xlDialogRowHeight
 xlDialogRun
 xlDialogSaveAs
 xlDialogSaveCopyAs
 xlDialogSaveNewObject
 xlDialogSaveWorkbook
 xlDialogSaveWorkspace
 xlDialogScale
 xlDialogScenarioAdd
 xlDialogScenarioCells
 xlDialogScenarioEdit
 xlDialogScenarioMerge
 xlDialogScenarioSummary
 xlDialogScrollbarProperties
 xlDialogSelectSpecial
 xlDialogSendMail
 xlDialogSeriesAxes
 xlDialogSeriesOrder
 xlDialogSeriesX
 xlDialogSeriesY
 xlDialogSetBackgroundPicture
 xlDialogSetPrintTitles
 xlDialogSetUpdateStatus
 xlDialogSheet
 xlDialogShowDetail
 xlDialogShowToolbar
 xlDialogSize
 xlDialogSort
 xlDialogSortSpecial
 xlDialogSplit
 xlDialogStandardFont
 xlDialogStandardWidth
 xlDialogStyle
 xlDialogSubscribeTo
 xlDialogSubtotalCreate
 xlDialogSummaryInfo
 xlDialogTable
 xlDialogTabOrder
 xlDialogTextToColumns
 xlDialogUnhide
 xlDialogUpdateLink
 xlDialogVbaInsertFile
 xlDialogVbaMakeAddin
 xlDialogVbaProcedureDefinition
 xlDialogView3d
 xlDialogWindowMove
 xlDialogWindowSize
 xlDialogWorkbookAdd
 xlDialogWorkbookCopy
 xlDialogWorkbookInsert
 xlDialogWorkbookMove
 xlDialogWorkbookName
 xlDialogWorkbookNew
 xlDialogWorkbookOptions
 xlDialogWorkbookProtect
 xlDialogWorkbookTabSplit
 xlDialogWorkbookUnhide
 xlDialogWorkgroup
 xlDialogWorkspace
 xlDialogZoom
 xlDiamond
 xlDIF
 xlDifferenceFrom
 xlDirect
 xlDisabled
 xlDistributed
 xlDivide
 xlDot
 xlDouble
 xlDoubleAccounting
 xlDoubleClosed
 xlDoubleOpen
 xlDoubleQuote
 xlDoughnut
 xlDown
 xlDownThenOver
 xlDownward
 xlDrawingObject
 xlDRW
 xlDXF
 xlEditionDate
 xlEntireChart
 xlEPS
 xlErrDiv0
 xlErrNA
 xlErrName
 xlErrNull
 xlErrNum
 xlErrorHandler
 xlErrors
 xlErrRef
 xlErrValue
 xlExcel2
 xlExcel2FarEast
 xlExcel3
 xlExcel4
 xlExcel4IntlMacroSheet
 xlExcel4MacroSheet
 xlExcel4Workbook
 xlExcelLinks
 xlExcelMenus
 xlExclusive
 xlExponential
 xlExtended
 xlExternal
 xlFill
 xlFillCopy
 xlFillDays
 xlFillDefault
 xlFillFormats
 xlFillMonths
 xlFillSeries
 xlFillValues
 xlFillWeekdays
 xlFillYears
 xlFilterCopy
 xlFilterInPlace
 xlFirst
 xlFitToPage
 xlFixedValue
 xlFixedWidth
 xlFloating
 xlFloor
 xlFormats
 xlFormula
 xlFormulas
 xlFreeFloating
 xlFullPage
 xlFunction
 xlGeneral
 xlGeneralFormatName
 xlGray16
 xlGray25
 xlGray50
 xlGray75
 xlGray8
 xlGrid
 xlGridline
 xlGrowth
 xlGrowthTrend
 xlGuess
 xlHairline
 xlHGL
 xlHidden
 xlHide
 xlHigh
 xlHorizontal
 xlHourCode
 xlIBeam
 xlIcons
 xlImmediatePane
 xlIndex
 xlInfo
 xlInside
 xlInteger
 xlInterpolated
 xlInterrupt
 xlIntlAddIn
 xlIntlMacro
 xlJustify
 xlLandscape
 xlLast
 xlLastCell
 xlLeft
 xlLeftBrace
 xlLeftBracket
 xlLeftToRight
 xlLegend
 xlLightDown
 xlLightHorizontal
 xlLightUp
 xlLightVertical
 xlLine
 xlLinear
 xlLinearTrend
 xlList1
 xlList2
 xlList3
 xlListSeparator
 xlLocalFormat1
 xlLocalFormat2
 xlLocalSessionChanges
 xlLogarithmic
 xlLogical
 xlLong
 xlLotusHelp
 xlLow
 xlLowerCaseColumnLetter
 xlLowerCaseRowLetter
 xlMacintosh
 xlMacrosheetCell
 xlManual
 xlManualUpdate
 xlMAPI
 xlMax
 xlMaximized
 xlMaximum
 xlMDY
 xlMedium
 xlMetric
 xlMicrosoftAccess
 xlMicrosoftFoxPro
 xlMicrosoftMail
 xlMicrosoftPowerPoint
 xlMicrosoftProject
 xlMicrosoftSchedulePlus
 xlMicrosoftWord
 xlMin
 xlMinimized
 xlMinimum
 xlMinusValues
 xlMinuteCode
 xlMixed
 xlModule
 xlMonth
 xlMonthCode
 xlMonthLeadingZero
 xlMonthNameChars
 xlMove
 xlMoveAndSize
 xlMovingAvg
 xlMSDOS
 xlMultiply
 xlNarrow
 xlNext
 xlNextToAxis
 xlNo
 xlNoButtonChanges
 xlNoCap
 xlNoChange
 xlNoChanges
 xlNoDockingChanges
 xlNoDocuments
 xlNoMailSystem
 xlNoncurrencyDigits
 xlNone
 xlNonEnglishFunctions
 xlNormal
 xlNorthwestArrow
 xlNoShapeChanges
 xlNotes
 xlNotPlotted
 xlNotYetRouted
 xlNumber
 xlNumbers
 xlOff
 xlOLEEmbed
 xlOLELink
 xlOLELinks
 xlOn
 xlOneAfterAnother
 xlOpaque
 xlOpen
 xlOpenSource
 xlOr
 xlOtherSessionChanges
 xlOutside
 xlOverThenDown
 xlPageField
 xlPageHeader
 xlPageItem
 xlPaper10x14
 xlPaper11x17
 xlPaperA3
 xlPaperA4
 xlPaperA4Small
 xlPaperA5
 xlPaperB4
 xlPaperB5
 xlPaperCsheet
 xlPaperDsheet
 xlPaperEnvelope10
 xlPaperEnvelope11
 xlPaperEnvelope12
 xlPaperEnvelope14
 xlPaperEnvelope9
 xlPaperEnvelopeB4
 xlPaperEnvelopeB5
 xlPaperEnvelopeB6
 xlPaperEnvelopeC3
 xlPaperEnvelopeC4
 xlPaperEnvelopeC5
 xlPaperEnvelopeC6
 xlPaperEnvelopeC65
 xlPaperEnvelopeDL
 xlPaperEnvelopeItaly
 xlPaperEnvelopeMonarch
 xlPaperEnvelopePersonal
 xlPaperEsheet
 xlPaperExecutive
 xlPaperFanfoldLegalGerman
 xlPaperFanfoldStdGerman
 xlPaperFanfoldUS
 xlPaperFolio
 xlPaperLedger
 xlPaperLegal
 xlPaperLetter
 xlPaperLetterSmall
 xlPaperNote
 xlPaperQuarto
 xlPaperStatement
 xlPaperTabloid
 xlPaperUser
 xlPart
 xlPCT
 xlPCX
 xlPercent
 xlPercentDifferenceFrom
 xlPercentOf
 xlPercentOfColumn
 xlPercentOfRow
 xlPercentOfTotal
 xlPIC
 xlPICT
 xlPicture
 xlPie
 xlPivotTable
 xlPlaceholders
 xlPlotArea
 xlPLT
 xlPlus
 xlPlusValues
 xlPolynomial
 xlPortrait
 xlPower
 xlPowerTalk
 xlPrevious
 xlPrimary
 xlPrinter
 xlProduct
 xlPublisher
 xlPublishers
 xlR1C1
 xlRadar
 xlReadOnly
 xlReadWrite
 xlReference
 xlRelative
 xlRelRowAbsColumn
 xlRight
 xlRightBrace
 xlRightBracket
 xlRoutingComplete
 xlRoutingInProgress
 xlRowField
 xlRowHeader
 xlRowItem
 xlRows
 xlRowSeparator
 xlRowThenColumn
 xlRTF
 xlRunningTotal
 xlScale
 xlScreen
 xlScreenSize
 xlSecondary
 xlSecondCode
 xlSelect
 xlSemiautomatic
 xlSemiGray75
 xlSendPublisher
 xlSeries
 xlShared
 xlShort
 xlShowLabel
 xlShowLabelAndPercent
 xlShowPercent
 xlShowValue
 xlSimple
 xlSingle
 xlSingleAccounting
 xlSingleQuote
 xlSolid
 xlSortLabels
 xlSortValues
 xlSquare
 xlStack
 xlStandardSummary
 xlStar
 xlStDev
 xlStDevP
 xlStError
 xlStretch
 xlStrict
 xlSubscriber
 xlSubscribers
 xlSubtract
 xlSum
 xlSYLK
 xlSyllabary
 xlTableBody
 xlTemplate
 xlText
 xlTextBox
 xlTextMac
 xlTextMSDOS
 xlTextPrinter
 xlTextValues
 xlTextWindows
 xlThick
 xlThin
 xlThousandsSeparator
 xlTIF
 xlTiled
 xlTimeLeadingZero
 xlTimeSeparator
 xlTitleBar
 xlToLeft
 xlToolbar
 xlToolbarButton
 xlTop
 xlTop10Items
 xlTop10Percent
 xlTopToBottom
 xlToRight
 xlTransparent
 xlTriangle
 xlUp
 xlUpdateState
 xlUpdateSubscriber
 xlUpperCaseColumnLetter
 xlUpperCaseRowLetter
 xlUpward
 xlUserResolution
 xlVALU
 xlValue
 xlValues
 xlVar
 xlVarP
 xlVertical
 xlVeryHidden
 xlVisible
 xlWait
 xlWatchPane
 xlWeekday
 xlWeekdayNameChars
 xlWhole
 xlWide
 xlWindows
 xlWJ2WD1
 xlWK1
 xlWK1ALL
 xlWK1FMT
 xlWK3
 xlWK3FM3
 xlWKS
 xlWMF
 xlWorkbook
 xlWorkbookTab
 xlWorks2FarEast
 xlWorksheet
 xlWorksheet4
 xlWorksheetCell
 xlWorksheetShort
 xlWPG
 xlWQ1
 xlX
 xlXYScatter
 xlY
 xlYear
 xlYearCode
 xlYes
 xlZero
 Xor
 XPos
 XValues
 XYGroup
 XYGroups
 Y1
 Y2
 Year
 YPos
 Zoom
 ZOrder
 Checkbox
 CommandButton
 ComboBox
 Frame
 Image
 Label
 ListBox
 MultiPage
 OptionButton
 RefEdit
 ScrollBar
 SpinButton
 TabStrip
 TextBox
 ToggleButton
 UserForm
 Sheet
 Chart
 _setfocus
 _afterupdate
 _beforedragover
 _beforedroporpaste
 _beforeupdate
 _change
 _click
 _dblclick
 _enter
 _exit
 _error
 _keydown
 _keyup
 _keypress
 _mousedown
 _mouseup
 _mousemove
 _addcontrol
 _removecontrol
 _spinup
 _spindown
 _layout
 _dropbuttonclick
 _deactivate
 _initialize
 _queryclose
 _terminate
 _Value
 _Caption
 _Picture
 _Visible
 _List
 _Text
 jan
 feb
 mar
 apr
 may
 jun
 jul
 aug
 sep
 oct
 nov
 dec
 January
 February
 March
 April
 May
 June
 July
 August
 September
 October
 November
 December
 vb_globalnamespace
 AM
 PM
 collection
 Byte
 assert
 clearcomments
 shape
 addcomment
 vbBack
 vbCr
 vbCrLf
 vbFormFeed
 vbLf
 vbNewLine
 vbNullChar
 vbNullString
 vbObjectError
 vbTab
 vbVerticalTab
 vbBinaryCompare
 vbDatabaseCompare
 vbTextCompare
 Workbook_Activate
 Workbook_AddinInstall
 Workbook_AddinUninstall
 Workbook_AfterXmlExport
 Workbook_AfterXmlImport
 Workbook_BeforeClose
 Workbook_BeforePrint
 Workbook_BeforeSave
 Workbook_BeforeXmlExport
 Workbook_BeforeXmlImport
 Workbook_Deactivate
 Workbook_NewSheet
 Workbook_Open
 Workbook_PivotTableCloseConnection
 Workbook_PivotTableOpenConnection
 Workbook_SheetActivate
 Workbook_SheetBeforeDoubleClick
 Workbook_SheetBeforeRightClick
 Workbook_SheetCalculate
 Workbook_SheetChange
 Workbook_SheetDeactivate
 Workbook_SheetFollowHyperlink
 Workbook_SheetPivotTableUpdate
 Workbook_SheetSelectionChange
 Workbook_Sync
 Workbook_WindowActivate
 Workbook_WindowDeactivate
 Workbook_WindowResize
 Excel
 Office
 MsoSyncEventType
 xmlMap
 XlXmlImportResult
 XlXmlExportResult
 Hyperlink
 Worksheet_Activate
 Worksheet_BeforeDoubleClick
 Worksheet_BeforeRightClick
 Worksheet_Calculate
 Worksheet_Change
 Worksheet_Deactivate
 Worksheet_FollowHyperlink
 Worksheet_PivotTableUpdate
 Worksheet_SelectionChange
 Chart_Activate
 Chart_BeforeDoubleClick
 Chart_BeforeRightClick
 Chart_Calculate
 Chart_Deactivate
 Chart_DragOver
 Chart_DragPlot
 Chart_MouseDown
 Chart_MouseMove
 Chart_MouseUp
 Chart_Resize
 Chart_Select
 Chart_SeriesChange
 VBProject
 VBComponents
 VBComponent
 CodeModule
 Raise
 startLine
 CountOfLines
 InsertLines
 DeleteLines
 Comment
 go

Save Excel.Range to image file

Saving a Spreadsheet Range as a image file

The following code will save a spreadsheet range as a bitmap:

Option Explicit 
 
Private Type PicBmp 
    Size As Long 
    Type As Long 
        hBmp As Long 
        hPal As Long 
        Reserved As Long 
    End Type 
     
    Private Type Guid 
        Data1 As Long 
        Data2 As Integer 
        Data3 As Integer 
        Data4(0 To 7) As Byte 
    End Type 
     
    Private Const CF_BITMAP = 2 
    Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" _ 
    (PicDesc As PicBmp, RefIID As Guid, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 
    Private Declare Function GetClipboardData Lib "user32"  _ 
    (ByVal wFormat As Long) As Long 
    Private Declare Function CloseClipboard Lib "user32" () As Long 
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ 
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
     
    Sub SaveImage(rng As Range, strFileName As String) 
        Dim hwnd As Long 
        Dim hPtr As Long 
        hwnd = FindWindow("xlmain", Application.Caption) 
        rng.CopyPicture xlScreen, xlBitmap 
        OpenClipboard hwnd 
        hPtr = GetClipboardData(CF_BITMAP) 
        SavePicture CreateBitmapPicture(hPtr), strFileName 
        CloseClipboard 
    End Sub 
     
     
    Function CreateBitmapPicture(ByVal hBmp As Long) As IPicture 
        Dim lngR As Long, Pic As PicBmp, IPic As IPicture, IID_IDispatch As Guid 
         
        With IID_IDispatch 
            .Data1 = &H20400; 
            .Data4(0) = &HC0; 
            .Data4(7) = &H46; 
        End With 
         
        With Pic 
            .Size = Len(Pic) 
            .Type = 1 
            .hBmp = hBmp 
        End With 
         
        lngR = OleCreatePictureIndirect(Pic, IID_IDispatch, 1, IPic) 
        Set CreateBitmapPicture = IPic 
    End Function 
     

To use it pass the range you want to display and a filename to use e.g.

SaveImage Sheet1.Range("A1:A8"), "C:Documents and settingsmarkdesktoptest.bmp" 

Incidentally if you used VB6 and compiled to a COM addin you would only need:

SavePicture Clipboard.GetData(vbCFBitmap), "C:Documents and settingsmarkdesktoptest2.bmp"
If we want to save as JPG file
Sub SelectedRangeToImage()
    Dim tmpChart As Chart
    Dim n As Long
    Dim shCount As Long
    Dim sht As Worksheet
    Dim sh As Shape
    Dim fileSaveName As Variant
    Dim pic As Variant
    'Create temporary chart as canvas
    Set sht = Selection.Worksheet
    Selection.Copy
    sht.Pictures.Paste.Select
    Set sh = sht.Shapes(sht.Shapes.Count)
    Set tmpChart = Charts.Add
    tmpChart.ChartArea.Clear
    tmpChart.Name = "PicChart" & (Rnd() * 10000)
    Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
    tmpChart.ChartArea.Width = sh.Width
    tmpChart.ChartArea.Height = sh.Height
    tmpChart.Parent.Border.LineStyle = 0
    'Paste range as image to chart
    sh.Copy
    tmpChart.ChartArea.Select
    tmpChart.Paste
    'Save chart image to file
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="Image (*.jpg), *.jpg")
    If fileSaveName <> False Then
      tmpChart.Export Filename:=fileSaveName, FilterName:="jpg"
    End If
    'Clean up
    sht.Cells(1, 1).Activate
    sht.ChartObjects(sht.ChartObjects.Count).Delete
    sh.Delete
End Sub

VBA UserForms

Userforms

written by Helen Toomik – Last updated Oct 2004

Contents:

Caveats… Please note that this is not “Excel VBA 101” – I assume you are already familiar with VBA and in particular Excel. I developed this primarily for Excel 2000 and 2002/XP, and some parts are not applicable to Excel 97.

Thanks… to everybody who helped with proofreading, debugging and other suggestions.

Introduction – What is a userform?

A userform is a custom dialogue box that you can use when MsgBox and InputBox are just not enough. Userforms can be used for displaying as well as inputting data. You could even build an entire user interface using userforms, and never let the user touch the spreadsheet itself.

Most of the things that you can see in Windows dialogue boxes can be replicated in userforms – commandbuttons, option buttons, checkboxes, textboxes, comboboxes – plus a whole lot of other, more rarely used controls like graphs, calendars, etc.

This tutorial is an introduction to basic userform concepts, as well as commonly used userform techniques. I’ll explain some of the more general ideas “verbally”, but I’m also including a sample file where you can see how it all works in practice, so the tutorial text itself won’t have many code samples.

I’ve put this together mainly for Excel, but the principles (and most of the code) would also apply to other MS Office applications. The main difference would be in the parts that transfer data between userform and your document.

Getting started

  1. Create a form. Open the Visual Basic Editor (Tools…Macros… or Alt+F11). Add a userform by choosing Insert…Userform from the menu bar, or right-click in the project explorer and do the same thing from there.
  2. Add controls. When you create a userform, a new floating toolbox window will appear, called the Controls Toolbox. (If it doesn’t, you can find it in the View menu.) This shows the most common types of controls. Click on one that you like, and simply draw the control in your userform.
  3. More controls. Many more controls are available but not immediately visible. Right-click on the Controls Toolbox and choose Additional Controls. You can add calendars, spreadsheets, graphs, etc.
  4. Add event procedures. Events allow the form and its controls to respond to what the user does. Event procedures sit in the code module associated with the userform (its class module). You can access the code by double-clicking on the form/control, or right-clicking on the form/control and choosing “View Code”, or right-clicking on its icon in the project explorer. See the events below.
  5. Show the form. To show the userform, open the form’s class module and hit F5, like you’d do for running a macro, or click the Play button on the toolbar. Note that pressing F5 in a class module does not run the sub that your cursor is in, but the userform itself.
  6. Debug. As with macros, F8 allows you to step through the code line by line.

Forms toolbar controls vs. Control toolbox controls

If you have used controls in Excel, you may have used controls from the Forms toolbar, rather than the Controls toolbox. The two sometimes look very similar, but work quite differently behind the scenes. Ozgrid has a fairly good overview of the differences.

Finding out more

If you want to know more after reading this tutorial, there are three easy ways:

  1. The example file I’ve included. It has examples for the commands I’ve mentioned here, in particular all the FAQs, and some more.
  2. Help. The VBA help files for userform-related commands are fairly good. Take a look at their examples, too.
  3. Object browser (F2). Shows you what properties and methods are available for a certain object. Pressing F1 here shows the Help section for that object/method/property.

Properties

Forms and controls have properties (like size, location, etc). You can change these while setting up the form, and most of them can also be changed at runtime (via code). The values you choose while setting up the form will be default values, and runtime changes will only be valid while the form is loaded (see Life cycle of a form below).

The two most important properties of objects are .Name and .Value:

  • Name is useful for referring to the controls. You can refer to controls by their index number, but that yields code that is difficult to understand and debug, so using names is generally preferable.
  • Value is what you use to actually input or output data. Value means slightly different things for different controls – for optionbuttons and checkboxes it is True/False, for textboxes the text they contain, etc. Value can be used both for input and for output.

For example:

txtFirstName.Text = Range("A1").Value 
Range("B1").Value = optVegetarian.Value 
 

A good habit

Give your forms and controls descriptive and systematic names. You’ll often find it helpful to give each type of control a prefix: frm for forms (frmMain and frmDetails), opt for option buttons etc. This makes your code easier to read, and also allows you to use some techniques that would otherwise be more complicated (see Looping through controls below).

http://www.xoc.net/standards/rvbanc.asp has a lot more detail on naming variables and objects.

Referring to controls and forms, and Me

When referring to the controls of a userform from code in its class module, you can refer to them by name: txtFirstName.Value = “John”. For controls in other userforms, the name of the control is preceded by the name of the form (e.g. frmEmployees.lstDepartments).

The same approach is valid for public variables declared in the form’s class module – these behave essentially as properties of the form (e.g. frmInputs.AllDone). Note that you cannot declare a variable as Global in the class module of a userform.

Me is a shortcut for referring to the userform from its class module (e.g. Me.Height). For example, referring to TextBox1 in UserForm1:

 
Me.TextBox1.Text ="Example" 
 'in the userform's class module, or:
UserForm1.TextBox1.Text ="Example" 
 'in a different module

The life cycle of a userform – showing and closing, etc.

To show your form manually, press F5 in the form window or in its class module. If there is a Userform_Initialize procedure in the module (see Events), that will run first.

To show and hide your form via code (for example, you might want a commandbutton on frmIntro to hide frmIntro and show frmMain instead), use the .Show and .Hide methods.

Advanced topic: Load and unload

Behind the scenes, there’s more than just showing and hiding going on. Before a form is shown, it needs to be loaded into memory. If you show a form that hasn’t been loaded, it will load automatically.

Indeed, any reference to the form, or to a variable or control or property of the form, will force it to load, and therefore trigger the Initialize event.

If you want to initialise the form without showing it, you can load it like this:

Load frmMain 

After hiding a form, it will still be loaded. If you show it again, the Initialize procedure will not run again. (The Activate procedure will, however.) To clear the form from memory, you have to unload it. When the user closes a form with the close button, the form is automatically unloaded.

So the sequence is: Load – Show – … – Hide – Unload.

Unload clears all variables in the form’s module – it is comparable to stopping a procedure. Any values that the user has entered will be lost, and controls will revert to their default values that you have entered using the Properties window. If you want to save their values, you need to do so before unloading the form.

Modal vs modeless

Forms can be shown in one of two “modes” – modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible – like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Forms can be shown in one of two “modes” – modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible – like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Code execution will also continue in the background while a modeless form is shown. You can make your code wait until the form has been closed using a loop that checks whether the form has been closed:

 
Do Until frmOther.Visible = False 
    DoEvents 
Loop 

The default setting is modal (NB: the opposite of the case in VB6). Once a form is shown as modal, you cannot change it to modeless – you have to hide the form and then show it again, specifying that you want it modeless.

Modeless forms are only available from Excel2000 onwards.

Events

This is where things get interactive. Events allow the form and its controls to respond to what the user does. You are probably familiar with events from Excel VBA – Workbook_Open, Worksheet_Change etc. While you can do a lot in Excel without events, forms are pretty useless without them.

  • Common events for forms include Initialize, Activate, QueryClose, and Click.
  • Common events for controls include AfterUpdate, Change, Click, Enter and Exit.

To insert an event procedure, right-click on the object and choose “View code”. An event procedure is created automatically for that control’s standard event. To create a procedure for a different event, choose the event you want from the drop-down menu at the top right of the VBE window. Alternatively, go to the form’s class module and choose the object from the left drop-down and the event from the right drop-down.

Userform_Initialize

The most important event for forms is the Initialize event. Initialize is pretty much the first thing that happens to a form – the Initialize event is triggered as soon as the form starts loading, either because it is called by code or by the user (by hitting F5 or F8).

This is where you would initialise variables and controls. For example, you can update textboxes with latest values from the spreadsheet, change the default value of a textbox to today’s date, etc.

QueryClose and Terminate

The equivalent “end of life” events for a form are two: QueryClose and Terminate. QueryClose occurs first, and gives you the chance to cancel it (and not close the form); Terminate is final and not possible to cancel.

So the sequence of events is: Initialize – …- QueryClose – Terminate.

Userform_Activate

If you hide a form without unloading it, and then show it again, Initialize won’t run again. Instead, the Activate event occurs. Activate is triggered every time the form gets focus. This happens each time the form is shown. If you have several forms visible at the same time, the Activate event is also triggered every time you switch between forms.

Events with parameters

As with Excel events, some events have parameters, which give you more information about how and why the event was triggered – the UserForm_KeyDown event tells you which key was pressed, etc. When you create an event procedure for one of those events using the drop-down menus in VBE, the procedure is automatically set up to properly capture all the parameters.

Some parameters are read-only, whereas others are read/write. For example, the Userform_QueryClose event, which occurs before a form is closed, has a Cancel parameter. Setting Cancel = True within the procedure cancels the event, and the form is not closed.

FAQ: How can I pass data between userforms?

There are two main approaches to passing data between forms. Either you pass data directly from form to form (which means that the two forms are loaded in memory simultaneously) or you store the data somewhere and then access it later from the other form.

Passing from form to form can be done from either the “source” form or the “target” form. Remember that this has to be done before the source form is unloaded. Also remember that when referring to controls in another form, you need to specify the form name:

txtName.Value = frmTheOtherForm.txtName.Value 

Passing data directly from form to form will trigger the Initialize event of the other form (unless that form is already visible) since you are referencing its controls’ properties. If the Initialize event procedure in its turn includes code that shows the form, or calls other subs, this can easily trigger a lot of code, and get difficult to debug, so I’d use this approach with relatively simple forms only.

In comparison, storing the data in a variable allows more flexibility and control. The data could be stored either in a public variable, in a worksheet cell (so that it can be saved when the file is closed) or in a name in the worksheet.

The sample file has examples of both approaches.

FAQ: How can I do … with all the … in my form?

For example, how can I add up the values of all textboxes? uncheck all checkboxes? etc.

You can loop through all the controls in your form, pick out those that are of the right type, and apply the relevant code to those.

As with most things, there are several ways of doing this… using TypeName, TypeOf or control names.

TypeName()

TypeName returns a string – “TextBox” for textboxes, “CommandButton” for commandbuttons etc. The typename of a control is generally the same as the control’s default name & caption, but without the number. For example, when you add a new textbox to your form, it is called TextBox1 by default.

TypeName is case-sensitive, and if you make a typo, you get no error message – the code simply won’t work properly.

TypeOf

TypeOf is a fancier way of doing the same thing. Instead of a string, it returns the object type directly, referencing the object library. You can find the library and type of an object through the Object Browser. For example, the type of a textbox is MSForms.Textbox.

The main practical advantage of TypeOf is that it makes your code easier to debug. Typos get caught when you try to compile the module, and TypeOf supports Intellisense – start typing If TypeOf ctl Is, and you’ll get a dropdown list with all the available choices.

“If TypeOf … Is … Then” is considered a special case of If statements, and is covered in VBA help under If.

Control name

If you have named your controls consistently, you can use the Name property to identify them, with the help of the Left() function. This “low-tech” approach is somewhat more flexible – you could use this to identify a subset of all textboxes. If, for example, your form has 10 textboxes (txtTeamName, txtTeamNumber, txtMember1, txtMember2, …, txtMember10) you could use this approach to identify and empty all textboxes whose name starts with txtMember.

Examples

The three alternative If statements in this example would all achieve more or less the same thing (assuming the names of your textboxes all start with “txt”):

 
Dim ctl As Control 
For Each ctl In Me.Controls 
    If TypeOf ctl Is MSForms.Textbox Then 
         'or
         'If TypeName(ctl) ="TextBox" Then
         'or
         'If Left(ctl.Name, 3) ="txt" Then
         'do something with the textbox
        ctl.Text ="Hello" 
    End If 
Next ctl 
 

There’s an example in the sample file above, too.

FAQ: How can I get data into my listbox?

RowSource

You can link a listbox directly to a range in your worksheet. This is similar to having a listbox in the worksheet. Any changes you make to that range will immediately be reflected in the listbox. The link is read-only: you cannot change items in the listbox to change the worksheet.

Note that the parameter for RowSource is a string and not a range:

lstNames.RowSource = Range("Names").Address 

List / Column

You can set list items using the List and Column properties. The two are essentially the same, except for their orientation: the syntax is List(row, column) and Column(column, row). Both can be used to copy an entire array to the listbox, or to set individual items.

lstNames.List = Range("Names").Value 

AddItem

AddItem adds a single row to the listbox, and can put a value in the first column of the new row. For multicolumn listboxes, you’ll have to use List or Column to put in values in the rest of the columns.

With lstNames 
    .Additem 
    .List(0, 0) ="John" 
    .List(0, 1) ="Smith" 
End With 

You’ll find examples of all these three methods in the sample file.

FAQ: How can I create a progress bar for my loop?

One easy way to create a progress bar is to create a form with two labels (call them lblBackground and lblProgressBar for example). Make lblBackground as wide as you want the progress bar to be, and set the width of lblProgressBar equal to 0 to start with. Also make sure that lblProgressBar is in front of lblBackground. Then increase the width of lblBar as you run the loop.

Me.lblProgressBar.Width = p * Me.lblBackground.Width 
 'where p is the proportion of the loop that's done, for example 0.75

Again, there’s an example in the sample file.

VBA Code Compilers

First point to state is the VBA SDK 6.x site, on how to upgrade Visual Basic 6
to Visual Basic 6.5 with VBA’s SDK + VB.NET. Don’t know where it came from, but looks really insteresting. You can get your own VBA system on your application.
Then, there is this book (avaliable at the archive.org library), about hardcore of Visual Basic. It really exposes harcore for VB, like raw COM creation… and other weird things like that. I get to this following this post, and before that I was pursuing the Nativecode thing that is exposed there, from the Unviewable application. Also it seems that this also shares some kind of operation.
Orlando has always get me wondering how he get’s Excel to EXE compiled, and some other nice tools.
Want to get the most on Office VBA?, then borrow this book, avaliable via the archive.org library.
Following is a recopilation of what comercial software can do right now in Excel workbook protection tools, focused on the base features of the products which affect the security and misbehavior of protected solutions.
There are other decompilers for VBA:
  • To simply unlock the xls/xlsm file, just go here.
  • https://github.com/bontchev/pcodedmp

For Visual Basic programs compiled to .NET (e.g. with Visual Studio .NET 2003 or later), see DotNetDecompilers.

  • https://www.decalage.info/vba_tools and http://decalage.info/vba_emulation
  • JosephCo wrote a decompiler called Exdec.
  • VBDis 3/4/5 by DoDi. Version 3 (for VB version 3) was the most successful, because it has the most information in the “executable” (including comments!). VBDis 3 is available from this page.
  • Decompiler Technologies (formerly Visual Basic Right Back) is a decompilation service.
  • http://www.vb-decompiler.com: VB Decompiler Forum. Even includes some specifics on building a VB5/VB6 decompiler.
  • http://www.vb-decompiler.org: decompiles pcode, “high level assembler” for native code. Proprietary software, but free (as in beer) “Lite” version is available.
  • VBDE by Iorior dumps GUI information about VB executables; some decompilation ability
  • WKTVBDebugger VB debugger by WTK
  • http://www.decompiler-vb.netVBReFormer is a decompiler for native Visual Basic applications. It shows design code data (forms, controls, etc.), allows the modification of design properties directly on the binary, can disassemble native code, and can decompile native code (as much as that is possible).
  • Another one here, http://www.vbdecompiler.co.uk/
  • VBEditor by Hexman recovers Gui Information
  • R.A.C.E. by Sarge recovers Gui Information, and P-Code opcodes.
  • P32Dasm by Darker is a P-Code decompiler which decompiles to P-Code tokens.
  • Semi VB Decompiler by vbgamer45 recovers gui information, and P-Code tokens. For VB 5 and 6. Does not recompile native code.
  • VB Parser – Recovers P-Code tokens.
  • VBRezQ recovers the project file and forms.
  • http://vbdebug.cjb.net for a Visual Basic debugger (p-code programs only) by Mr Silver and Mr Snow.
  • VB Shrink by Cute-Bits is a tool that strips non essential info from the executable, to make it more difficult to decompile
  • VB EXE Obfuscator by Jory
  • Decompiler Defeater (see DeFeater)
  • Visual Basic MAK Compiler Pro (aka make_mak) by Christian Germelmann has a check box decompDefeat; the author claims “your apps will be immune against ‘reverse engeneering'”.
  • See also http://www.riis.com/archives/19980503.html on VB decompilation. (Dead link check out archive.org if you want to view that article.)
  • See also Master the Black Art of the VB Interpreter, Ash Rofail. In Visual Basic Programmer’s Journal, Dec 1996, pp 58-64.
  • The paper Visual Basic Reversed – A decompiling approach by Andrea Geddon has lots of details about the internals of VB5 and VB6 native (machine code) compiled programs. This is not a peer reviewed academic paper, and is written from a cracking point of view.
  • decompiler.com (bad link!) had some good links on this page, now only available from archive.orghttp://www.decompiler.com/viewtopic.php?t=2 (from the Software Reviews forum, Software Listings topic). There was also other good information in forums and the like; this site was heavily Visual Basic oriented. Several tools were available for download. Offline since late 2003.
From: #13 Re: Creating an .exe

Digiwise’s Post:

WhiteHatXL’s analysis:

DataSafeXL:
PROS: A newbie in Excel cannot reach the content of the protected workbook.
CONS: The product is programmed in vba. The tool relies on standard MS Excel protection methods like workbook/worksheet protection and vba project password plus the vba code obfuscating. You do not need to be C programmer to pass over such protection.
“For maximum VBA security, use the Microsoft Excel 2007-2010 format (“.xlsm”) and add a VBA password to your file which contains random alphanumeric characters and is at least 15 characters long.”
This advice from their help file just kills me!
Looks like the guy who wrote this product has been learning vba programming by the way.
SUMMARY: Junk. The product is on a student project level.Spreadsheet Sentry:
PROS: The tool is programmed in C++, uses strong encryption to protect formulas in cells. In protected solution formulas are encrypted, stored in cells and not readable.
CONS: Takes WAY too much time to encode a workbook even for middle size model.
Protected solutions work veeeery sloooow. Calculation time is wasted for encryption/decryption every formula in calculation chain.
The tool doesn’t protect VBA code.
SUMMARY: Protection works. The tool appropriates for small workbooks without VBA macros.LockXLS:
This is the most controversial product in the review. After the first glance I wanted to give “the perfect” mark, but see what I have finally found ….
PROS: The tool is programmed in C++. In protected solution everything works like in original workbook. Average user cannot see formulas in cells and vba code in protected workbook. Protects xla files. Lots of features.
CONS: The product uses unmodified, original workbooks in protected solutions and relies on hackers’ methods to take customer away from seeing the content of the workbook (Hooks Excel windows and blocks Excel’s COM objects methods calls). The tool is trying to stub all Excel’s security breaches, which is impossible with an unmodified, original workbook.
When a protected solution has been started on an end customer computer, the spreadsheet becomes as unprotected as newborn baby.
5 lines of C code extract the original workbook from the protected solution, doesn’t matter whether it’s xls or exe file.
To prove the vulnerability of LockXLS solutions, just e-mail a protected solution to WhiteHatXL at yahoo dot com , the original workbook will be returned.
SUMMARY: Fake. This product is not for a workbook protection.Secure Calc (former ExcelShield):
These guys have ignored my requests for a trial version, so I have evaluated the product by the simple example from their site—It may work different for complex spreadsheets.
PROS: The tool uses strong encryption for formulas, removes formulas from cells, has calculation engine to evaluate protected formulas.
CONS: Engine is written in .NET environment in pure managed code, which is not good for a protection tool. Source code may be reached by Reflector and reverse engineered. No VBA Protection.
The calc engine is not included into protected solution and should be preinstalled on customer computer separately. Each time, after typing new data into cell, the customer has to click the ‘Calculate’ button, which is available through the main Excel menu. This is very annoying.
SUMMARY: Protection works. It looks good enough, if you do not need to protect vba code.xCell Compiler:
PROS: The tool is programmed in C++, compiles formulas into binary code and removes them from cells, protects vba code. Has calculation engine to evaluate protected formulas. Has a lot of features.
CONS: The tool has limitations declared on their web site. A protected solution doesn’t support adding/removing columns/rows/sheets. Sorting for ranges with formulas doesn’t work.
SUMMARY: Protection works. Good, if you do not use features mentioned in limitations.

Excel Translator:
I was unable to get the neither trial version nor example of protected workbook. Very “strong” protection!

Converter XL to Exe:
PROS: Free, Free, Free!!!
CONS: This tool is not for a workbook protection.
SUMMARY: Toy.

Other interesting links to consider:

 

Call OCX COM sample

dilettante (MIS)
Start with a small script:

Jabber.vbs

CODE

Option Explicit

Function Jabber()
    Const ForReading = 1
    Dim FSO
    Dim strIn
    Dim tsIn
    Dim strOut
    Dim tsOut
    Dim strLine

    Set FSO = CreateObject("Scripting.FileSystemObject")
    strIn = InputBox("File to Jabber")
    If StrIn = "" Then
    Jabber = True
        Exit Function
    End If
    strOut = InputBox("New jabbered file")
    If strOut = "" Then
        Jabber = True
        Exit Function
    End If
    Set tsIn = FSO.OpenTextFile(strIn, ForReading)
    Set tsOut = FSO.CreateTextFile(strOut, True)
    Do Until tsIn.AtEndOfStream
        strLine = tsIn.ReadLine
        strLine = Replace(strLine, "a", vbFormFeed)
        strLine = Replace(strLine, "e", "a")
        strLine = Replace(strLine, "i", "e")
        strLine = Replace(strLine, "o", "i")
        strLine = Replace(strLine, "u", "o")
        strLine = Replace(strLine, vbFormFeed, "u")
        tsOut.WriteLine strLine
    Loop
    tsOut.Close
    Set tsOut = Nothing
    tsIn.Close
    Set tsIn = Nothing
    Set FSO = Nothing
    Jabber = False 'No cancels.
End Function

WScript.Echo "Start!"
If Jabber() Then
    WScript.Echo "Canceled!"
Else
    WScript.Echo "Done!"
End If

Then I started up VB5CCE and created an ActiveX control project.  I named the project Jabber and the UserControl Compiled.

Next I copied in the VBScript code for the function Jabber() into the UserControl’s code window.  Because VB5 did not have the Replace() function yet, I faced a decision.

One way would be to write my own Replace() using the string functions already in VB5.  The other is to write a function in the calling script that invokes VBScript’s Replace, and pass the calling script to the VB5 version of Jabber() as an Object, then invoke the function from the script as a method.

I chose the latter, and I called my Replace() wrapper function R().

Here is the VB5CCE code:

Compiled.ctl

CODE

Option Explicit

Public Function Jabber(ByVal Caller As Object)
    Const ForReading = 1
    Dim FSO
    Dim strIn
    Dim tsIn
    Dim strOut
    Dim tsOut
    Dim strLine

    Set FSO = CreateObject("Scripting.FileSystemObject")
    strIn = InputBox("File to Jabber")
    If strIn = "" Then
        Jabber = True
        Exit Function
    End If
    strOut = InputBox("New jabbered file")
    If strOut = "" Then
        Jabber = True
        Exit Function
    End If
    Set tsIn = FSO.OpenTextFile(strIn, ForReading)
    Set tsOut = FSO.CreateTextFile(strOut, True)
    Do Until tsIn.AtEndOfStream
        strLine = tsIn.ReadLine
        strLine = Caller.R(strLine, "a", vbFormFeed)
        strLine = Caller.R(strLine, "e", "a")
        strLine = Caller.R(strLine, "i", "e")
        strLine = Caller.R(strLine, "o", "i")
        strLine = Caller.R(strLine, "u", "o")
        strLine = Caller.R(strLine, vbFormFeed, "u")
        tsOut.WriteLine strLine
    Loop
    tsOut.Close
    Set tsOut = Nothing
    tsIn.Close
    Set tsIn = Nothing
    Set FSO = Nothing
    Jabber = False 'No cancels.
End Function

Not much has changed here from the original VBScript version.  I saved the project and compiled it, which registers the new Jabber.ocx on that machine in passing.

Then I slightly rewrote the calling script:

Jabber Compiled.vbs

CODE

Option Explicit

Dim Jabber

Function R(ByVal Expression, ByVal Find, ByVal ReplaceWith)
    R = Replace(Expression, Find, ReplaceWith)
End Function

WScript.Echo "Start!"

Set Jabber = CreateObject("Jabber.Compiled")
If Jabber.Jabber(Me) Then
    WScript.Echo "Canceled!"
Else
    WScript.Echo "Done!"
End If
Set Jabber = Nothing

The only deployment issue is making sure that the VB5 runtime is installed on target machines, and that Jabber.ocx gets copied and registered, such as by running:

regsrv32 {path}Jabber.ocx

This can be done from a command prompt or Start|Run dialog.  One could also create an OCX installer package, etc.

I think this is as close as you can get “for free” to compiling VBScript.  One might replace the calling script by using some free compiler for a language that supports ActiveX objects.  Then you’d have a 100% solution.

You can also easily use things like the Common Dialog control in VB5CCE to provide a nicer file dialog than you get by using Inputbox().  Adding a reference to the Microsoft Scripting Runtime lets you use early binding and defines the FSO constants, and for that matter you can use typed variables for better performance.

Create a COM Add-in

Introduction

COM addins were introduced in Office 2000. The alternative to a COM Addin is a standard addin such as an xla for Excel or Template for word. In essence COM addins are compiled dll’s (dynamic link libraries) that use the COM interface to interact with Office. The fact that they are compiled means that they:

  • are more secure
  • run faster
  • are able to use controls and structures unavailable in VBA

The drawback though is that they a bit harder to:

  • create
  • edit
  • install

There are basically few options to compile directly from VBA code:

  • Visual Basic (5 or 6), as Add-in (DLL or OCX)
  • Visual Basic (5 or 6), as a compiled EXE from XLS file
  • Visual Basic 5 Controls Creation Edition, as ActiveX Add-in (OCX)

All probably will require a little code edition, but should be minor editions.

Creating a COM Addin in VB6

[Adapted from Mark Rowlinson]

Open VB6 and create a new project. In the list of available projects select the type Addin.

When the new project opens you will have a Form called frmAddin and a Designer object called Connect. Remove the form from the project as it is a sample form and we don’t need it.

The next step is to set the addin up using the designer object. Double click on it and you will be presented with a Designer Form with several fields.

  • Addin Display Name – This is the name of your addin.
  • Addin Description – Give your addin a description so users know what it does
  • Application – The application you are creating the Addin for. For the purposes of this article we will create an Addin for Excel so select Excel.
  • Application Version – The version of the application you are creating it for.
  • Initial Load Behaviour – Whether the addin loads automatically when the application is started up or not. For this addin set this field to Startup. The addin will then load when Excel starts.

Setting References and Other Project properties

The next thing to do is to set the required references. By default VB adds a reference to the VB6 extensibility library i.e. it assumes we are creating an addin for VB6. We wish to remove this and replace it with the reference to Excel.

To do this goto Project/References, uncheck the VB Extensibility Library and then find the reference to the Microsoft Excel Object Model. Check the box next to it and click OK.

Next goto to Project/Properties (bottom of the Project menu) and set the name and description of the project. (The difference here between the name set in the designer is that as we are creating a standard COM/ActiveX dll it could be added to a reference to other projects as well as being used as an addin. The Description here is the one that will appear in the references list.) This would then be a good time to save the project.

More on the Designer

Right click on the designer object in the project explorer and select ‘View Code’ or click the ‘View Code’ button at the top of the project explorer.

You will notice there is already alot of code in here that was generated for the sample Addin. Most of this code can be removed from the module. The only 2 procedures you need to leave behind are:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ 
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 

And

Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 

These 2 procedures relate to the starting up and closing down of the addin. The starting up procedure passes a reference to the application that loaded the addin. In our case this will be an Excel application object. We can store this and use it for manipulating Excel from within the addin. Add a standard code module to the project and add the following line of code:

Public xlApp As Excel.Application

Now go back to the OnConnection procedure. Remove the existing code and add:

Set xlApp=Application

This procedure is also the place to initialise any objects and create any menus/toolbars etc. Therefore add a procedure CreateToolbarButtons to your code module and call it from you OnConnection procedure. The completed procedure should now look something like the following:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ 
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 
     
     'store the application
    Set xlApp=Application 
     
     'set up our menus
    CreateToolbarButtons 
     
End Sub

The OnDisconnection procedure is called when the addin is unloaded. It should therefore be used to remove any toolbars and destroy any objects etc. The procedure should therefore look something like:

Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 
     
     'destroy xl object reference
    Set xlApp=Nothing 
     
     'removemenus
    RemoveToolbarButtons 
     
End Sub

You should also now add a procedure to your code module called RemoveToolbarButtons.

This is the basic structure of a COM addin completed – not too hard really!

Handling Toolbar Events

In Excel VBA when you create a commandbar button you can set the OnAction property to the name of the procedure you wish to run when the button is clicked. With COM addins, although you can still do this, when the button is clicked the OnAction procedure will not be found if it resides within the dll. OnAction will only look in standard VBA code modules.

This means that to handle the events you have to use a class module and declare a reference to the object ‘WithEvents’. You can then access the click event of button (other controls have different events available). Therefore add a class module to your project and name it cbEvents. Now add the following line:

Public WithEvents cbBtn As CommandBarButton 

Now if you can select cbBtn in the top left dropdown and select the click event in the top right dropdown. The following code should result:

Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
End Sub 

Notice you are passed a reference to the control that caused the procedure to be called. We shall use this class module to handle the click event of all the buttons we create so we need to check which control was passed to determine the procedure to call. The easiest way to do this is to check the OnAction property. We can set this when we create the buttons to the name of the procedure we wish to call and use a select case statement to call that procedure e.g.

Select Case Ctrl.OnAction 
Case "Sub1" 
    Sub1 
Case "Sub2" 
    Sub2 
End Select 

Finally, we need to set the CancelDefault property to true within this event to cancel Excel looking for the sub specified in the OnAction property which as discussed earlier it won’t find!

The completed class module should therefore look something like:

 
Option Explicit 
 
Public WithEvents cbBtn As CommandBarButton 
 
Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
     
     'supress errors
    On Error Resume Next 
     
     'check onaction property
    Select Case Ctrl.OnAction 
    Case "Sub1" 
        Sub1 
    Case "Sub2" 
        Sub2 
    End Select 
     
     'cancel Excel looking for the sub specified in the OnAction property
    CancelDefault = True 
     
End Sub 

You should now create your toolbar buttons in the usual way in your CreateToolbarButtons procedure remembering to use xlApp instead of Application to reference the commandbars collection. The only difference is that when you have created the button you should assign it to an instance of the class we just created. The easiest way to do this is to declare an instance of the class and of a collection at the top of the module i.e.

Dim ButtonEvent As cbEvents 
Dim ButtonEvents As Collection 

Now at the start of your CreateToolbarButtons procedure create a new collection:

Set ButtonEvents=New Collection 

Finally after each button you create create a new instance of cbEvents class and assign to ButtonEvent, assign the created button to the cBtn of the ButtonEvent object then add the ButtonEvent object to the collection. In code this becomes (assuming your newly created button is in an object btNew):

Set ButtonEvent=New cbEvents 
Set ButtonEvent.cBtn=btNew 
ButtonEvents.Add ButtonEvent 

An example procedure is as follows:

Public Sub CreateToolbarButtons() 
     
     'to make sure the buttons we are about to add aren't added twice
     'try to remove them first
    RemoveToolbarButtons 
     
     'declare some variables
    Dim cbBar As Office.CommandBar 
    Dim btNew As Office.CommandBarButton 
     
     'create a new collection
    Set ButtonEvents=New Collection 
     
     'find the worksheet menu bar in excel (this is the one
     'with the file, edit, view etc. commands)
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
     'add a new button to the Tools menu
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) 
    With btNew 
        .OnAction = "Sub1" 
         'set a unique tag to make our custom controls easy
         'to find later to delete
        .Tag = "COMAddinTest" 
         'set the tooltip text
        .ToolTipText = "Calls Sub1" 
         'set the caption that appears in the menu
        .Caption = "Sub1" 
    End With 
     
     'get a new instance of our cbevents class
    Set ButtonEvent = New cbEvents 
     'now assign the button we created to it
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
     'now add another button
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) 
    With btNew 
        .OnAction = "Sub2" 
         'set a unique tag to make our custom controls easy
         'to find later to delete
        .Tag = "COMAddinTest" 
         'set the tooltip text
        .ToolTipText = "Calls Sub2" 
         'set the caption that appears in the menu
        .Caption = "Sub2" 
    End With 
     
     'get a new instance of our cbevents class
    Set ButtonEvent = New cbEvents 
     'now assign the button we created to it
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
     
End Sub 

The RemoveToolbarButtons sub would then look something like:

Public Sub RemoveToolbarButtons() 
     
    Dim cbBar As CommandBar 
    Dim cbCtr As CommandBarControl 
     
     'supress errors - this is important here as they may not have been created
     'yet or may have been alreday deleted
    On Error Resume Next 
     'need to remove button from the command bar
     'first find the commandbar
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
     'not find the control using the tag we set when creating
    Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
    While Not cbCtr Is Nothing 
         'now delete it
        cbCtr.Delete 
        Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
         
    Wend 
     'remove event handlers from memory
    Set ButtonEvents = Nothing 
    Set ButtonEvent=Nothing 
End Sub 

 

Installing an Excel COM Addin

Registering the Dll

Dll’s require registering with Windows in order to run. To do this you need to run regsvr32.exe passing it the filename of the dll. An easy way to do this is to download the script on the downloads page. Once you have run the script you’ll be able to Register and Unregister dll’s easily using the right click context menu.

NB: When you create the DLL in VB6 it will auto register it on the PC it was created on.

NB2: Also note that to remove or delete a dll you should unregister it first. You can again do this easily if you have ran my script but if not you need to run regsvr32.exe passing the /u switch.

Installing the Addin in Excel

As we set the initial load behaviour to ‘Startup’ in the designer the Addin should load automatically on reloading Excel. If startup isn’t selected then you may need to load it manually. To do this you need to select the ‘Com Addins’ menu option in Excel.

This is often not available by default and needs to be added by going to Tools/Customise and dragging it on to the menu bar. On selecting the ‘Com Addins’ button the available Com addins will be listed. The Addin should be in the list with a checkbox next to it. This will be checked if it is loaded and unchecked if unloaded. You can also remove addins and browse for other addins not in the list.

 

Create ActiveX controls using the VB 5 CCE

[adapted from http://www.borncity.com/web/WSHBazaar1/WSHVB5CCE.htm]

Visual Basic 5 Controls Creation Edition (VB 5 CCE)  (Note: Microsoft has removed VB 5 CCE in 2004, but could be found on Microsoft Press book about Access Basic, on the companion CD), is basically a stripped-down version of the full Visual Basic 5.0 application; i.e., it does not include the functionality to create DLL and EXE projects and it only has limited documentation.

Visual Basic Runtime msvbvm50.exe (1.3m)
Visual Basic 5.0 Control Creation Edition vb5ccein.exe (7.1m)
Visual Basic 5.0 CCE Help Files ccehelp.exe (2.5m)

Hereafter it explains the basic techniques to create an ActiveX control using VB 5 CCE. In addition, this page comes with a ready-to-use sample that you could use as a foundation to develope your own controls.

The steps to create an ActiveX control

Install VB 5 CCE in Windows, and fire it.

  1. After launching the program, a “New project” dialog asks you what to do. Select the ActiveX Control icon on the New property page and close the dialog window using the “OK” button. A new project will be created. This project contains one UserControl.
  2. Click in the project explorer window on the project name (“Project1(project1)”). Use the (Name) entry of the property page to re-name your project (from “project1” to “WSHTest” for instance).
  3. Click on the Entry “UserControl(UserControl)”. Use the (Name) entry in the property page to re-name your control from “usercontrol1” to “Test1” for instance.
  4. Save the project into a file (like WSHTest.vbp) using the Save button or the File menu.
  5. Double click on the UserControl icon to open the control window. Use the Create “View Code” button in the project explorer window to show the code page.
  6. Add the code for procedures and functions which will be used within the project. These entries are exposed later as the methods of your ActiveX control.
  7. Select the command “Make …..ocx” in the “Files” menu. If you used the file Test.vbp to save your project the name Test.ocx is used. I have used the name WSHTest.ocx in the ZIP-Archive.

In some environments, the last step opens some dialogs allowing you to set a few options for your ActiveX control. You may close these dialogs to set the default values. Then, an OCX-file with the name of your project file (or the selected name) gets created. The OCX control gets registered automatically on your machine. Within your WSH script you do not need to worry about this file name, because the Windows Registry keeps all the information where to find the control.

Note: The window of the project explorer may be found in the upper right corner of the picture shown above. Currently my WSHTest project contains only one UserControl module. The window in the lower right is the property window showing the properties of the selected module. Currently I have selected the UserControl Test1. The names shown in brackets within the project explorer window are derived from the file names. If you save the project the first time, a project file name is associated. I have used here Test.vbp, so all other files uses the same name but different extensions (like Test.ctl for the source code file of the control). I have named the OCX file as WSHTest.ocx (which is different than the project file name) to demonstrate that file names are not used within your WSH scripts.

Important: Thanks to Steven Bondi, who has tested my sample, I would like to add an additional note. I can’t publish here hundred pages of my WSH book used to explain the basics of ActiveX programming. But, my first release of this page was a bit to short. You may use my sample ZIP file to study the code. But take care that the path settings depend on my test machine (I run several Windows 98 builds on my test machine – therefore the project file contains path names that may not apply to all user machines). If you intend to create your own ActiveX control it is important to use a new project! This assures that all paths and libraries are set properly within the project. Also VB 5 CCE creates a unique GUID for your control. This GUID is requested to register your control as a COM object. If you would like to sample code from another project, use the export and import function of VB 5 CCE (the program can run only one project at a time – this is another limitation of VB).

Use the Control in your script

After completing these steps, your ActiveX control is ready to be used within VBA. You need the project name and the UserControl in the VB 5 CCE, required for the CreateObject (“TypLib.class”) statement. The following VBScript line creates a reference to your new ActiveX control:

Set objAdr = WScript.CreateObject("WSHTest.Test1")

The name WSHTest is the name of the type library and it corresponds to the project name. The subname Test1 is the class name of the object and it is the same as the UserControl name. After you create a reference to the object using the CreateObject method you may access the methods exposed from this object. The following statement uses the WSHDate method:

myDate = objAdr.WSHDate()

We use the object variable and the name of the method within an assignment statement. The result is assigned to the myDate variable. Pretty simple, isn’t it?

The source code for the sample ActiveX control

For your convenience I have provided a few simple methods within the UserControl Test1. The source code is shown below:

'************************************************
'* Project:  Test1 (file Test.vbp)
'* Author:   (c) Günter Born
'*
'* This control demonstrates how to use the VB 5 CCE to
'* create ActiveX-controls for the Windows Scripting Host.
'* Compile it using the "Make Test.ocx" command of the
'* "File" menu within the VB 5 CCE. The OCX component is
'* getting registered automatically.
'*
'* Use it at your own risk. In no event will the author be
'* responsible/liable for any consequences of the use of this software.
'* The control may be used for free. Distribution is allowed,
'* if the whole package is distributed with the copyright note
'* and a note pointing to my web-site. This demo was derived
'* from a real sample published in my book "Inside Windows
'* Scripting Host", Microsoft Press Germany. Further versions
'* of this control will be published on my WSH Bazaar at:
'* http://www.borncity.de
'*************************************************

Public Function WSHGetTime() As String
' Just to test, return current time
 WSHGetTime = Time
End Function

Public Function WSHGetDate() As String
' Just to test, return current date
 WSHGetDate = Date
End Function

Public Function WSHInputBox(Prompt As String, title As String, Default As Variant) As Variant
' For JScript: provide a InputBox-Function
 WSHInputBox = InputBox(Prompt, title, Default)
End Function

Public Sub WSHAbout()
 txt = "ActiveX-Control to extend the Windows Scripting Host" + vbCrLf + _
        "with a new Test object and and new methods. Get more from my" + vbCrLf + _
        "Windows Scripting Host Bazaar at:" + vbCrLf + vbCrLf + _
        "http://www.borncitye.de"
 MsgBox txt, vbOKOnly + vbInformation, "(c) Günter Borns WSH-Test - Version 1.1"
End Sub

'*** End

The ActiveX control exposes the methods WSHGetDateWSHGetTimeWSHInputBox and WSHAbout. You may call these methods from either VBScript or JScript. WSHGetDate and WSHGetTime returns the current date and the current time. The WSHAbout method invokes a simple dialog box with some information about the control. The WSHInputBox method invokes an Inputbox dialog. This will be rather helpful in JScript, because this language doesn’t support the InputBox function from VBScript. Having the right ActiveX control enables you to use WSHInputBox in JScript (without the trickier way I have shown in my sample pages).

The source code for a VBScript test program

To show you more about the way how to use the methods provided by the WSHTest.ocx, I have created a short VBScript program. (By the way, the interface of these methods are identically to the WSHExtendActiveX control offered for download from this site). Further details about using the ActiveX control may be obtained from the following listing.

'************************************************
' File:    TestOCX.vbs (WSH sample in VBScript) 
' Author:  (c) Günter Born
'
' Demonstrates how to access the Test.ocx component
' using a WSH script.
' Use at your own risk. No support, no liability.
'
' New samples are available at the the WSH Bazaar:
' http://www.borncity.de
'************************************************
Option Explicit

Dim objAdr 

MsgBox "Test whether the ActiveX control Test.ocx is registered." + _
        vbCRLF + "If a run-time error occurs, the OCX isn't registered." + _
        vbCRLF + "Then use the program RegisterOcx.bat."

' Create the Test object, requires Test.ocx, otherwise the
' following line creates a run-time error (can't create object).
Set objAdr = CreateObject("WSHTest.Test1")

' our first attempt to use some methods
WScript.Echo "Time:", objAdr.WSHGetTime()
WScript.Echo "Date:", objAdr.WSHGetDate()

WScript.Echo "Your Input was: ", objAdr.WSHInputBox ( _
        "Enter something", "WSHTest by G. Born", "Hello")

objAdr.WSHAbout
' *** End

After invoking the script (double click on the VBS file), you will be guided through a few dialogs showing the date and time, asking for input and showing the “About” dialog. As you can see, accessing the methods of the ActiveX control is very simple.

And here comes another simple sample which may be used in the real world: it shows how convenient the ActiveX control WSHTest.ocx may be for the JScript programmers. The following few lines of code implements an InputBox in JScript.

//************************************************
// File:    Input.js  (WSH sample in JScript) 
// Author:   (c) G. Born 
//
// This script demonstrates how to get a user input in
// JScript. I use the ActiveX control Test.ocx.
// Use AS IS without any warranty. The source:
// the WSH Bazaar at
// http://www.borncity.de
//************************************************
// 

var title = "InputBox function for JScript";
var prompt = "Please enter your name:";

// Create the new object
var WSHTest = WScript.CreateObject("WSHTest.Test1");

// Get the user input and display it with the Echo method
WScript.Echo (WSHTest.WSHInputBox (prompt, title, "Hello World"));
//*** End

After invoking the script (double click on the JS file), you will see an input box asking for your name. If you click on OK, the user input will be shown in a 2nd dialog box. Clicking Cancel returns an empty string which will also be shown in a message box.

The whole project may be downloaded as a ZIP file: ActiveXDemo.zip (8 KB). Unzip the content of your file into a separate folder. The folder will then contain the source code modules for VB 5 CCE, the ready to use OCX, the VBS/JS test files and two BAT files. If you have already installed VB 5 CCE you can register the OCX and use the VBS file to test the control. To register/unregister the control use the two BAT files RegisterOcx.bat and UnregisterOcx.bat. If you load the VBP file into VB 5 CCE, you can inspect the source code and you may compile the project into a new OCX file.

Some examples of how to use the control to control Excel or Word:

 

 

Compile XLS file to EXE

Extracted from https://www.mrexcel.com/forum/excel-questions/456284-how-create-exe-file-vba.html?highlight=resource:

In VB6, select Add-Ins, Add in manager, VB6 Resource editor, Loaded/Unloaded, OK, select VB resource editor on tool bar (green), click Add Custom resource (on tool bar of the Resource editor) and add your .xls file. Note the “CUSTOM” number added. Use the following code to instal the .xls file.

Code:
LoadDataIntoFile 120, "C:\Myfilename.xls"
' ***120 is Custom Resource number
' "C:\Myfilename.xls" is file destination

Public Sub LoadDataIntoFile(DataName As Integer, filename As String)
    Dim myArray() As Byte
    Dim myFile As Long
    If Dir(filename) = "" Then
        myArray = LoadResData(DataName, "CUSTOM")
        myFile = FreeFile
        Open filename For Binary Access Write As #myFile
        '
        Put #myFile, , myArray
        
        Close #myFile
    End If
End Sub

Indent code

Autoindenting PHP Code

originaly written by Mark Rowlinson

Introduction

PHP code uses {} symbols to group pieces of code such as branches and loops. This code makes use of that by checking for the existence of them in each line. If it find a { then there should be a positive indent of the next line. If it finds a } then there should be a negative indent of the current line.

How to use the code

The code requires a reference to the “Microsoft Scripting Runtime” in order to make use of the FileSystemObject to read in and write out the file. Currently it is also written for Excel in that is uses GetOpenFileName to obtain the file to parse, this could easily be modified for VB to use a common dialog.

How it works

The code uses 1 Sub and 2 helper functions and uses a global variable to store the current indent level. When it starts it sets the indent to 0 and opens the file. It then loops through each line in the file, parses it using the FormatPHPLine function and stores it in an array. It then loops through the array to write the lines back out to the file

The FormatPHPLine checks for the occurrence of { and } to detremine if a positive or negative indent is required. If a negative is found it is applied strightaway i.e. the global variable is decreased by 1. The required indent is then created using the indent function. Finally the global variable is increased by 1 if a positive indent was found.

Improvements

There is much scope for improvement, such as, allowing for multiple occurrences on one line or making sure only code not comments are searched which I may add in the future.

The Full Code

Dim intIndent As Long 
 
Sub FormatPHP() 
     
    Dim strFile As String 
    intIndent = 0 
    strFile = Application.GetOpenFilename("PHP Files (*.php),*.php") 
    If strFile = "False" Then Exit Sub 
    Dim fso As Scripting.FileSystemObject 
    Set fso = New Scripting.FileSystemObject 
    Dim ts As Scripting.TextStream 
    Dim strText() As String 
    Dim x As Integer 
    x = 0 
    Set ts = fso.OpenTextFile(strFile, ForReading, False) 
    While Not ts.AtEndOfStream 
        x = x + 1 
        ReDim Preserve strText(1 To x) 
        strText(x) = FormatPHPLine(ts.ReadLine) 
    Wend 
    ts.Close 
    Set ts = fso.OpenTextFile(strFile, ForWriting, False) 
    For i = 1 To x 
        ts.WriteLine strText(i) 
    Next i 
    ts.Close 
    MsgBox "Done!" 
End Sub 
 
Function FormatPHPLine(ByVal strPHP As String) As String 
     'check for { and } to determine indent
     'if { then positive indent
     'if } then negative
    Dim pos As Boolean, neg As Boolean 
    If InStr(1, strPHP, "{") > 0 Then 
        pos = True 
    End If 
    If InStr(1, strPHP, "}") > 0 Then 
        neg = True 
    End If 
     'apply negative indent
    If neg Then 
        intIndent = Application.WorksheetFunction.Max(0, intIndent - 1) 
    End If 
    strPHP = indent(strPHP) 
     'apply +ve indent
    If pos Then 
        intIndent = intIndent + 1 
    End If 
    FormatPHPLine = strPHP 
End Function 
 
Function indent(ByVal y As String) As String 
    y = Trim$(y) 
    If intIndent > 0 Then 
        For i = 1 To intIndent 
            indent = indent & vbTab 
        Next i 
        indent = indent & y 
    Else 
        indent = y 
    End If 
End Function 

Port VBA to… (JavaScript, C++, Python)

An ongoing project we have on mind is to have a kind-of VBA port-language software, with the ultimately goal to get VBA code compiled, if this is anyway possible, to COM or DLL so code will be effectively hidden from prying eyes.

There are some steps that have to be achieved in this process before getting the port:

  1. Recognize special or reserved words
  2. Set reserved words equivalences to other programming language
  3. Detect procedures (Sub/Functions) and its variables
  4. Detect objects (Type)
  5. Detect enumeration variables (Enum)
  6. Structure code in blocks (DoLoop, ForNext, IfThen…) that can be indepedent one from each other
  7. Indent code (for better readability and comprenhension)
  8. Port from VBA to new language
  9. Revise code for unhandled exceptions

From here on, there is a high chance to get code compiled and reuse over Excel through VBA without exposing original code.

So, other popular scripting languages, aside VBA, are Python, JavaScript and PHP, all of them had been already wrapped to run under Excel. Other languages considered, as they already have their own compiler, are C++, the collection on .Net platform, and Fortran, so their compiled DLLs can be used with Excel.

If language port can be done to any one of those, then, it can be achived for them all.


VBA to JavaScript converter

My main interest now is focused in learning JS, and get the port from VBA to JavaScript.

One site that game me some hints on how to start on the JS world came from here. The converter scripts are far from running, but the map to follow the conversions is compact and, somehow, clear.

There are some functions in VBA but not in JS, so here comes very handy the collecting work done in this CodeProject article by .

Are these sites worth looking?, seems yes

  • http://jsil.org/
  • https://www.codeproject.com/Articles/25069/JSBasic-A-BASIC-to-JavaScript-Compiler
  • http://jsc.sourceforge.net/

I started with the IF (elseif, else) block, then jumped to the FOR (for each) block, and ended with the LOOP (Do-While/Until, While-Wend) block.

Let’s see the code:

Public Sub sV2J_If()
    Dim CodeBlock As String

    'CodeBlock = "If Condition1 = 1 Then If Condition2 = 2 Then If Condition3 = 3 Then Action: If Condition4 = 3 Then Action" & vbLf & _
                "If Condition1 = 1 Then" & vbLf & _
                vbTab & "If Sub1 = 1 Then" & vbLf & _
                vbTab & "   sub1 = 1.1" & vbLf & _
                vbTab & "ElseIf Sub2 = 2 Then" & vbLf & _
                vbTab & "   sub2 = 1.2" & vbLf & _
                vbTab & "ElseIf Sub3 = 3 Then" & vbLf & _
                vbTab & "   sub3 = 1.2" & vbLf & _
                vbTab & "Else" & vbLf & _
                vbTab & "   sub4 = 1.4" & vbLf & _
                vbTab & "End If" & vbLf & _
                "ElseIf Condition2 = 2 Then" & vbLf & _
                "   Statement2 = 2" & vbLf & _
                "ElseIf Condition3 = 3 Then" & vbLf & _
                "   Statement3 = 2" & vbLf & _
                "Else" & vbLf & _
                "   Statement4 = 2" & vbLf & _
                "End If"

    Call fVbCleaner(CodeBlock)

    'CodeBlock = fIf(CodeBlock:=CodeBlock, _
                    Then»:=" Then", _
                    If»:="if ", _
                    ElseIf»:="elseif ", _
                    Else»:="else", _
                    EndIf»:="end if", _
                    Then«:=" {", _
                    If«:="if ", _
                    ElseIf«:="elseif ", _
                    Else«:="else", _
                    EndIf«:="}", _
                    Opener«:=" {", _
                    Closer«:="} ", _
                    Indenter:=" ", _
                    Comment:="'")

    CodeBlock = "For lgCounter1 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "   Statement = lgCounter1" & vbLf & _
                "   For lgCounter2 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "      Statement = lgCounter2" & vbLf & _
                "      For lgCounter3 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "         Statement = lgCounter3" & vbLf & _
                "      Next lgCounter3" & _
                "   Next lgCounter2" & _
                "Next lgCounter1"

    CodeBlock = fFor(CodeBlock:=CodeBlock, _
                     For»:="For ", _
                     Next»:="Next", _
                     Break»:="Exit For", _
                     For«:="for ", _
                     Next«:="continue", _
                     Break«:="continue", _
                     Opener«:=" {", _
                     Closer«:="} ", _
                     Indenter:=vbTab, _
                     Comment:="'")
Stop
    Debug.Print CodeBlock
End Sub

Public Function fJSCleaner(ByRef CodeBlock As String) As Boolean
'!!! ToDo: only if not inside comment block
    Dim aComment() As String
    Dim aStatement() As String
    Dim lgStatement As Long

    ' Clean code
    CodeBlock = VBA.Replace$(CodeBlock, vbTab, "  ")        ' Avoid odd chars
    CodeBlock = VBA.Replace$(CodeBlock, vbLf, vbCrLf)       ' Avoid impropper breaks
'!!!!
    CodeBlock = VBA.Replace$(CodeBlock, "; ", vbCrLf)       ' Avoid combined lines
'!!!!

    ' Avoid traps
    CodeBlock = VBA.Replace$(CodeBlock, "if(", "if (")
    CodeBlock = VBA.Replace$(CodeBlock, "elseif(", "elseif (")
    CodeBlock = VBA.Replace$(CodeBlock, "for(", "for (")

'!!!!
    ' Break one line If
'!!!!

    CodeBlock = VBA.Join(aStatement(), vbCrLf)
End Function

Public Function fVbCleaner(ByRef CodeBlock As String) As Boolean
'!!! ToDo: only if not inside comment block
    Dim aComment() As String
    Dim aStatement() As String
    Dim lgStatement As Long

    ' Clean code
    CodeBlock = VBA.Replace$(CodeBlock, vbTab, "  ")        ' Avoid odd chars
    CodeBlock = VBA.Replace$(CodeBlock, vbLf, vbCrLf)       ' Avoid impropper breaks
    CodeBlock = VBA.Replace$(CodeBlock, ": ", vbCrLf)       ' Avoid combined lines
    CodeBlock = VBA.Replace$(CodeBlock, " _" & vbCrLf, " ") ' Avoid line continuation

    ' Break one line If
    aStatement() = VBA.Split(CodeBlock, vbCrLf)
    For lgStatement = LBound(aStatement) To UBound(aStatement)
        Do While VBA.InStr(1, VBA.LCase$(aStatement(lgStatement)), " then ", vbTextCompare) > 0
            aStatement(lgStatement) = VBA.Replace$(aStatement(lgStatement), " then ", " Then" & vbCrLf, 1, 1, vbTextCompare) & vbCrLf & "End If"
        Loop
    Next lgStatement

    CodeBlock = VBA.Join(aStatement(), vbCrLf)
End Function

Public Function fIIf(ByVal CodeBlock As String)
'ternary Operator (cond ? truepart : falsepart)

End Function

Public Function fForEach(ByVal CodeBlock As String, _
                     Optional ByVal For» As String = "For ", _
                     Optional ByVal Break» As String = "Break", _
                     Optional ByVal Continue» As String = "Exit For", _
                     Optional ByVal For« As String = "for ", _
                     Optional ByVal Break« As String = "break", _
                     Optional ByVal Continue« As String = "continue", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
'for (x in person) {
'}
End Function

Public Function fFor(ByVal CodeBlock As String, _
                     Optional ByVal For» As String = "For ", _
                     Optional ByVal Next» As String = "Next", _
                     Optional ByVal Break» As String = "Exit For", _
                     Optional ByVal For« As String = "for ", _
                     Optional ByVal Next« As String = "continue", _
                     Optional ByVal Break« As String = "continue", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
    Dim aForStatement() As String
    Dim aStatement() As String
    Dim lgStatement As Long
    Dim lgStatements As Long
    Dim bNewVar As Boolean
    Dim bGotoNext As Boolean
    Dim Counter As String

    aForStatement() = VBA.Split(CodeBlock, ";")
    lgStatements = UBound(aForStatement) - LBound(aForStatement) + 1

    If lgStatements >= LBound(aForStatement) + 0 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 0)) = vbNullString Then
            'Optional
            Counter = aForStatement(LBound(aForStatement) + 1)
        Else
            If VBA.InStr(1, aForStatement(LBound(aForStatement) + 0), "var ") > 0 Then
                bNewVar = True
                Counter = aForStatement(LBound(aForStatement) + 0)
            Else
            End If
        End If

    ElseIf lgStatements >= LBound(aForStatement) + 1 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 1)) = vbNullString Then
            'Optional
        End If

    ElseIf lgStatements >= LBound(aForStatement) + 2 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 2)) = vbNullString Then
            'Optional
        End If
    End If

' for (i = 0; i < cars.length; i++) {
'}
'for(var x=0, x<n; x++){...}

End Function

Public Function fFor_(ByVal CodeBlock As String, _
                      Optional ByVal For» As String = "For ", _
                      Optional ByVal Break» As String = "Break", _
                      Optional ByVal Continue» As String = "Exit For", _
                      Optional ByVal For« As String = "for ", _
                      Optional ByVal Break« As String = "break", _
                      Optional ByVal Continue« As String = "continue", _
                      Optional ByVal Opener« As String = " {", _
                      Optional ByVal Closer« As String = "} ", _
                      Optional ByVal Indenter As String = vbTab, _
                      Optional ByVal Comment As String = "'") As String
End Function

Public Function fIf(ByVal CodeBlock As String, _
                    Optional ByVal Then» As String = " Then", _
                    Optional ByVal If» As String = "If ", _
                    Optional ByVal ElseIf» As String = "ElseIf ", _
                    Optional ByVal Else» As String = "Else", _
                    Optional ByVal EndIf» As String = "End If", _
                    Optional ByVal Then« As String = " {", _
                    Optional ByVal If« As String = "if ", _
                    Optional ByVal ElseIf« As String = "elseif ", _
                    Optional ByVal Else« As String = "else", _
                    Optional ByVal EndIf« As String = "}", _
                    Optional ByVal Opener« As String = " {", _
                    Optional ByVal Closer« As String = "} ", _
                    Optional ByVal Indenter As String = vbTab, _
                    Optional ByVal Comment As String = "'") As String
' Translate IF block

    Dim aStatement() As String
    Dim aStatementLevel() As Long
    Dim aStack() As Long
    Dim lgStatement As Long
    Dim lgStatement_End As Long
    Dim lgLevel As Long:    lgLevel = 0
    Dim strCode As String:  strCode = vbNullString
    Dim CodeOut As String:  CodeOut = vbNullString

    ' Split If sub-blocks
    CodeBlock = VBA.Replace$(CodeBlock, Then» & vbCrLf, vbCrLf)
    CodeBlock = VBA.Replace$(CodeBlock, Then», vbCrLf)
    aStatement() = VBA.Split(CodeBlock, vbCrLf)
    ReDim aStatementLevel(LBound(aStatement) To UBound(aStatement))
    ReDim aStack(LBound(aStatement) To UBound(aStatement))
    For lgStatement = LBound(aStatement) To UBound(aStatement)
        strCode = VBA.Trim$(VBA.LCase$(aStatement(lgStatement)))
        If strCode Like If» & "*" Then
            aStack(lgStatement) = 1
            aStatement(lgStatement) = VBA.Mid$(aStatement(lgStatement), _
                                               VBA.InStr(1, aStatement(lgStatement), If», vbTextCompare) + VBA.Len(If»))

            ' Avoid "=" comparison and apply "==" (Note: will fail for "===" intention!)
            aStatement(lgStatement) = VBA.Replace$(aStatement(lgStatement), "=", "==")

            lgLevel = lgLevel + 1
            aStatementLevel(lgStatement) = lgLevel

        ElseIf strCode Like EndIf» & "*" Then
            aStack(lgStatement) = 0
            aStatement(lgStatement) = "'" 'comment... avoid line
            aStatementLevel(lgStatement) = lgLevel
            lgLevel = lgLevel - 1

        ElseIf strCode Like ElseIf» & " *" Then
            aStack(lgStatement) = 2
            aStatement(lgStatement) = VBA.Mid$(aStatement(lgStatement), _
                                               VBA.InStr(1, aStatement(lgStatement), ElseIf», vbTextCompare) + VBA.Len(ElseIf»))
            aStatementLevel(lgStatement) = lgLevel

        ElseIf strCode Like Else» & "*" Then
            aStack(lgStatement) = 3
            aStatement(lgStatement) = "'" 'comment... avoid line
            aStatementLevel(lgStatement) = lgLevel

        Else ' action code
            aStack(lgStatement) = 4
            aStatementLevel(lgStatement) = -lgLevel
            aStatement(lgStatement) = VBA.Trim$(aStatement(lgStatement))
        End If
    Next lgStatement

    lgStatement = LBound(aStatement)
    lgStatement_End = UBound(aStatement)
    CodeOut = fIf_(aStatement(), _
                   aStatementLevel(), _
                   aStack(), _
                   lgStatement, _
                   lgStatement_End, _
                   Then»:=Then», _
                   If»:=If», _
                   ElseIf»:=ElseIf», _
                   Else»:=Else», _
                   EndIf»:=EndIf», _
                   Then«:=Then«, _
                   If«:=If«, _
                   ElseIf«:=ElseIf«, _
                   Else«:=Else«, _
                   EndIf«:=EndIf«, _
                   Opener«:=Opener«, _
                   Closer«:=Closer«, _
                   Indenter:=Indenter, _
                   Comment:=Comment)

    fIf = CodeOut
End Function

Public Function fIf_(ByRef aStatement() As String, _
                     ByRef aStatementLevel() As Long, _
                     ByRef aStack() As Long, _
                     ByRef lgStatement_Start As Long, _
                     ByVal lgStatement_End As Long, _
                     Optional ByVal Then» As String = " Then", _
                     Optional ByVal If» As String = "If ", _
                     Optional ByVal ElseIf» As String = "ElseIf ", _
                     Optional ByVal Else» As String = "Else", _
                     Optional ByVal EndIf» As String = "End If", _
                     Optional ByVal Then« As String = " {", _
                     Optional ByVal If« As String = "if ", _
                     Optional ByVal ElseIf« As String = "elseif", _
                     Optional ByVal Else« As String = "else", _
                     Optional ByVal EndIf« As String = "}", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
' Translate IF sub-block

    Dim CodeOut As String
    Dim lgStatement As Long
    Dim lgStatement_Block As Long
    Dim lgLevel As Long
    Dim Indent As String
    Dim Indent_ As String

    lgLevel = aStatementLevel(lgStatement_Start)
    Indent = VBA.String(lgLevel - 1, Indenter)
    Indent_ = VBA.String(lgLevel, Indenter)
    CodeOut = vbNullString
    CodeOut = CodeOut & Indent & If« & "(" & aStatement(lgStatement_Start) & ")" & Then« & vbLf

    For lgStatement = (lgStatement_Start + 1) To lgStatement_End
        If aStack(lgStatement) = 1 Then ' if sub-block
            lgStatement_Block = lgStatement
            Do Until (aStack(lgStatement_Block) = 0) And (lgLevel + 1 = aStatementLevel(lgStatement_Block))
                lgStatement_Block = lgStatement_Block + 1
            Loop

            CodeOut = CodeOut & _
                      fIf_(aStatement(), _
                           aStatementLevel(), _
                           aStack(), _
                           lgStatement, _
                           lgStatement_Block, _
                           Then»:=Then», _
                           If»:=If», _
                           ElseIf»:=ElseIf», _
                           Else»:=Else», _
                           EndIf»:=EndIf», _
                           Then«:=Then«, _
                           If«:=If«, _
                           ElseIf«:=ElseIf«, _
                           Else«:=Else«, _
                           EndIf«:=EndIf«, _
                           Opener«:=Opener«, _
                           Closer«:=Closer«, _
                           Indenter:=Indenter)

        ElseIf aStack(lgStatement) = 2 Then 'ElseIf
            CodeOut = CodeOut & Indent & Closer« & " " & ElseIf« & "(" & aStatement(lgStatement) & ") " & Opener« & vbLf
            Do While (aStack(lgStatement) = 0) And (lgLevel = aStatementLevel(lgStatement))
                CodeOut = CodeOut & VBA.String(lgLevel, Indenter) & aStatement(lgStatement) & vbLf
            Loop

        ElseIf aStack(lgStatement) = 3 Then 'Else
            CodeOut = CodeOut & Indent & Closer« & " " & Else« & Opener« & vbLf

        ElseIf aStack(lgStatement) = 0 Then 'EndIf
            CodeOut = CodeOut & Indent & EndIf« & ";" & vbLf

        Else 'ElseIf aStack(lgStatement) = 4 Then 'And aStatementLevel(lgStatement) < 0
            If aStatement(lgStatement)  Comment Then
                CodeOut = CodeOut & Indent_ & vbTab & aStatement(lgStatement) & ";" & vbLf
            End If
        End If
    Next lgStatement

    lgStatement_Start = lgStatement_End
    fIf_ = CodeOut
End Function

Here is a VBA equivalent to JS Push function

Sub sPush()
Dim aArray() As Variant
Dim PushVal As Variant

aArray() = Range("$A$1:$B$5").Value2 'fArray2D(Array(1, 2))
PushVal = Range("$A$6:$B$8").Value2 'fArray2D(Array(3, 4))
aArray() = fArray2D(Array(1, 2))
PushVal = fArray2D(Array(3, 4))
Stop
fPush aArray(), PushVal
Stop
End Sub

Public Function fArray2D(ByRef aArray As Variant) As Variant()
Dim aArrTmp() As Variant
Dim lgArrayDim1 As Long
Dim lgArrayDim2 As Long
Dim lgR As Long
Dim lgC As Long

On Error GoTo Array2D
lgArrayDim1 = UBound(aArray, 1) - LBound(aArray, 1) + 1
lgArrayDim2 = UBound(aArray, 2) - LBound(aArray, 2) + 1

GoTo ExitProc

Array2D:
If lgArrayDim2 = 0 Then
ReDim aArrTmp(LBound(aArray, 1), _
LBound(aArray, 1) To UBound(aArray, 1))

For lgC = LBound(aArray, 1) To UBound(aArray, 1)
aArrTmp(LBound(aArray, 1), lgC) = aArray(lgC)
Next lgC

fArray2D = aArrTmp()
Erase aArrTmp()
End If

ExitProc:
On Error GoTo 0
End Function

Public Function fPush(ByRef aArray() As Variant, _
ByRef PushVal As Variant)
Dim aArrTmp() As Variant
Dim lgArrayDim1 As Long
Dim lgArrayDim2 As Long
Dim lgPushDim1 As Long
Dim lgPushDim2 As Long
Dim lgR As Long
Dim lgR_Push As Long
Dim lgC As Long

On Error GoTo ErrControl 'Resume Next
lgArrayDim1 = UBound(aArray, 1) - LBound(aArray, 1) + 1
lgArrayDim2 = UBound(aArray, 2) - LBound(aArray, 2) + 1
lgPushDim1 = UBound(PushVal, 1) - LBound(PushVal, 1) + 1
lgPushDim2 = UBound(PushVal, 2) - LBound(PushVal, 2) + 1

If lgArrayDim2 = lgPushDim2 Then
ReDim aArrTmp(LBound(aArray, 1) To UBound(aArray, 1) + 1 + UBound(PushVal, 1) - LBound(PushVal, 1), _
LBound(aArray, 2) To UBound(aArray, 2))
lgR_Push = -1
For lgR = LBound(aArrTmp, 1) To lgArrayDim1 - (1 - LBound(aArray, 1))
lgR_Push = lgR_Push + 1
For lgC = LBound(aArray, 2) To UBound(aArray, 2)
aArrTmp(lgR, lgC) = aArray(LBound(aArray, 1) + lgR_Push, lgC)
Next lgC
Next lgR

lgR_Push = -1
For lgR = lgArrayDim1 + LBound(aArray, 1) To UBound(aArrTmp, 1)
lgR_Push = lgR_Push + 1
For lgC = LBound(PushVal, 2) To UBound(PushVal, 2)
aArrTmp(lgR, lgC) = PushVal(LBound(PushVal, 1) + lgR_Push, lgC)
Next lgC
Next lgR
aArray() = aArrTmp()
Erase aArrTmp()
End If

ExitProc:
On Error GoTo 0
Exit Function
ErrControl:
GoTo ExitProc
End Function
[/sourcecode]