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

VB6 IsMissing() and optional arguments

In VB6/VBA the IsMissing function is used to test for optional arguments passed to user-defined function procedures as variants. Default values were not required for optional arguments; if no value was passed then IsMissing returned true. It was also possible to specify a default value of a primitive type to pass to the Variant argument.

The important point is that IsMissing only work for optional argurment of Variant Type.
If the optional argument is not of variant type, you should not use the IsMissing() function.
In this case always supply a default value, even if the compiler does not require it.

VBA / VB6 Interface implementation

Few months ago I blogged about interface implementation in VB6. You can find the post here
However, I discovered that the code had a memory leak caused by a circulare reference.
You can find a solution here.

The Keyword Implements in VBA/VB6 allows you to do Interface Implementation, which in turns allows for Polymorphism. This is indeed a great capability of the VB6 language that is often overlooked.
While the VB6 version of interface implementation is not so elegant as the one from .NET environment, it still allows you to implement most of the pattern of the GoF books
I will show you a very easy example on how to use it in VBA.
The Idea is to create an interface called IInstrumet with just one property "Id", and have a Security class that implements its interface. You could also have a Fund, Porftolio or a Security Class that implements this interface. This wil allow for polymorphism.

Dim inst as IInstrument
Dim sec as Security
Dim fn as Fund

Set sec = new Security
Set fn = new Fund

Set inst = sec
Set inst = fn

As you can see, bot a security and a fund can be assigned to an Instruments object!
We first define a Class called IInstrument. The code is here

'This is an Interface for the Generic Financial Instrument

Public Property Get Id() As String
'Only Signature
End Property

Public Property Let Id(value As String)
'Only Signature
End Property

We now create a new Class called Security that Implements the IInstrument one. This is a bit more tricky.
Once we implement an Interface, the Class that implements it in VB6 will declare those method as Private like that.

Private Property Get IInstrument_Id() As String
   IInstrument_Id = mId
End Property

This is a kind of unsual behaviour, because if I know that the Security class implements the IInstrument interface, I expect to have access to the same methods and property made available by the interface obejcts. For this reason, I usually expose as public member the same properties and functions that are available in the interface. When I implement them, I delegate the job to the interface method


3) I implement the interface methods as I normally do. a declare a module level variable mId

Private Property Get IInstrument_Id() As String

    IInstrument_Id = mId  
End Property

Private Property Let IInstrument_Id(value As String)
   mId =value
End Property

4) I crate public properties / methods mirroring the interface delegating their implementation to the
    mInstrument object
Public Property Get Id() As String

   Id =IInstrument_Id
End Property

Public Property Let Id(value As String)
  IInstrument_Id = value
End Property

Here you can find the Security Class Code

Implements IInstrument

Private mId As String
Public Ticker as String



Private Sub Class_Initialize()

End Sub

Private Property Get IInstrument_Id() As String
       IInstrument_Id = mId
End Property


Private Property Let IInstrument_Id(value As String)
  mId = value
End Property


'Public Interface

Public Property Get Id() As String
     Id = IInstrument_Id
End Property

Public Property Let Id(value As String)
   IInstrument_Id = value
End Property

We can now test the code

Sub TestSecurity()
 Dim Sec1 As Security
 Dim Inst As IInstrument
 Dim Sec2 As Security
 Set Sec1 = New Security
 
 Sec1.Id = 10
 Sec1.Ticker = "MXEU"
 
 Set Inst = Sec1 'Upcast: A Security in an Instruments
 Debug.Print Inst.Id
 
 'DownCast, this should have been done explicit, but VBA does not support CType.
 'VB6 does. So instead of CType(Inst, "Security") we can do
 If TypeName(Inst) = "Security" Then
    Set Sec2 = Inst
    End If
 
 
 Set Sec2 = Inst

 Debug.Print Sec2.Id
 Debug.Print Sec2.Ticker
End Sub

VBA Strongly typed collections

An Excel File with the sample code can be found here

In this blog post I will show you how to create a Strongly Type Collection Class wich has both an Item as default property and for wich we can use the For Each Loop.
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth

Option Explicit

Dim mName As String
Dim mSurname As String
Dim mDateofBirth As Date

Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(strName As String)
   mName = strName
End Property
Public Property Get Surname() As String
   Surname = mSurname
End Property
Public Property Let Surname(strSurname As String)
   mSurname = strSurname
End Property
Public Property Get DateOfBirth() As Date
  DateOfBirth = mDateofBirth
End Property
Public Property Let DateOfBirth(dteDateofBirth As Date)
   mDateofBirth = dteDateofBirth
End Property 

Then we can create the Collection Class called People

Option Explicit

'This is going to be a stroingly type Collection

Private mCol As Collection

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub
Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub
Property Get Item(Index As Variant) As Person
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
      
   Set Item = mCol.Item(Index)
End Property
Property Get NewEnum() As IUnknown
  'Attribute NewEnum.VB_UserMemId = -4
  'Attribute NewEnum.VB_MemberFlags = "40"
  
  'The first Attribute makes it the Default Enumerator Property
  'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense
 
  
  'This Routine  Get the Enumerator for the Collection.
  'To get this to work you must add two attributes
  
  
  
  Set NewEnum = mCol.[_NewEnum]
End Property
Public Sub Add(Item As Person, Optional key As Variant)
  Call mCol.Add(Item, key)
End Sub
Public Function Count() As Long
   Count = mCol.Count
End Function
Public Sub Remove(Index As Variant)
   mCol.Remove (Index)
End Sub

There are few Attributes that you cannot see in the VBA IDE. If you export a file from one of your VB6 procedures and view it, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s). The two properties in question will look something like this:

Property Get Item(Index As Variant) As Parameter
       Attribute Item.VB_UserMemId = 0   

       Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown

   Attribute NewEnum.VB_UserMemId = -4  
   Attribute NewEnum.VB_MemberFlags = "40"

  Set NewEnum = Me.mCollection.[_NewEnum]

End Property

Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)
The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to
(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove")
 (5) Manually add the Attribute Lines as shown above
 (6) Re-Import the edited Class

 An easier way, as one of my reader pointed out is
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it
2)  Export the Class
3) Remove the Class
4) Reinport it again.
 The Attribute will be in the .cls file, but they will not be visible (you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)
As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.) The alternative is to create your class 100% within VB6 and then import it into your VBA Project. Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

This is the Code you can use to test the Class

Option Explicit

Sub prova()

Dim Employee As Person
Dim Director As Person
Dim Team As People
Dim p As Person
Dim i As LongSet Employee = New Person
Employee.DateOfBirth = "10 Jan 1974"
Employee.Name = "Mario"
Employee.Surname = "Rossi"Set Director = New Person
Director.DateOfBirth = "10 Mar 1970"
Director.Name = "Giulia"
Director.Surname = "Verdi"Set Team = New People
Call Team.Add(Employee)
Call Team.Add(Director)

For i = 1 To Team.Count
 Debug.Print Team(i).Name
  
Next i

For Each p In Team
  Debug.Print p.Name
NextEnd Sub

C# Express Tools Options

Here are some usefull Options to tick in the C# 2010 Options Dialog Box

To Show the Configuration and Platform options in the Project property Debug Window
Projects and Solutions - General - Show Advance build configurations


To redirect output to the immediate window
Debbugging - General - Redirect all output window text to the Immediage window

How to change the Exception Handling Options
Tools - Customize - Commands
Menu bar: Debug
Click where you want to put the new Command, in the controls dialog box
Add Command, Debug, Exceptions...
Ok, Close