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 and VB6 Debugging Options

In this blog post I will explore the VBA Debugging options.
If you go to
Tools - Options - General

You will see the Error Trapping Options

1) Break on All Errors
2) Break in Class Modlue
3) Bread on Unhandled Errors


Depeding on whether you have an error hanler active or not, or if you call the a class of modue function you will get different behavious. Let's test them out.

1) Break on all errors. 

      Caller is a Sub of Function
          It stops at all errors as soon as they occur: ex division by zero or Err.Raise

      Caller is an Excel UDF.
         The code will NOT STOP. It will just end execution at the point where the error is caused
         Excel will Return #VALUE!


2) Break in Class Module

     Caller is a Sub or Function
          It stops only on Unhandled errors. If it meets an Err.Raise in a Class module it will stop
          in any case.

    Caller is an Excel UDF
          It terminate only on Unhandled errors. If it meets an Err.Raise in a Class modue it will raise
          Err.num 440, irrespective or the error number raised. VERY STRANGE
 
3) Break on Unhandled Errors
      Caller is a Sub or Functin or UDF.
      It stops only on Unhandler errors.


As you can see Opton number 3 is the one that gives the most consistency, followed by option number 1 and
finally option number 2.
I would recommend to use always "Break on Unhandled Errors" and switch to any of the other two options only if you are debugging difficult code.
Option 2 is interesting especially when you are developing an ActiveX component and you want to stop the debugger in the class.

Again, use "Break on Unhandled Errors" and you will save a lot of time trying to put up with the inconsistency between on how the debbuger behaves in case you are using an Excel UDF or just simply a sub or function.

Interface Implementation in VBA and VB6

WARNING THIS CODE HAS A MEMORY LEAK: check UPDATE HERE

I have realized that setting Set mInstruments = Me in the Class inizialize method create a circular reference and so a memory leak. The new code is much better.

The Keyworkd Implements in VBA/VB6 allows you to Interface Implementation in VB6, which in turns allows for Polymorfism. This is indeed a great capabilites on 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 some nice pattern like the strategy or the abstract facotory one.
I will show you a very easy example on how to use it in VBA."
The Idea is to create an interface calle IInstrumets 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 its interface. This wil allow for polymorfism.

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(strId 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 code the Class using this rules
1) In the Class that implements the interface I declare at Class level a private object of the interface type

      Private mInstrument As IInstrument



2) In the constructor I assing the Obj to this instance. This will allow me to call the interface methods of my class

  Private Sub Class_Initialize()
    'I need to Access the IInstruments Methods
    Set mInstrument = Me
End Sub

3) I implement the interface methods as I normally do.

Private Property Get IInstrument_Id() As String

    IInstrument_Id = mId  
End Property

Private Property Let IInstrument_Id(strId As String)
   mId = strId
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 = mInstrument.Id
End Property

Public Property Let Id(strId As String)
   mInstrument.Id = strId
End Property

Here you can find the Security Class Code

Implements IInstrument

Private mId As String
Private mInstrument As IInstrument

Public Ticker As String

Private Sub Class_Initialize()
  'I need to Access the IInstruments Methods
  Set mInstrument = Me
End Sub

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


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


'Public Interface

Public Property Get Id() As String
     Id = mInstrument.Id
End Property

Public Property Let Id(strId As String)
  mInstrument.Id = strId
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

VB6 and VBA Enumerations

An Enumeration in VBA/VB6 is a special type of long Variable.
A great resource can be found here on cpearson.com

Enum Position
  [_First] = -1
  RelativeDynamic = 0
  RelativeStatic = 1
  Absolute = 2
  [_Last] = 3
End Enum


Enum FactorEngine
  [_First] = -1
  AA = 0
  [_Last] = 1
End Enum

  

The code above specify two kind of Enumerations, Position and FactorEngine.
The [_First] and [_Last] are not necessary but they can be used to validate the Enumerated variables.
The _ makes the Enumrated variable hidden to the intellisense, while the [ makes it a valid character for the VB6 interpreter.
This is an example on how to Validate Enumerations

Sub TestEnum()
Dim Fac As FactorEngine
Dim i As Long
Dim IsValid As Boolean

Fac = AA
IsValid = False

For i = FactorEngine.[_First] To FactorEngine.[_Last]

  If Fac = i Then
      IsValid = True
      Exit For
  End If
Next i

If IsValid = True Then
   Debug.Print Fac & " Is a valid Engine"
Else
   Debug.Print Fac & " Is NOT a valid Engine"
End If


End Sub

How to access .Net library from VBA and VB 6.0

Download Example from Microsoft  here

Here you can find a couple of links that will explain you how to access the .Net Framework Class Libray (FCL) from VB6.0 and VBA.

This link contains a series of articles on COM and .NET
Link 1

This one shows you to use some .NET library withoug writing any line of code Link 2
In a netshell

1. Download and install either version 1.1 or version 2.0 of the .NET Framework. If you have installed Visual Studio .NET 2003 or Visual Studio 2005, or any of the Express products, then the .NET framework is already installed.
2. Execute Register.bat, which is included in the code download for this article. This registers the .NET framework System.dll so that it can be called as a COM object.
3. Start Visual Basic 6.
4. In the New Project dialog, select Standard EXE, and click OK.
5. Add a CommandButton and Image control to the form.
6. Set the Stretch property of the Image to true.
7. Select the Project | References menu command.
8. Click Browse.
9. For v1.1 of the .NET Framework, select C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.tlb. For v2.0 of the .NET framework, select C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.tlb.
10. Click OK. 

 Thies the the contens of the Register.bat file you can find on the link above.

path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;
regasm "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\system.dll"
regasm "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.dll"
pause
  
as you can see it use the regasm utilty to register as a COM object the system.dll file.

More interestingly:

The FCL also ships with a number of powerful collection classes, which include:

• ArrayList—An array class that doesn't have a fixed size. You can just keep adding items to it.
• Hashtable—This class is similar to the Scripting.Dictionary class. You can add items and look them up by key.
• Queue—This is a first in, first out (FIFO) collection. You push items in, and then read them out at a later time in the same order.
• Stack—A first-in, last out (FILO) collection. You push items onto the stack, and then pop them off in reverse order.
• SortedList—Similar to the Hashtable, except when you iterate through the items, they're always sorted by the key.

 To use this classe just add

a reference to either C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb or C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\mscorlib.tlb, depending on the version of the framework that you want to use. Mscorlib is part of the Microsoft .NET Framework, and contains the collection classes.

and you are ready to go.

The are few caveats though
1) Intellisense will not work!!!.  To sort this out you need to write your own COM wrapper (I will tell you how to do it)
2) To use the For Each loop to run through the collection you need to define a variable as IEnumerable and assign the list to this interface. This is because the For Each loop need an explicit GetEnumerable (equal to NewEnum) method to work
   
    Dim objSortedList As mscorlib.SortedList
    Dim Enum As mscorlib.IEnumerable
    Set Enum = objSortedList
3) If you follow step 2 instruction you will have the For Each loop work, however the single object returned for an HashTable or a SortedList is a DictionaryEntry. This means tha VB 6.0 will not be able to access anyway its properties
 
All of these problems can be solved coding your own COM class in C#.

This is explained on this Link3 you can also find some info on my blog here and I will soon post some sample projects