MZ-Tools for VBA and VB6

MZ-Tools is a terrific tool for anybody serious in getting some code done in Excel VBA and VB6.

Press here to download this free tool MZ-tools

With this tool is much easier to document your code and speed up some repetivie tasks.
The tools I use most often are

1) Insert and Remove line numbers.
    This is very usefull if you want to add the line number at which an error occurs with the Erl functions

2) Add Module Header. This is the the snipped I use

'----------------------------------------------------------------------
' Module    : {MODULE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'----------------------------------------------------------------------
'

Option Explicit

Private Const cMODULE_NAME As String = "{MODULE_NAME}"

3) Add Procedure Header

'---------------------------------------------------------------------------
' Procedure : {PROCEDURE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'---------------------------------------------------------------------------
'
' Inputs    :
'
' Output    :
'
'---------------------------------------------------------------------------
'

4) Error Handler

    Const cPROC_NAME As String = "{PROCEDURE_NAME}()"

On Error GoTo ErrorHandler

    {PROCEDURE_BODY}

Exit {PROCEDURE_TYPE}

ErrorHandler:
    Call ErrorHandler.Log(cMODULE_NAME, cPROC_NAME, Err.description,)

All those code snippets must be added in the option... dialog box of the MZ-Tools add-in.

Havin a module header and a procedure header for each module and procedure that you write will greatly enhance code maintainance.

In addition I have also and Error Handling snippet. This use my ErrorHandler module.

The ErroHandler module is a piece of code I wrote to manage in a coherent way the error raised by an application.

Property Set, Let, Get

In VBA when we define the properties of an object we have a Let, Set and Get operators.
We use the Get operator to retrieve the value of a property
Whe use the Set operator to set the value of an Object
We use the Let operator to set the value of a variable (not an object : string, double, enumeration...)

The use of the Set operator, force you to use the Set function when setting the value of an object property of type object.

Option Explicit

Private Const cMODULE_NAME As String = "House"
Private mAddress As String
Private mDoors As Collection



Public Property Get Address() As String
  
   Address = mAddress
   
End Property

Public Property Let Address(value As String)
   
   mAddress = value
   
End Property

Public Property Get Doors() As Collection
   
   Set Doors = mDoors
  
End Property

Public Property Set Doors(value As Collection)
   Set mDoors = value
End Property

Buttons and spreadsheet duplicaton with Excel VBA

If you want to add Button on an excel spreadsheet you have two choice

1) Active-x buttons
2) Form buttons


I you plan to duplicate a spreadsheet whic contains buttons using VBA Code, than you must use
Form buttons not Active-x ones.
If you use Active-x buttions you might get a VBA run time errors, which now on top of my head I don't rememeber.

So the tip here is
Use Form buttons if you plan to duplicat using VBA code to duplicate excel spread-sheets that contains them.

Using implements behind an Excel worksheet function

Since an excel worksheet is represented by a class module in vba, you might be tempted, as I was, to use the implements keywords behind a worksheet.
This would allow you to use polymorphically an Excel worksheet and could open-up differ possibilities.
Howev this is my advice

DO NOT USE IMPLEMENTS BEHING A WORKSHEET

I have noticed that despites the code compiles, the overall worksheet becomes unstable and tend to crash!
In additon the TypeOf function applied to the worksheet object that use the implements keyword, does not always behaves as you would expect.
Ex:
if you write on top of an excel worksheet module

Implements IEngine


Some time the test TypeOf sht is IEngine will return false even if it is implementing the interface.

Strongly typed dictionary collection in VBA

In this post, I will show you how to build a strongly type dictionary and how to loop through its elements with a For Each Loop.

The Generic Dictionary Object can be found in the "Microsoft Scripting Runtime" library.
This can be used to store any type of variables or object. However, it is usually very useful to wrap it up so that you can create your own strongly typed dictionary.
As you will see from the code below, a very simple way to loop through the collection of elements in a dictionary is to loop through its keys. The variable key must be variant type.

For Each key In emps.Keys
   Set emp = emps(key)
   Debug.Print emp.Name
Next

The first class is a strongly typed dictionary of Employee object, called Employees.
A usual to make the .Item property to be the default property you need to add the

Attribute Item.VB_UserMmeId = 0

just below the definition of the Get Item property

Option Explicit

Private Const cMODULE_NAME As String = "Employees"Private mDic As Dictionary


Private Sub Class_Initialize()
  Set mDic = New Dictionary
End Sub
Private Sub Class_Terminate()
  Set mDic = Nothing
End Sub

Public Sub Add(key As Variant, Item As Employee)
  
    Call mDic.Add(key, Item)
  
End Sub

Public Property Get Item(key As Variant) As Employee
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
   'In VBA, uncomment the first line. Export, Remove and import the file again. To make it work

   Set Item = mDic.Item(key)
       
End Property

Public Function count() As Long
   count = mDic.count
End Function

Public Function Exists(key As Variant) As Boolean
  Exists = mDic.Exists(key)
End Function

Public Function items() As Variant
 items = mDic.items
End Function

Public Function Remove(key As Variant)
  mDic.Remove (key)
End Function

Public Function RemoveAll()
  mDic.RemoveAll
End Function

Public Function Keys() As Variant
  Keys = mDic.Keys
End Function

This is the the Employee Class

Option Explicit

Private Const cMODULE_NAME As String = "Employee"
Private mIdentifier As Long
Private mName As String
Private mAge As Long

Public Property Get Identifier() As Long
  Identifier = mIdentifier
End Property

Public Property Let Identifier(value As Long)
    mIdentifier = value
End Property

Public Property Get Name() As String
   Name = mName
End Property

Public Property Let Name(value As String)
   mName = value
End Property

Public Property Get Age() As Long
  Age = mAge
End Property

Public Property Let Age(value As Long)
  mAge = value
End Property

This is the Sub to test the Code

Sub TestCollection()

Dim emp As Employee
Dim emps As Employees
Dim key As VariantSet emps = New Employees
Set emp = New Employee
emp.Identifier = 1
emp.Name = "Mario"
emp.Age = 34
Call emps.Add(emp.Identifier, emp)

Set emp = New Employee
emp.Identifier = 2
emp.Name = "Gino"
emp.Age = 12
Call emps.Add(emp.Identifier, emp)

For Each key In emps.Keys
   Set emp = emps(key)
   Debug.Print emp.Name
NextEnd Sub

Environ. A useful function to get environment infos

The Environ function is a pretty useful VBA function that gives you back many important info about the pc.
If you copy and paste this code snippet, you will see in the immediate window the full list of parameter to use and what they return.

One of the most useful are

USERNAME
APPDATA
CommonProgramFiles

Public Sub EnvironParameters()
    
    Dim nCount As Integer
    nCount = 0
    nCount = nCount + 1
    Do Until Environ(nCount) = ""
        Debug.Print Environ(nCount)
        nCount = nCount + 1
    Loop

   
End Sub

This is the complete list of parameters

ALLUSERSPROFILE

APPDATA
CommonProgramFiles
COMPUTERNAME
ComSpec
FP_NO_HOST_CHECK
HOMEDRIVE
HOMEPATH
HOMESHARE
LOGONSERVER
NUMBER_OF_PROCESSORS
OS
Path
PATHEXT
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramFiles
PSModulePath
SESSIONNAME
SystemDrive
SystemRoot
TEMP
TMP
TNS_ADMIN
UATDATA
USERDNSDOMAIN
USERDOMAIN
USERNAME
USERPROFILE
VS90COMNTOOLS
WecVersionForRosebud.224
windir