tag:blogger.com,1999:blog-10134736116262553612024-03-27T06:35:52.042+00:00Productive BytesPierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.comBlogger73125tag:blogger.com,1999:blog-1013473611626255361.post-77720318189272207612012-10-15T17:03:00.001+01:002012-10-15T17:05:16.164+01:00MZ-Tools for VBA and VB6MZ-Tools is a terrific tool for anybody serious in getting some code done in Excel VBA and VB6.<br />
<br />
Press here to download this free tool <a href="http://www.mztools.com/v3/mztools3.aspx" target="_blank">MZ-tools</a><br />
<br />
With this tool is much easier to document your code and speed up some repetivie tasks.<br />
The tools I use most often are<br />
<br />
1) Insert and Remove line numbers. <br />
This is very usefull if you want to add the line number at which an error occurs with the Erl functions<br />
<br />
2) Add Module Header. This is the the snipped I use <br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="rem">'----------------------------------------------------------------------</span><span class="rem">
' Module : {MODULE_NAME}</span><span class="rem">
' Author : {AUTHOR}</span><span class="rem">
' Date : {DATE}</span><span class="rem">
' Purpose : </span><span class="rem">
'----------------------------------------------------------------------</span><span class="rem">
'</span><span class="kwrd">
Option</span> Explicit
<span class="kwrd">Private</span> <span class="kwrd">Const</span> cMODULE_NAME <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"{MODULE_NAME}"</span></pre>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
3) Add Procedure Header</div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="rem">'---------------------------------------------------------------------------</span><span class="rem">
' Procedure : {PROCEDURE_NAME}</span><span class="rem">
' Author : {AUTHOR}</span><span class="rem">
' Date : {DATE}</span><span class="rem">
' Purpose : </span><span class="rem">
'---------------------------------------------------------------------------</span><span class="rem">
'</span><span class="rem">
' Inputs :</span><span class="rem">
'</span><span class="rem">
' Output :</span><span class="rem">
'</span><span class="rem">
'---------------------------------------------------------------------------</span>
'</pre>
<br />
4) Error Handler</div>
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"> <span class="kwrd">Const</span> cPROC_NAME <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"{PROCEDURE_NAME}()"</span><span class="kwrd">
On</span> <span class="kwrd">Error</span> <span class="kwrd">GoTo</span> ErrorHandler
{PROCEDURE_BODY}
<span class="kwrd">Exit</span> {PROCEDURE_TYPE}
ErrorHandler:
<span class="kwrd">Call</span> ErrorHandler.Log(cMODULE_NAME, cPROC_NAME, Err.description,)
</pre>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
All those code snippets must be added in the option... dialog box of the MZ-Tools add-in.</div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
Havin a module header and a procedure header for each module and procedure that you write will greatly enhance code maintainance.</div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
In addition I have also and Error Handling snippet. This use my ErrorHandler module.</div>
<div class="csharpcode">
The ErroHandler module is a piece of code I wrote to manage in a coherent way the error raised by an application.</div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
<div class="csharpcode">
<br /></div>
Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com1tag:blogger.com,1999:blog-1013473611626255361.post-18067526370588468652012-10-15T16:42:00.000+01:002012-10-15T16:42:53.729+01:00Property Set, Let, Get<br />
In VBA when we define the properties of an object we have a Let, Set and Get operators.<br />
We use the Get operator to retrieve the value of a property<br />
Whe use the Set operator to set the value of an Object<br />
We use the Let operator to set the value of a variable (not an object : string, double, enumeration...)<br />
<br />
The use of the Set operator, force you to use the Set function when setting the value of an object property of type object.<br />
<br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Private</span> <span class="kwrd">Const</span> cMODULE_NAME <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"House"</span><span class="kwrd">
Private</span> mAddress <span class="kwrd">As</span> <span class="kwrd">String</span><span class="kwrd">
Private</span> mDoors <span class="kwrd">As</span> Collection
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Address() <span class="kwrd">As</span> <span class="kwrd">String</span>
Address = mAddress
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Address(value <span class="kwrd">As</span> <span class="kwrd">String</span>)
mAddress = value
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Doors() <span class="kwrd">As</span> Collection
<span class="kwrd">Set</span> Doors = mDoors
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Set</span> Doors(value <span class="kwrd">As</span> Collection)
<span class="kwrd">Set</span> mDoors = value
<span class="kwrd">End</span> <span class="kwrd">Property</span></pre>
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-4198708224443467812012-10-15T15:57:00.000+01:002012-10-15T15:58:05.635+01:00Buttons and spreadsheet duplicaton with Excel VBAIf you want to add Button on an excel spreadsheet you have two choice<br />
<br />
1) Active-x buttons<br />
2) Form buttons<br />
<br />
<br />
I you plan to duplicate a spreadsheet whic contains buttons using VBA Code, than you must use<br />
Form buttons not Active-x ones.<br />
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.<br />
<br />
So the tip here is<br />
Use Form buttons if you plan to duplicat using VBA code to duplicate excel spread-sheets that contains them.<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-12450126511673707312012-10-15T15:13:00.001+01:002012-10-15T15:13:30.736+01:00Using implements behind an Excel worksheet functionSince 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.<br />
This would allow you to use polymorphically an Excel worksheet and could open-up differ possibilities.<br />
Howev this is my advice<br />
<br />
DO NOT USE IMPLEMENTS BEHING A WORKSHEET<br />
<br />
I have noticed that despites the code compiles, the overall worksheet becomes unstable and tend to crash!<br />
In additon the TypeOf function applied to the worksheet object that use the implements keyword, does not always behaves as you would expect.<br />
Ex:<br />
if you write on top of an excel worksheet module<br />
<br />
Implements IEngine<br />
<br />
<br />
Some time the test TypeOf sht is IEngine will return false even if it is implementing the interface.<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-84977693094552445822012-10-15T15:00:00.002+01:002012-10-15T15:02:01.394+01:00Strongly typed dictionary collection in VBAIn 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.<br />
<br />
The Generic Dictionary Object can be found in the "Microsoft Scripting Runtime" library.<br />
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. <br />
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.<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">For</span> <span class="kwrd">Each</span> key <span class="kwrd">In</span> emps.Keys
<span class="kwrd">Set</span> emp = emps(key)
Debug.Print emp.Name
<span class="kwrd">Next</span></pre>
<br />
The first class is a strongly typed dictionary of Employee object, called Employees.<br />
A usual to make the .Item property to be the default property you need to add the<br />
<br />
Attribute Item.VB_UserMmeId = 0<br />
<br />
just below the definition of the Get Item property<br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Private</span> <span class="kwrd">Const</span> cMODULE_NAME <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"Employees"</span><span class="kwrd">Private</span> mDic <span class="kwrd">As</span> Dictionary
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> Class_Initialize()
<span class="kwrd">Set</span> mDic = <span class="kwrd">New</span> Dictionary
<span class="kwrd">End</span> <span class="kwrd">Sub</span><span class="kwrd">
Private</span> <span class="kwrd">Sub</span> Class_Terminate()
<span class="kwrd">Set</span> mDic = <span class="kwrd">Nothing</span><span class="kwrd">
End</span> <span class="kwrd">Sub</span><span class="kwrd">
Public</span> <span class="kwrd">Sub</span> Add(key <span class="kwrd">As</span> <span class="kwrd">Variant</span>, Item <span class="kwrd">As</span> Employee)
<span class="kwrd">Call</span> mDic.Add(key, Item)
<span class="kwrd">End</span> <span class="kwrd">Sub</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Item(key <span class="kwrd">As</span> <span class="kwrd">Variant</span>) <span class="kwrd">As</span> Employee
<span class="rem">'Attribute Item.VB_UserMemId = 0</span>
<span class="rem">'This Attribute makes Item the default property</span>
<span class="rem">'In VBA, uncomment the first line. Export, Remove and import the file again. To make it work</span>
<span class="kwrd">Set</span> Item = mDic.Item(key)
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> count() <span class="kwrd">As</span> <span class="kwrd">Long</span>
count = mDic.count
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> Exists(key <span class="kwrd">As</span> <span class="kwrd">Variant</span>) <span class="kwrd">As</span> <span class="kwrd">Boolean</span>
Exists = mDic.Exists(key)
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> items() <span class="kwrd">As</span> <span class="kwrd">Variant</span>
items = mDic.items
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> Remove(key <span class="kwrd">As</span> <span class="kwrd">Variant</span>)
mDic.Remove (key)
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> RemoveAll()
mDic.RemoveAll
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">
Public</span> <span class="kwrd">Function</span> Keys() <span class="kwrd">As</span> <span class="kwrd">Variant</span>
Keys = mDic.Keys
<span class="kwrd">End</span> <span class="kwrd">Function</span></pre>
<br />
<br />
This is the the Employee Class<br />
<br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Private</span> <span class="kwrd">Const</span> cMODULE_NAME <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"Employee"</span><span class="kwrd">
Private</span> mIdentifier <span class="kwrd">As</span> <span class="kwrd">Long</span><span class="kwrd">
Private</span> mName <span class="kwrd">As</span> <span class="kwrd">String</span><span class="kwrd">
Private</span> mAge <span class="kwrd">As</span> <span class="kwrd">Long</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Identifier() <span class="kwrd">As</span> <span class="kwrd">Long</span>
Identifier = mIdentifier
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Identifier(value <span class="kwrd">As</span> <span class="kwrd">Long</span>)
mIdentifier = value
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Name() <span class="kwrd">As</span> <span class="kwrd">String</span>
Name = mName
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Name(value <span class="kwrd">As</span> <span class="kwrd">String</span>)
mName = value
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Age() <span class="kwrd">As</span> <span class="kwrd">Long</span>
Age = mAge
<span class="kwrd">End</span> <span class="kwrd">Property</span><span class="kwrd">
Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Age(value <span class="kwrd">As</span> <span class="kwrd">Long</span>)
mAge = value
<span class="kwrd">End</span> <span class="kwrd">Property</span></pre>
<br />
This is the Sub to test the Code<br />
<br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">Sub</span> TestCollection()
<span class="kwrd">Dim</span> emp <span class="kwrd">As</span> Employee
<span class="kwrd">Dim</span> emps <span class="kwrd">As</span> Employees
<span class="kwrd">Dim</span> key <span class="kwrd">As</span> <span class="kwrd">Variant</span><span class="kwrd">Set</span> emps = <span class="kwrd">New</span> Employees
<span class="kwrd">Set</span> emp = <span class="kwrd">New</span> Employee
emp.Identifier = 1
emp.Name = <span class="str">"Mario"</span>
emp.Age = 34
<span class="kwrd">Call</span> emps.Add(emp.Identifier, emp)
<span class="kwrd">Set</span> emp = <span class="kwrd">New</span> Employee
emp.Identifier = 2
emp.Name = <span class="str">"Gino"</span>
emp.Age = 12
<span class="kwrd">Call</span> emps.Add(emp.Identifier, emp)
<span class="kwrd">For</span> <span class="kwrd">Each</span> key <span class="kwrd">In</span> emps.Keys
<span class="kwrd">Set</span> emp = emps(key)
Debug.Print emp.Name
<span class="kwrd">Next</span><span class="kwrd">End</span> <span class="kwrd">Sub</span></pre>
Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com3tag:blogger.com,1999:blog-1013473611626255361.post-92223445294311642352012-10-15T12:13:00.002+01:002012-10-15T12:13:12.315+01:00Environ. A useful function to get environment infosThe Environ function is a pretty useful VBA function that gives you back many important info about the pc.<br />
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.<br />
<br />
One of the most useful are<br />
<br />
USERNAME<br />
APPDATA<br />
CommonProgramFiles<br />
<br />
<br />
<!-- code formatted by http://manoli.net/csharpformat/ --><br />
<pre class="csharpcode"><span class="kwrd">Public</span> <span class="kwrd">Sub</span> EnvironParameters()
<span class="kwrd">Dim</span> nCount <span class="kwrd">As</span> <span class="kwrd">Integer</span>
nCount = 0
nCount = nCount + 1
<span class="kwrd">Do</span> <span class="kwrd">Until</span> Environ(nCount) = <span class="str">""</span>
Debug.Print Environ(nCount)
nCount = nCount + 1
<span class="kwrd">Loop</span>
<span class="kwrd">End</span> Sub</pre>
<br />
This is the complete list of parameters<br />
<br />
ALLUSERSPROFILE<br />
<br />
APPDATA<br />
CommonProgramFiles<br />
COMPUTERNAME<br />
ComSpec<br />
FP_NO_HOST_CHECK<br />
HOMEDRIVE<br />
HOMEPATH<br />
HOMESHARE<br />
LOGONSERVER<br />
NUMBER_OF_PROCESSORS<br />
OS<br />
Path<br />
PATHEXT<br />
PROCESSOR_ARCHITECTURE<br />
PROCESSOR_IDENTIFIER<br />
PROCESSOR_LEVEL<br />
PROCESSOR_REVISION<br />
ProgramFiles<br />
PSModulePath<br />
SESSIONNAME<br />
SystemDrive<br />
SystemRoot<br />
TEMP<br />
TMP<br />
TNS_ADMIN<br />
UATDATA<br />
USERDNSDOMAIN<br />
USERDOMAIN<br />
USERNAME<br />
USERPROFILE<br />
VS90COMNTOOLS<br />
WecVersionForRosebud.224<br />
windir<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-25346483006008674132012-08-16T14:47:00.000+01:002012-08-16T14:47:45.124+01:00VB6 IsMissing() and optional arguments<br />
<br />
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.<br />
<br />
The important point is that IsMissing only work for optional argurment of Variant Type.<br />
If the optional argument is not of variant type, you should not use the IsMissing() function.<br />
In this case always supply a default value, even if the compiler does not require it.<br />
<br />
<br />
<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com1tag:blogger.com,1999:blog-1013473611626255361.post-70923090828671280242012-08-13T21:51:00.001+01:002012-08-13T22:01:45.406+01:00VBA / VB6 Interface implementationFew months ago I blogged about interface implementation in VB6. You can find the post <a href="http://productivebytes.blogspot.ie/2012/04/interface-implementation-in-vba-and-vb6.html" target="_blank">here</a> <br />
However, I discovered that the code had a memory leak caused by a circulare reference.<br />
You can find a solution here.<br />
<br />
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.<br />
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<br />
I will show you a very easy example on how to use it in VBA.<br />
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. <br />
<br />
Dim inst as IInstrument<br />
Dim sec as Security<br />
Dim fn as Fund<br />
<br />
Set sec = new Security<br />
Set fn = new Fund<br />
<br />
Set inst = sec<br />
Set inst = fn<br />
<br />
As you can see, bot a security and a fund can be assigned to an Instruments object!<br />
We first define a Class called IInstrument. The code is here<br />
<br />
<pre class="csharpcode"><span class="rem">'This is an Interface for the Generic Financial Instrument</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="rem">'Only Signature</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Id(value <span class="kwrd">As</span> <span class="kwrd">String</span>)
<span class="rem">'Only Signature</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
</pre>
<br />
<br />
We now create a new Class called Security that Implements the IInstrument one. This is a bit more tricky.<br />
Once we implement an Interface, the Class that implements it in VB6 will declare those method as Private like that.<br />
<br />
Private Property Get IInstrument_Id() As String<br />
IInstrument_Id = mId<br />
End Property<br />
<br />
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<br />
<br />
<br />
3) I implement the interface methods as I normally do. a declare a module level variable mId<br />
<br />
Private Property Get IInstrument_Id() As String <br />
<br />
IInstrument_Id = mId <br />
End Property<br />
<br />
Private Property Let IInstrument_Id(value As String)<br />
mId =value<br />
End Property<br />
<br />
4) I crate public properties / methods mirroring the interface delegating their implementation to the <br />
mInstrument object<br />
Public Property Get Id() As String<br />
<br />
Id =IInstrument_Id<br />
End Property<br />
<br />
Public Property Let Id(value As String)<br />
IInstrument_Id = value<br />
End Property<br />
<br />
Here you can find the Security Class Code<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Implements</span> IInstrument
<span class="kwrd">Private</span> mId <span class="kwrd">As</span> <span class="kwrd">String</span>
Public Ticker as String
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> Class_Initialize()
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> IInstrument_Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
IInstrument_Id = mId
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Private</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> IInstrument_Id(value <span class="kwrd">As</span> <span class="kwrd">String</span>)
mId = value
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="rem">'Public Interface</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
Id = IInstrument_Id
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Id(value <span class="kwrd">As</span> <span class="kwrd">String</span>)
IInstrument_Id = value
<span class="kwrd">End</span> <span class="kwrd">Property</span>
</pre>
We can now test the code<br />
<br />
<pre class="csharpcode"><span class="kwrd">Sub</span> TestSecurity()
<span class="kwrd">Dim</span> Sec1 <span class="kwrd">As</span> Security
<span class="kwrd">Dim</span> Inst <span class="kwrd">As</span> IInstrument
<span class="kwrd">Dim</span> Sec2 <span class="kwrd">As</span> Security
<span class="kwrd">Set</span> Sec1 = <span class="kwrd">New</span> Security
Sec1.Id = 10
Sec1.Ticker = <span class="str">"MXEU"</span>
<span class="kwrd">Set</span> Inst = Sec1 <span class="rem">'Upcast: A Security in an Instruments</span>
Debug.Print Inst.Id
<span class="rem">'DownCast, this should have been done explicit, but VBA does not support CType.</span>
<span class="rem">'VB6 does. So instead of CType(Inst, "Security") we can do</span>
<span class="kwrd">If</span> TypeName(Inst) = <span class="str">"Security"</span> <span class="kwrd">Then</span>
<span class="kwrd">Set</span> Sec2 = Inst
<span class="kwrd">End</span> <span class="kwrd">If</span>
<span class="kwrd">Set</span> Sec2 = Inst
Debug.Print Sec2.Id
Debug.Print Sec2.Ticker
<span class="kwrd">End</span> Sub</pre>
Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com2tag:blogger.com,1999:blog-1013473611626255361.post-19951858262100159142012-08-09T12:10:00.002+01:002012-08-09T12:12:09.236+01:00VBA Strongly typed collectionsAn Excel File with the sample code can be found <a href="https://sites.google.com/site/bypaparo/storage/Collection%20Examples.xlsm?attredirects=0&d=1" target="_blank">here</a><br />
<br />
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.<br />
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth <br />
<br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Dim</span> mName <span class="kwrd">As</span> <span class="kwrd">String
</span><span class="kwrd">Dim</span> mSurname <span class="kwrd">As</span> <span class="kwrd">String
</span><span class="kwrd">Dim</span> mDateofBirth <span class="kwrd">As</span> <span class="kwrd">Date
</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Name() <span class="kwrd">As</span> <span class="kwrd">String</span>
Name = mName
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Name(strName <span class="kwrd">As</span> <span class="kwrd">String</span>)
mName = strName
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Surname() <span class="kwrd">As</span> <span class="kwrd">String</span>
Surname = mSurname
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Surname(strSurname <span class="kwrd">As</span> <span class="kwrd">String</span>)
mSurname = strSurname
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> DateOfBirth() <span class="kwrd">As</span> <span class="kwrd">Date</span>
DateOfBirth = mDateofBirth
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> DateOfBirth(dteDateofBirth <span class="kwrd">As</span> <span class="kwrd">Date</span>)
mDateofBirth = dteDateofBirth
<span class="kwrd">End</span> <span class="kwrd">Property</span> </pre>
<pre class="csharpcode"> </pre>
<pre class="csharpcode"> </pre>
<br />
Then we can create the Collection Class called People<br />
<br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="rem">'This is going to be a stroingly type Collection</span>
Private mCol <span class="kwrd">As</span> Collection
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> Class_Initialize()
<span class="kwrd">Set</span> mCol = <span class="kwrd">New</span> Collection
<span class="kwrd">End</span> <span class="kwrd">Sub</span><span class="kwrd">
Private</span> <span class="kwrd">Sub</span> Class_Terminate()
<span class="kwrd">Set</span> mCol = <span class="kwrd">Nothing</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Property</span> <span class="kwrd">Get</span> Item(Index <span class="kwrd">As</span> <span class="kwrd">Variant</span>) <span class="kwrd">As</span> Person
<span class="rem">'Attribute Item.VB_UserMemId = 0</span>
<span class="rem">'This Attribute makes Item the default property</span>
<span class="kwrd">Set</span> Item = mCol.Item(Index)
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Property</span> <span class="kwrd">Get</span> NewEnum() <span class="kwrd">As</span> IUnknown
<span class="rem">'Attribute NewEnum.VB_UserMemId = -4</span>
<span class="rem">'Attribute NewEnum.VB_MemberFlags = "40"</span>
<span class="rem">'The first Attribute makes it the Default Enumerator Property</span>
<span class="rem">'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense</span>
<span class="rem">'This Routine Get the Enumerator for the Collection.</span>
<span class="rem">'To get this to work you must add two attributes</span>
<span class="kwrd">Set</span> NewEnum = mCol.[_NewEnum]
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Add(Item <span class="kwrd">As</span> Person, <span class="kwrd">Optional</span> key <span class="kwrd">As</span> <span class="kwrd">Variant</span>)
<span class="kwrd">Call</span> mCol.Add(Item, key)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Function</span> Count() <span class="kwrd">As</span> <span class="kwrd">Long</span>
Count = mCol.Count
<span class="kwrd">End</span> <span class="kwrd">Function</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Remove(Index <span class="kwrd">As</span> <span class="kwrd">Variant</span>)
mCol.Remove (Index)
<span class="kwrd">End</span> <span class="kwrd">Sub</span></pre>
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:<br />
<br />
Property Get Item(Index As Variant) As Parameter<br />
Attribute Item.VB_UserMemId = 0 <br />
<br />
Set Item = m_Collection.Item(Index)<br />
End Property<br />
<br />
Property Get NewEnum() As IUnknown <br />
<br />
Attribute NewEnum.VB_UserMemId = -4 <br />
Attribute NewEnum.VB_MemberFlags = "40" <br />
<br />
Set NewEnum = Me.mCollection.[_NewEnum]<br />
<br />
End Property<br />
<br />
Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.<br />
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.<br />
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.<br />
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)<br />
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.<br />
<br />
The solution is to<br />
(1) Make your Class<br />
(2) SAVE<br />
(3) Export the Class<br />
(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")<br />
(5) Manually add the Attribute Lines as shown above<br />
(6) Re-Import the edited Class<br />
<br />
An easier way, as one of my reader pointed out is<br />
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it<br />
2) Export the Class<br />
3) Remove the Class<br />
4) Reinport it again.<br />
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 <a class="kLink" href="http://www.xtremevbtalk.com/archive/index.php/t-146131.html#" id="KonaLink1" style="position: static; text-decoration: underline !important;" target="undefined"><span style="color: blue; font-family: verdana,arial,sans-serif; font-size: 11px; font-weight: 400; position: static;"><span class="kLink" style="color: blue; font-family: verdana,arial,sans-serif; font-size: 11px; font-weight: 400; position: relative;">VBA</span></span></a>, it will just be quietly ignored. So there's no reason to bother doing this, really.)<br />
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...<br />
<br />
This is the Code you can use to test the Class <br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Sub</span> prova()
<span class="kwrd">Dim</span> Employee <span class="kwrd">As</span> Person
<span class="kwrd">Dim</span> Director <span class="kwrd">As</span> Person
<span class="kwrd">Dim</span> Team <span class="kwrd">As</span> People
<span class="kwrd">Dim</span> p <span class="kwrd">As</span> Person
<span class="kwrd">Dim</span> i <span class="kwrd">As</span> <span class="kwrd">Long</span><span class="kwrd">Set</span> Employee = <span class="kwrd">New</span> Person
Employee.DateOfBirth = <span class="str">"10 Jan 1974"</span>
Employee.Name = <span class="str">"Mario"</span>
Employee.Surname = <span class="str">"Rossi"</span><span class="kwrd">Set</span> Director = <span class="kwrd">New</span> Person
Director.DateOfBirth = <span class="str">"10 Mar 1970"</span>
Director.Name = <span class="str">"Giulia"</span>
Director.Surname = <span class="str">"Verdi"</span><span class="kwrd">Set</span> Team = <span class="kwrd">New</span> People
<span class="kwrd">Call</span> Team.Add(Employee)
<span class="kwrd">Call</span> Team.Add(Director)
<span class="kwrd">For</span> i = 1 <span class="kwrd">To</span> Team.Count
Debug.Print Team(i).Name
<span class="kwrd">Next</span> i
<span class="kwrd">For</span> <span class="kwrd">Each</span> p <span class="kwrd">In</span> Team
Debug.Print p.Name
<span class="kwrd">Next</span><span class="kwrd">End</span> <span class="kwrd">Sub</span></pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com2tag:blogger.com,1999:blog-1013473611626255361.post-16557724165505441592012-07-24T13:06:00.000+01:002012-07-24T13:06:47.111+01:00C# Express Tools OptionsHere are some usefull Options to tick in the C# 2010 Options Dialog Box<br />
<br />
<strong>To Show the Configuration and Platform options in the Project property Debug Window</strong><br />
Projects and Solutions - General - Show Advance build configurations<br />
<br />
<br />
<strong>To redirect output to the immediate window</strong><br />
Debbugging - General - Redirect all output window text to the Immediage window<br />
<br />
<strong>How to change the Exception Handling Options</strong><br />
Tools - Customize - Commands<br />
Menu bar: Debug<br />
Click where you want to put the new Command, in the controls dialog box<br />
Add Command, Debug, Exceptions...<br />
Ok, Close<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-33016846466847179152012-07-20T22:46:00.000+01:002012-07-20T22:58:01.323+01:00Excel Tip of the Day: INDIRECTThe INDIRECT function is a pretty hard function to understand at first glance.
However all you need to know is this<br />
<br />
1) It converts a string into a cell Reference<br />
2) It does not work with named formula<br />
3) INDIRECT is a volatile function<br />
4) It is often used in conjuction with the function ADDRESS<br />
<br />
<br />
<b> 1) It converts a string into a cell Reference</b><br />
<br />
<br />
=INDIRECT("A1")<br />
<br />
Is equivalent to a foruma =A1<br />
<br />
=INDIRECT("TblOrders")<br />
<br />
it gives you back a reference to the TblOrders Table. This is Equivalent to a formula =TblOrders<br />
The advantage is that you can form the string using formulas to make dyamically build reference to table objects<br />
<br />
<br />
<b>2) INDIRECT does not work with named formula</b><br />
<br />
if you have a named formula like myrange =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)<br />
and then you use =INDIRECT("myrange") this will not be equivalent to =myrange.<br />
<br />
However if you do something like myrange = $A$3:$C$10<br />
and then you type =INDIRECT("myrange") this will work fine and will be equal to =myrange<br />
<br />
This means that if you are trying to use in a list validation INDIRECT(C3) where C3="mylist" and<br />
mylist = OFFSET($A$1,1,0,COUNTA($A:$A)-1,1), <br />
this dynamic validation procedure will fail - INDIRECT is poiting to a named formula<br />
<br />
You will instead need to use something like<br />
<br />
C3 = "mylistheader", whe mylistheader is a named cell,<br />
mylistheader = $A$1.<br />
mylistheadeCol = $A:$A<br />
<br />
OFFSET(INDIRECT(C3),0,0,COUNTA(INDIRECT(A1&"Col")),1)<br />
<br />
INDIRECT(C3) = a referece to $A$1<br />
COUNTA = will count the name in the list<br />
<br />
This will work just fine<br />
<br />
<b>3) INDIRECT is a volatile function</b><br />
<br />
This mean that Excel recomputes it each time it recalculate the spread sheet. It make the spreadsheet very heavy. Use it sparingly.<br />
<br />
<b>4) It is often used in conjuction with the function ADDRESS</b><br />
to dynamically build range reference <br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-13040226429790785022012-07-16T14:46:00.001+01:002012-07-17T10:09:25.928+01:00Exposing COM Collection With EventsThis is a piece of code that shows you how to expose COM Collection with Events in C#.<br />
<a href="http://www.productivebytes.blogspot.ie/2012/07/exposing-com-events.html" target="_blank">here</a> you can find the code for the Person Class.<br />
<br />
There is only one problem with this code. If you declase the class with Event you need to handle it, i.e you need to define in VBA the event sub. You can just put some empty code inside it. You can see onother interesting post <a href="http://blogs.msdn.com/b/murat/archive/2008/11/20/exposing-com-events-c.aspx" target="_blank">here at murat</a><br />
<br />
UPDATE: I have added a try cach statment to the code to sort the problem mentioned above.<br />
When you declare an Object WithEvents in VBA, the Event in C# will not be null, so the != will not work.<br />
This is why I have protected the code with a try, catch, statement.<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Text;
<span class="kwrd">using</span> System.Runtime.InteropServices;
<span class="kwrd">namespace</span> MyCOMEvents01
{
<span class="rem">// To expose properties and methods to COM, you must declare them on the class </span>
<span class="rem">// interface and mark them with a DispId attribute, and implement them in the class. </span>
<span class="rem">// The order in which the members are declared in the interface is the </span>
<span class="rem">// order used for the COM vtable.</span>
<span class="rem">// ex:</span>
<span class="rem">// [DispId(1)]</span>
<span class="rem">// void Init(string userid , string password);</span>
<span class="rem">// [DispId(2)]</span>
<span class="rem">// bool ExecuteSelectCommand(string selCommand);</span>
<span class="rem">//Class Interface</span>
[Guid(<span class="str">"09a22bef-9826-4ea6-8e12-83adbbc0efd1"</span>),
ComVisible(<span class="kwrd">true</span>),
InterfaceType(ComInterfaceType.InterfaceIsDual)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> IPerson
{
[DispId(1)]
<span class="kwrd">string</span> Id { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
[DispId(2)]
<span class="kwrd">string</span> Name { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
[DispId(3)]
<span class="kwrd">double</span> Age { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
}
<span class="rem">// To expose events from your class, you must declare them on the events </span>
<span class="rem">// interface and mark them with a DispId attribute. </span>
<span class="rem">// The class should not implement this interface. </span>
<span class="rem">//Events Interface</span>
[Guid(<span class="str">"94d63c5e-125e-4f7d-aa0a-0d62dd4dc4fd"</span>),
ComVisible(<span class="kwrd">true</span>),
InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> IPersonEvents
{
[DispId(101)]
<span class="kwrd">void</span> OnAfterNameChange(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> name);
[DispId(102)]
<span class="kwrd">void</span> OnBeforeNameChange(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> newName, <span class="kwrd">ref</span> <span class="kwrd">bool</span> cancel);
}
<span class="rem">//The Class can also implement other interfaces. But only</span>
<span class="rem">//the first one will be exposed to COM.</span>
<span class="rem">//COM Class do not support inheritance beyond interface implementation</span>
<span class="rem">//Class Employees : List<Employee> is not COM compatible</span>
<span class="rem">//Class Implement the Class Interface</span>
[Guid(<span class="str">"0836089b-7099-4c0d-be97-39a009d1a9ba"</span>),
ComVisible(<span class="kwrd">true</span>),
ClassInterface(ClassInterfaceType.None),
ComDefaultInterface(<span class="kwrd">typeof</span>(IPerson)),
ComSourceInterfaces(<span class="kwrd">typeof</span>(IPersonEvents)),
ProgId(<span class="str">"MyCOMEvents01.Person"</span>)]
<span class="kwrd">public</span> <span class="kwrd">class</span> Person : IPerson
{
[ComVisible(<span class="kwrd">false</span>)] <span class="rem">//Does not need to be visible to COM</span>
<span class="kwrd">public</span> <span class="kwrd">delegate</span> <span class="kwrd">void</span> OnAfterNameChangeHandler(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> name);
[ComVisible(<span class="kwrd">false</span>)] <span class="rem">//Does not need to be visible to COM</span>
<span class="kwrd">public</span> <span class="kwrd">delegate</span> <span class="kwrd">void</span> OnBeforeNameChangeHandler(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> newName, <span class="kwrd">ref</span> <span class="kwrd">bool</span> cancel);
<span class="kwrd">public</span> <span class="kwrd">event</span> OnAfterNameChangeHandler OnAfterNameChange;
<span class="kwrd">public</span> <span class="kwrd">event</span> OnBeforeNameChangeHandler OnBeforeNameChange;
<span class="kwrd">public</span> <span class="kwrd">string</span> Id { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
<span class="kwrd">private</span> <span class="kwrd">string</span> _Name;
<span class="kwrd">public</span> <span class="kwrd">string</span> Name
{
<span class="kwrd">get</span> { <span class="kwrd">return</span> _Name; }
<span class="kwrd">set</span>
{
<span class="kwrd">bool</span> cancel = <span class="kwrd">false</span>;
<span class="kwrd">if</span> (OnBeforeNameChange != <span class="kwrd">null</span>)
{
<span class="rem">//if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.</span>
<span class="rem">//So we must protect the code.</span>
<span class="kwrd">try</span> { OnBeforeNameChange(<span class="kwrd">this</span>, value.ToString(), <span class="kwrd">ref</span> cancel);}
<span class="kwrd">catch</span> (Exception){} <span class="rem">//Do Nothing</span>
}
<span class="kwrd">if</span> (cancel == <span class="kwrd">false</span>)
{
_Name = value;
<span class="kwrd">if</span> (OnAfterNameChange != <span class="kwrd">null</span>)
{
<span class="rem">//if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.</span>
<span class="rem">//So we must protect the code.</span>
<span class="kwrd">try</span> { OnAfterNameChange(<span class="kwrd">this</span>, _Name); }
<span class="kwrd">catch</span> (Exception){} <span class="rem">//Do Nothing</span>
}
}
}
}
<span class="kwrd">public</span> <span class="kwrd">double</span> Age { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
}
}
</pre>
<br />
<br />
And here the VBA code to test it. <br />
<br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Dim</span> <span class="kwrd">WithEvents</span> ps <span class="kwrd">As</span> MyCOMEvents01.Persons
<span class="kwrd">Sub</span> Test()
<span class="kwrd">Dim</span> p1 <span class="kwrd">As</span> MyCOMEvents01.Person
<span class="kwrd">Dim</span> p2 <span class="kwrd">As</span> MyCOMEvents01.Person
<span class="kwrd">Dim</span> key <span class="kwrd">As</span> <span class="kwrd">Variant</span>
<span class="kwrd">Set</span> p1 = <span class="kwrd">New</span> MyCOMEvents01.Person
<span class="kwrd">Set</span> p2 = <span class="kwrd">New</span> MyCOMEvents01.Person
<span class="kwrd">Set</span> ps = <span class="kwrd">New</span> MyCOMEvents01.Persons
p1.ID = 1
p1.Name = <span class="str">"Mario"</span>
p2.ID = 2
p2.Name = <span class="str">"Pluto"</span>
<span class="kwrd">Call</span> ps.Add(p1.ID, p1)
<span class="kwrd">Call</span> ps.Add(p2.ID, p2)
<span class="kwrd">For</span> <span class="kwrd">Each</span> key <span class="kwrd">In</span> ps
Debug.Print ps(key).Name
<span class="kwrd">Next</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> ps_OnPersonAdd(<span class="kwrd">ByVal</span> sender <span class="kwrd">As</span> <span class="kwrd">Variant</span>)
Debug.Print <span class="str">"Added"</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
</pre>
<a href="http://www.blogger.com/"></a><span id="goog_1756809187"></span><span id="goog_1756809188"></span>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-74254203890078201422012-07-16T12:35:00.003+01:002012-07-17T10:11:50.679+01:00Exposing COM EventsHi,<br />
This is a quick sample code that shows you how to expose events to COM.<br />
Just remember to "register the assembly for COM interop" and please do not tick "Make Assembly COM Visible". We use the ComVisible attribute to decise what to make visible for COM interop.<br />
<br />
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Text;
<span class="kwrd">using</span> System.Runtime.InteropServices;
<span class="kwrd">namespace</span> MyCOMEvents01
{
<span class="rem">// To expose properties and methods to COM, you must declare them on the class </span>
<span class="rem">// interface and mark them with a DispId attribute, and implement them in the class. </span>
<span class="rem">// The order in which the members are declared in the interface is the </span>
<span class="rem">// order used for the COM vtable.</span>
<span class="rem">// ex:</span>
<span class="rem">// [DispId(1)]</span>
<span class="rem">// void Init(string userid , string password);</span>
<span class="rem">// [DispId(2)]</span>
<span class="rem">// bool ExecuteSelectCommand(string selCommand);</span>
<span class="rem">//Class Interface</span>
[Guid(<span class="str">"09a22bef-9826-4ea6-8e12-83adbbc0efd1"</span>),
ComVisible(<span class="kwrd">true</span>),
InterfaceType(ComInterfaceType.InterfaceIsDual)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> IPerson
{
[DispId(1)]
<span class="kwrd">string</span> Id { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
[DispId(2)]
<span class="kwrd">string</span> Name { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
[DispId(3)]
<span class="kwrd">double</span> Age { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
}
<span class="rem">// To expose events from your class, you must declare them on the events </span>
<span class="rem">// interface and mark them with a DispId attribute. </span>
<span class="rem">// The class should not implement this interface. </span>
<span class="rem">//Events Interface</span>
[Guid(<span class="str">"94d63c5e-125e-4f7d-aa0a-0d62dd4dc4fd"</span>),
ComVisible(<span class="kwrd">true</span>),
InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> IPersonEvents
{
[DispId(101)]
<span class="kwrd">void</span> OnAfterNameChange(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> name);
[DispId(102)]
<span class="kwrd">void</span> OnBeforeNameChange(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> newName, <span class="kwrd">ref</span> <span class="kwrd">bool</span> cancel);
}
<span class="rem">//The Class can also implement other interfaces. But only</span>
<span class="rem">//the first one will be exposed to COM.</span>
<span class="rem">//COM Class do not support inheritance beyond interface implementation</span>
<span class="rem">//Class Employees : List<Employee> is not COM compatible</span>
<span class="rem">//Class Implement the Class Interface</span>
[Guid(<span class="str">"0836089b-7099-4c0d-be97-39a009d1a9ba"</span>),
ComVisible(<span class="kwrd">true</span>),
ClassInterface(ClassInterfaceType.None),
ComDefaultInterface(<span class="kwrd">typeof</span>(IPerson)),
ComSourceInterfaces(<span class="kwrd">typeof</span>(IPersonEvents)),
ProgId(<span class="str">"MyCOMEvents01.Person"</span>)]
<span class="kwrd">public</span> <span class="kwrd">class</span> Person : IPerson
{
[ComVisible(<span class="kwrd">false</span>)] <span class="rem">//Does not need to be visible to COM</span>
<span class="kwrd">public</span> <span class="kwrd">delegate</span> <span class="kwrd">void</span> OnAfterNameChangeHandler(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> name);
[ComVisible(<span class="kwrd">false</span>)] <span class="rem">//Does not need to be visible to COM</span>
<span class="kwrd">public</span> <span class="kwrd">delegate</span> <span class="kwrd">void</span> OnBeforeNameChangeHandler(<span class="kwrd">object</span> sender, <span class="kwrd">string</span> newName, <span class="kwrd">ref</span> <span class="kwrd">bool</span> cancel);
<span class="kwrd">public</span> <span class="kwrd">event</span> OnAfterNameChangeHandler OnAfterNameChange;
<span class="kwrd">public</span> <span class="kwrd">event</span> OnBeforeNameChangeHandler OnBeforeNameChange;
<span class="kwrd">public</span> <span class="kwrd">string</span> Id { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
<span class="kwrd">private</span> <span class="kwrd">string</span> _Name;
<span class="kwrd">public</span> <span class="kwrd">string</span> Name
{
<span class="kwrd">get</span> { <span class="kwrd">return</span> _Name; }
<span class="kwrd">set</span>
{
<span class="kwrd">bool</span> cancel = <span class="kwrd">false</span>;
<span class="kwrd">if</span> (OnBeforeNameChange != <span class="kwrd">null</span>)
{
<span class="rem">//if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.</span>
<span class="rem">//So we must protect the code.</span>
<span class="kwrd">try</span> { OnBeforeNameChange(<span class="kwrd">this</span>, value.ToString(), <span class="kwrd">ref</span> cancel);}
<span class="kwrd">catch</span> (Exception){} <span class="rem">//Do Nothing</span>
}
<span class="kwrd">if</span> (cancel == <span class="kwrd">false</span>)
{
_Name = value;
<span class="kwrd">if</span> (OnAfterNameChange != <span class="kwrd">null</span>)
{
<span class="rem">//if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.</span>
<span class="rem">//So we must protect the code.</span>
<span class="kwrd">try</span> { OnAfterNameChange(<span class="kwrd">this</span>, _Name); }
<span class="kwrd">catch</span> (Exception){} <span class="rem">//Do Nothing</span>
}
}
}
}
<span class="kwrd">public</span> <span class="kwrd">double</span> Age { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
}
}
</pre>
<br />
You can find here a quick VBA code to test the classs<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Dim</span> <span class="kwrd">WithEvents</span> p <span class="kwrd">As</span> MyCOMEvents01.Person
<span class="kwrd">Sub</span> test()
<span class="kwrd">Set</span> p = <span class="kwrd">New</span> MyCOMEvents01.Person
p.Name = <span class="str">"Mario"</span>
p.Name = <span class="str">"Ciccio"</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> p_OnAfterNameChange(<span class="kwrd">ByVal</span> sender <span class="kwrd">As</span> <span class="kwrd">Variant</span>, <span class="kwrd">ByVal</span> Name <span class="kwrd">As</span> <span class="kwrd">String</span>)
Debug.Print Name
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> p_OnBeforeNameChange(<span class="kwrd">ByVal</span> sender <span class="kwrd">As</span> <span class="kwrd">Variant</span>, <span class="kwrd">ByVal</span> newName <span class="kwrd">As</span> <span class="kwrd">String</span>, cancel <span class="kwrd">As</span> <span class="kwrd">Boolean</span>)
<span class="kwrd">If</span> newName = <span class="str">"Ciccio"</span> <span class="kwrd">Then</span>
cancel = <span class="kwrd">True</span>
Debug.Print <span class="str">"Do not Change Name"</span>
<span class="kwrd">End</span> <span class="kwrd">If</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
</pre>
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-57415657749476592172012-06-30T14:38:00.001+01:002012-08-11T15:42:26.002+01:00Cross, Circular Reference in VBAParticular care should be used in VBA when we run into a cross-reference, also called circular-reference.<br />
<br />
Let's suppose that we have a collection Knots of Knot objects<br />
<br />
<pre class="csharpcode"><span class="kwrd">Dim</span> col <span class="kwrd">as</span> Knots
<span class="kwrd">Dim</span> n <span class="kwrd">as</span> knot
<span class="kwrd">Set</span> col = <span class="kwrd">new</span> Knots
<span class="kwrd">Set</span> n = <span class="kwrd">new</span> Knot
<span class="kwrd">Set</span> n.Parent = col
<span class="kwrd">Set</span> col = <span class="kwrd">Nothing</span>
</pre>
<br />
If we count the reference to the Knots object untill we reach the Set n.Parent = col line, we can see that the sum to 2.<br />
Both col and n.Parent refers to a Knots object in menory.<br />
The new keyword creates the object in memory, a brand new one. The Set n.Parent = Col make the counter to this reference to increment by 1. VBA keeps a counter of each object reference and it deallocates the momory used by it only if it reaches 0. Each time we use Set col = Nothing VBA reduce the counter by 1, but it will free the memory only when this counter reaches 0. <br />
So if we just set col = Nothing, we will fail to free the memory from the object. The Reference counter will be 1 instead of 0, so VBA will not free memory for it.<br />
If a collection of knots holds n knot objects, this collection will have n+1 reference and they all need to cleaned up to have the memory free from any leaks. <br />
To work around this problem we must make sure that each knot object set its parent property to nothing when is terminated. We can do this creating Terminate sub. For the Knots class we need to create a another Terminate methods that will loop each element of the collection to call the knot Terminate() sub.<br />
Please note that if instead we call Set Knot = Nothing, this will not clear the memory.<br />
<br />
<pre class="csharpcode"><span class="kwrd">'For the knot Class</span></pre>
<pre class="csharpcode"><span class="kwrd">Public</span> <span class="kwrd">Sub</span> Terminate()
<span class="kwrd"> Set Me</span>.Parent = <span class="kwrd">Nothing</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
'For the knots Class
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Terminate()
<span class="kwrd"> For</span> <span class="kwrd">Each</span> Knot <span class="kwrd">in</span> Knots</pre>
<pre class="csharpcode"> Call knot.Terminate() </pre>
<pre class="csharpcode"></pre>
<pre class="csharpcode"><span class="kwrd"> Next</span>
<span class="kwrd"> Set</span> mCol = <span class="kwrd">Nothing</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
</pre>
<br />
With the addition of these to Terminate Class events, we make sure that all reference to the Knots class coming from its items are terminate, so we don'have any memory leak. Please note that we need to explicitly call the Terminate() method of the knots class before setting knots = Nothing<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-46508017455601511972012-06-30T07:28:00.001+01:002012-07-14T14:32:30.087+01:00VBA Error HandlingIn my previous post I showed the difference between<br />
<br />
Break on All Errors<br />
Break in Class module<br />
Break on unhandled errors<br />
<br />
After looking at each of them we came to the conclusion that we should really use Break on unhandled errors as our default option. <a href="http://www.productivebytes.blogspot.it/2012/06/vba-and-vb6-debuggin-options.html" target="_blank">See my post</a><br />
We this option on, however we need to set up o more sofisticated approach to make our life easier.<br />
I looked into the problem and the best approach I could find is the one described in details in the book,<br />
Professional Excel Developers, Chapter 15, VBA Error Handling.<br />
The Error Handling system described there are two: the functin return value and the re-throw method.<br />
I will not go into the details of the two system in this post, I will just add few comments of mine and present you the main ideas.<br />
To start with, The main Vba keyword to deal with Error handling are<br />
<br />
1) The object Err<br />
2) On Error Goto Label<br />
3) On Error Resume Next<br />
4) Resume / Resume Next / Resume Label<br />
5) On Error Goto 0<br />
<br />
The Err object is a global object whose property are filled by Vba as an error occurs.<br />
Err.Number, Err.Source, Err.Descripton and Err.Raise are by far the most important ones.<br />
<br />
Each time we meet a Exit Sub, Exit Fucntion, Exit Property, End Sub, End Function, End Property, Resume and On Error statement the property of the object error are reset. So some time we want to be carefull and store then into some variables. <br />
<br />
<br />
An Error Handler is a labeled section of a procedure that will run as an error occur.<br />
<br />
The Only way into this part of the code is an error, the only way out of this code should be a resume statement. You can see an example here. <br />
<br />
<pre class="csharpcode"><span class="kwrd">Private</span> <span class="kwrd">Sub</span> MySub()
<span class="kwrd">On</span> <span class="kwrd">Error</span> <span class="kwrd">Goto</span> ErrorHandler
<span class="rem">'Some code goes here</span>
ExitProc:
<span class="kwrd">Exit</span> <span class="kwrd">Sub</span>
ErrorHandler:
<span class="rem">'Clean up code goes here</span>
<span class="kwrd">if</span> CentralErrorHandler(<span class="str">"Mymodule"</span>,<span class="str">"MySub"</span>) <span class="kwrd">Then</span>
<span class="kwrd">Stop</span>
<span class="kwrd">Resume</span>
<span class="kwrd">else</span>
Go <span class="kwrd">to</span> ExitProc
<span class="kwrd">End</span> <span class="kwrd">if</span>
<span class="kwrd">Exit</span> Sub</pre>
<br />
<br />
The second importan principle is the Single Exi Point. Eache time we write a procedure, we need to make sure that there is a single exit point. In this example is ExitProc.<br />
<br />
The call to the CentralErrorHandler funcion happens only when we cannot deal with the error within the code, so an exception must be raised. In my personal implementation I actually changed the name of the function from CentraErrorHandler to Exception.LogMe, or if you want Exception.Inizialize.<br />
<br />
The CentralErrorHandler function will be responsible for<br />
1) Log erros to a txt log file<br />
2) Activate or deactivate the Debug mode<br />
3) Show a message to the User is we are at an entry point or in Debug mode<br />
4) Re-raise the error is we are not at an entry point or we are not in Debug mode<br />
<br />
The call looks like<br />
<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Public</span> <span class="kwrd">Function</span> CentralErrorHandler(<span class="kwrd">module</span>,proc,entryPoint,showMessage) <span class="kwrd">as</span> boolean</pre>
<br />
module and proc tells the CentralErrorHandler what it the source of the error. In this case MyModule:MySub<br />
<br />
entryPoint tells it if we are at an entry point.<br />
showMessage tells it if we need a message displayed.<br />
<br />
<br />
The CentralErroHandler function looks like <br />
<br />
<pre class="csharpcode"><span class="kwrd">Public</span> <span class="kwrd">Function</span> CentralErrorHandler(<span class="kwrd">module</span>,proc,entrypoint,showmessage) <span class="kwrd">as</span> <span class="kwrd">boolean</span>
<span class="rem">'Store the variable of the Global Error message</span>
<span class="kwrd">Static</span> errMsg <span class="kwrd">as</span> <span class="kwrd">string</span>
errNum = Err.Num
errSource = Err.Source
errDes = Err.Description
<span class="rem">'We cannot allow errorn in the CentralErrorHandler</span>
<span class="kwrd">On</span> <span class="kwrd">Error</span> <span class="kwrd">Resume</span> <span class="kwrd">Next</span>
errFullSource = <span class="kwrd">module</span> & <span class="str">":"</span> & proc
errLogTxt = errFullSource & <span class="str">" "</span> & Err.Num & <span class="str">" "</span> & Err.Des
<span class="kwrd">if</span> len(errMsg) = 0 <span class="kwrd">Then</span> errMsg = Err.Description
<span class="rem">'Log the errLogTxt Error into a text file</span>
<span class="kwrd">if</span> entryPoint <span class="kwrd">OR</span> DebugMode <span class="kwrd">then</span>
<span class="kwrd">if</span> showMessage <span class="kwrd">Then</span> msgbox(errMsg)</pre>
<pre class="csharpcode"> errMsg = vbNullString </pre>
<pre class="csharpcode"> <span class="kwrd">else</span></pre>
<pre class="csharpcode"><span class="kwrd"> On Error Goto 0 </span>
Err.Raise errNum, errFullSource, errMsg</pre>
<pre class="csharpcode"> </pre>
<pre class="csharpcode"> <span class="kwrd">end</span> <span class="kwrd">if</span>
<span class="kwrd">End</span> Function</pre>
<br />
<br />
The idea is<br />
1) We store first the property of the Err Object, otherwise they will be reset by the call to Resume<br />
2) We create the new source code and txt to be logged<br />
3) We log the error to the file. We could add: if ToBeLog Then SaveToFile()<br />
4) the errMsg is Static, which means that we will show the original message<br />
5) If we are in DebugMode or at an EntyPoint show a message an reset the string<br />
6) Otherwise re-raise the error<br />
<br />
DebugMode is a boolean costant that tells the compiler if we are in DegubMode of not. We can define in at the module level that contains the global error handler.<br />
<br />
In few words: when we call the CentralErrorHanlder and we are at an entry point or Debugmode is true a msgbox is shown and the program stops and the errMsg string is cleared. (see the example above on how to call it)<br />
If the debug mode is false and we are not at entry point, a message is re-thrown, with the original Err.Num and Err.Description, but a new Source: MySub:MyModule.<br />
<br />
What is an entryPoint?<br />
An entry point is a point from which the user can start execution: menu button, worksheet events...<br />
<br />
For the System to work we need<br />
1) Any Entry point procedure must never call another entry point procedure. If two of them need to run the same code, we can move the code out to a non-entry level procedure<br />
If this happens, we have that the the entry point procedure called, will show a message rather than raising the error up to the caller.<br />
<br />
A special case are the Excel User define Functions, which I still need to made my mind up how to treat them.<br />
<br />
If we set an Excel UDF as EntryPoint = False and DebugMode = False than an error is re-thrown, so an Excel UDF must have EntryPoint = True<br />
<br />
If we set for an Excel UDF EntryPoint = True then as there is an error a msgbox will be diplayed. This is not compliant from what you would expect for an Excel UDF, and image what happens if we had to run hundreds of them.<br />
<br />
So we can have Excel UDF EntryPoint = True, showMessage = False.<br />
This gets better, no message anymore.<br />
But if we have 200,000 calls with an error we will log it 200,000 time in the txtfiles, whis is kind of inefficient.<br />
So we could have<br />
<br />
ExcelUDF EntryPoint = True, showMessage = False, LogTxt = False<br />
<br />
This is getting better, but as you see we have nearly turned off all the Central handling facilities!<br />
No message, no error re-thrown, no txt log. This begs the queston do we need a CentralErrorHandling at all for an Excel UDF?<br />
<br />
Lastly if we define an ExcelUDF EntryPoint = true, than we cannot call it from any other part of the code, which is pretty limiting.<br />
So what we can do it to move the code from the entry point to another internal function such that<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Public</span> <span class="kwrd">Function</span> MyUDFFunc() <span class="kwrd">as</span> <span class="kwrd">Variant</span>
MyUDFFunc = MyUDFFuncInternal()
<span class="kwrd">End</span> Function</pre>
<br />
<br />
<br />
So that we can set MyUDFFuncInternal EntryPoint = False, in such a case the msgbox will be shown only in Debugmode. EntryPoint = False, showMessage = True , LogTxt = False<br />
<br />
Then MyUDFFunc instead does not need any handler at all.<br />
If we are in DebugMode = True, all the debugging will happen in MyUDFFuncInternal, which will show a message and stop<br />
If we are in DebugMode = False, any error will be logged by MyUDFFuncInternal, whill will no show any message because DebugMode = False, EntryPoint = False. If MyUDFFuncInternal is successfull it will pass the value up to MyUDFFunc. If MyUDFFuncInternal will re-throw an error, MyUDFFunc will just show #N/A, becasue it will exit from execution straightaway.<br />
<br />
This could be the best solution: All the Excel UDF don't have any CentralErrorHandler at all, they just delegate the work at some internal fucntion, which wil full support the CentralErrorHandler approach.<br />
This will allow us to reuse them in code easily.<br />
I am still not completely convinced that this is the best approach though.<br />
<br />
UPDATE:<br />
After a quick chat with the author of Dailydose of excel, I came to realize that he does not use the Centra Error Handling either for Excel UDF. So my suggested soluiton is the best way I suggest to go.<br />
For Excel UDF no central error Handling. We delegate their functon to some Internal function that implements the CentralErrorHandling Approach. We can set for this functin ErrorLog = False, to prevent to log 100,000 or more calls to failing functions<br />
<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-90356433889040917812012-06-28T00:01:00.001+01:002012-06-28T00:01:41.782+01:00VBA and VB6 Debugging OptionsIn this blog post I will explore the VBA Debugging options.<br />
If you go to<br />
Tools - Options - General<br />
<br />
You will see the Error Trapping Options<br />
<br />
1) Break on All Errors<br />
2) Break in Class Modlue<br />
3) Bread on Unhandled Errors<br />
<br />
<br />
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.<br />
<br />
<b>1) Break on all errors. </b><br />
<br />
Caller is a Sub of Function <br />
It stops at all errors as soon as they occur: ex division by zero or Err.Raise<br />
<br />
Caller is an Excel UDF. <br />
The code will NOT STOP. It will just end execution at the point where the error is caused<br />
Excel will Return #VALUE!<br />
<br />
<br />
<b>2) Break in Class Module</b><br />
<br />
Caller is a Sub or Function<br />
It stops only on Unhandled errors. If it meets an Err.Raise in a Class module it will stop<br />
in any case.<br />
<br />
Caller is an Excel UDF<br />
It terminate only on Unhandled errors. If it meets an Err.Raise in a Class modue it will raise<br />
Err.num 440, irrespective or the error number raised. VERY STRANGE<br />
<br />
<b>3) Break on Unhandled Error</b>s<br />
Caller is a Sub or Functin or UDF.<br />
It stops only on Unhandler errors.<br />
<br />
<br />
As you can see Opton number 3 is the one that gives the most consistency, followed by option number 1 and<br />
finally option number 2.<br />
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.<br />
Option 2 is interesting especially when you are developing an ActiveX component and you want to stop the debugger in the class.<br />
<br />
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.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-87228615474660387282012-05-25T23:18:00.001+01:002012-05-25T23:20:09.365+01:00C# Emumeration with custom ToString method<br />
<br />
When using Enum in any language you want to have a method to pretty print them. A very easy way to accomplish this in C# is with extension methods.<br />
Here is a quick example<br />
<br />
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Linq;
<span class="kwrd">using</span> System.Text;
<span class="kwrd">namespace</span> RickysGuitars
{
<span class="kwrd">public</span> <span class="kwrd">enum</span> GuitarType
{
ACOUSTIC,
ELECTRIC
}
<span class="kwrd">public</span> <span class="kwrd">static</span> <span class="kwrd">class</span> GuitarTypeExtentions
{
<span class="kwrd">public</span> <span class="kwrd">static</span> <span class="kwrd">string</span> ToPrettyString(<span class="kwrd">this</span> GuitarType source)
{
<span class="kwrd">switch</span> (source)
{
<span class="kwrd">case</span> GuitarType.ACOUSTIC:
<span class="kwrd">return</span> <span class="str">"Acoustic Guitar"</span>;
<span class="kwrd">case</span> GuitarType.ELECTRIC:
<span class="kwrd">return</span> <span class="str">"Electric Guitar"</span>;
}
<span class="kwrd">return</span> <span class="kwrd">null</span>;
}
}
}
</pre>
<br />
<br />
An example of the method call would be<br />
<br />
<pre class="csharpcode">GuitarType a = GuitarType.ELECTRIC;
Debug.WriteLine(a.ToPrettyString());</pre>
<br />
which prints: Electric Guitar<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-85464576299192681642012-05-07T22:53:00.000+01:002012-05-07T22:54:10.650+01:00Example of a COM Dll developed in VB.NET with the COM templateYou can find here the <a href="https://sites.google.com/site/bypaparo/storage/TestCOMVisible01.zip?attredirects=0&d=1" target="_blank">code</a>. It is a VS2008 solution file. Just use the .vb classes if you don't have VS2008 to open up the solution<br />
<br />
I have already shown in my previous post how to create COM interop assembly in VB.NET and in C#.<br />
<br />
You can have a look <a href="http://www.productivebytes.blogspot.com/2012/05/developing-com-class-collection-in.html" target="_blank">here</a> and <a href="http://www.productivebytes.blogspot.com/2012/05/developing-com-class-collection-in.html" target="_blank">here</a>. For More more detailed info please look also <a href="http://www.productivebytes.blogspot.com/2010/05/develpiong-com-add-in-in-c.html" target="_blank">here</a><br />
where you will find plenty of details on the how COM dll development and deployment works.<br />
<br />
I will try to summarize some important point relevant to the VB.NET developer using the COM template here<br />
<br />
1) The COM template automatically ticks for you<br />
Compile / Register for COM interop <br />
Application / Assembly Infomation... / Make assembly COM Visible<br />
<br />
The second option is actually a bed idea, becuase it will register for COM all the types you declare in the assembly. If you have some assembly without the GUID it will create them for you generating a registry bloat.<br />
So each time you add a COM Template, go and untick Make assembly COM-Visible.<br />
Once you have done that you need to add <comvisible(true)> as class attributes. (see the code)</comvisible(true)><br />
<br />
2) If you define a Default Property, this will become a default property for your COM object as well. You can also have indexed properties. The ComClassAttribute will associate to it a DispId(0)<br />
<br />
3) if you define a GeEnumerator() function that return a IEnumerator than you will enable the For Each ... Next<br />
loop in VBA. ComClassAttribute will associate to it a DispId(-4)<br />
<br />
Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator<br />
End Function<br />
<br />
4) Also public events are exposed.<br />
<br />
<br />
The code will show you how to create a Collection with a default property and the For Each ... Next loop enabled and how to expose and event.<br />
<br />
Employee Class<br />
<br />
<pre class="csharpcode"><span class="kwrd">Imports</span> System.Runtime.InteropServices
<ComClass(Employee.ClassId, Employee.InterfaceId, Employee.EventsId), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Class</span> Employee
<span class="preproc">#Region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">' These GUIDs provide the COM identity for this class </span>
<span class="rem">' and its COM interfaces. If you change them, existing </span>
<span class="rem">' clients will no longer be able to access the class.</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> ClassId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"dd3ef2f6-261f-477d-af54-10abc39a07d9"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> InterfaceId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"a0680708-b5ca-4679-8e8e-1b012479b8ee"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> EventsId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"d7361527-7a80-4e47-9aff-4e603a26812b"</span>
<span class="preproc">#End Region</span>
<span class="rem">' A creatable COM class must have a Public Sub New() </span>
<span class="rem">' with no parameters, otherwise, the class will not be </span>
<span class="rem">' registered in the COM registry and cannot be created </span>
<span class="rem">' via CreateObject.</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> <span class="kwrd">New</span>()
<span class="kwrd">MyBase</span>.<span class="kwrd">New</span>()
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> _Name <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> Name() <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _Name
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">Set</span>(<span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">String</span>)
_Name = value
<span class="kwrd">End</span> <span class="kwrd">Set</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">End</span> <span class="kwrd">Class</span> </pre>
<pre class="csharpcode"> </pre>
<pre class="csharpcode"></pre>
<pre class="csharpcode"></pre>
<pre class="csharpcode"> </pre>
Employer Class<br />
<br />
<pre class="csharpcode"><span class="kwrd">Imports</span> System.Runtime.InteropServices
<ComClass(Employer.ClassId, Employer.InterfaceId, Employer.EventsId), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Class</span> Employer
<span class="preproc">#Region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">' These GUIDs provide the COM identity for this class </span>
<span class="rem">' and its COM interfaces. If you change them, existing </span>
<span class="rem">' clients will no longer be able to access the class.</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> ClassId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"a0513ce8-fac4-4187-8190-0584f59cda1e"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> InterfaceId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"2c55f846-2dc9-4f0f-9b82-5e16dfefee52"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> EventsId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"2f99d8e4-afe8-46ef-a4e0-d62b4db18a4d"</span>
<span class="preproc">#End Region</span>
<span class="rem">' A creatable COM class must have a Public Sub New() </span>
<span class="rem">' with no parameters, otherwise, the class will not be </span>
<span class="rem">' registered in the COM registry and cannot be created </span>
<span class="rem">' via CreateObject.</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> <span class="kwrd">New</span>()
<span class="kwrd">MyBase</span>.<span class="kwrd">New</span>()
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Event</span> OnNameChange(<span class="kwrd">ByRef</span> newName <span class="kwrd">As</span> <span class="kwrd">String</span>)
<span class="kwrd">Private</span> _Name <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> Name() <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _Name
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">Set</span>(<span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">String</span>)
<span class="kwrd">RaiseEvent</span> OnNameChange(value)
_Name = value
<span class="kwrd">End</span> <span class="kwrd">Set</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">End</span> Class</pre>
<br />
Collection Class<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Imports</span> System.Runtime.InteropServices
<ComClass(MyCol.ClassId, MyCol.InterfaceId, MyCol.EventsId), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Class</span> MyCol
<span class="kwrd">Implements</span> IEnumerable
<span class="preproc">#Region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">' These GUIDs provide the COM identity for this class </span>
<span class="rem">' and its COM interfaces. If you change them, existing </span>
<span class="rem">' clients will no longer be able to access the class.</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> ClassId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"994ba5ce-1301-455b-9334-409e28aea0c3"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> InterfaceId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"87280e58-8be8-40f8-8987-d3fac317c6c3"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> EventsId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"11d12ead-4562-4ab1-a04b-5ef7fa9fba4c"</span>
<span class="preproc">#End Region</span>
<span class="rem">' A creatable COM class must have a Public Sub New() </span>
<span class="rem">' with no parameters, otherwise, the class will not be </span>
<span class="rem">' registered in the COM registry and cannot be created </span>
<span class="rem">' via CreateObject.</span>
<span class="kwrd">Dim</span> _SortedList <span class="kwrd">As</span> SortedList
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> <span class="kwrd">New</span>()
<span class="kwrd">MyBase</span>.<span class="kwrd">New</span>()
_SortedList = <span class="kwrd">New</span> SortedList
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Default</span> <span class="kwrd">Public</span> <span class="kwrd">Property</span> Item(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList(key)
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">Set</span>(<span class="kwrd">ByVal</span> value)
_SortedList(key) = value
<span class="kwrd">End</span> <span class="kwrd">Set</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">ReadOnly</span> <span class="kwrd">Property</span> Count()
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList.Count
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Remove(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
_SortedList.Remove(key)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Add(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>, <span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">Object</span>)
_SortedList.Add(key, value)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Function</span> GetEnumerator() <span class="kwrd">As</span> System.Collections.IEnumerator <span class="kwrd">Implements</span> System.Collections.IEnumerable.GetEnumerator
<span class="rem">'Return _SortedList.GetEnumerator()</span>
<span class="kwrd">Dim</span> keys <span class="kwrd">As</span> ICollection = _SortedList.Keys
<span class="kwrd">Return</span> <span class="kwrd">CType</span>(keys.GetEnumerator, IEnumerator)
<span class="kwrd">End</span> <span class="kwrd">Function</span>
<span class="kwrd">End</span> <span class="kwrd">Class</span>
</pre>
VBA Code to test the class<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Option</span> Explicit
<span class="kwrd">Dim</span> <span class="kwrd">WithEvents</span> a <span class="kwrd">As</span> TestCOMVisible01.Employer
<span class="kwrd">Sub</span> prova()
<span class="kwrd">If</span> a <span class="kwrd">Is</span> <span class="kwrd">Nothing</span> <span class="kwrd">Then</span>
<span class="kwrd">Set</span> a = <span class="kwrd">New</span> TestCOMVisible01.Employer
<span class="kwrd">End</span> <span class="kwrd">If</span>
a.Name = <span class="str">"Gino"</span>
Debug.Print a.Name
<span class="kwrd">Dim</span> emp1 <span class="kwrd">As</span> <span class="kwrd">New</span> TestCOMVisible01.Employee
<span class="kwrd">Dim</span> emp2 <span class="kwrd">As</span> <span class="kwrd">New</span> TestCOMVisible01.Employee
<span class="kwrd">Dim</span> col <span class="kwrd">As</span> <span class="kwrd">New</span> TestCOMVisible01.MyCol
emp1.Name = <span class="str">"mario"</span>
emp2.Name = <span class="str">"pluto"</span>
<span class="kwrd">Call</span> col.Add(<span class="str">"1"</span>, emp1)
<span class="kwrd">Call</span> col.Add(<span class="str">"2"</span>, emp2)
<span class="kwrd">Dim</span> key <span class="kwrd">As</span> <span class="kwrd">Variant</span>
<span class="kwrd">For</span> <span class="kwrd">Each</span> key <span class="kwrd">In</span> col
Debug.Print col(key).Name
<span class="kwrd">Next</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> a_OnNameChange(newName <span class="kwrd">As</span> <span class="kwrd">String</span>)
newName = <span class="str">"ho cambiato il nome"</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
</pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-88201538747703010422012-05-07T16:39:00.000+01:002012-08-02T16:04:55.633+01:00Developing a COM Class Collection using VB.NET COM TemplateYou can find the code <a href="https://sites.google.com/site/bypaparo/storage/TestVBColLibrary.zip?attredirects=0&d=1" target="_blank">here</a> <br />
<br />
In this post I will show you how to develop a COM Class Collection in VB.NET using the COM Template.<br />
It is actually very easy, much easier that doing it manually. <a href="http://www.productivebytes.blogspot.com/2012/05/developing-com-class-collection-in.html" target="_blank">Here</a> you can see the manual procedure.<br />
<br />
The VB.NET ComClassAttribute, used by the COM Class template will generate for you automatically all the interface that you need to be exposed to COM.<br />
<br />
<br />
If you define a Default indexed property, it will make it the default property for the COM Object, i.e. it will associate a DispId(0) to the Default indexed property.<br />
<br />
In addition if you define a function <br />
<br />
Function GetEnumerator() as System.Collection.IEnumerator <br />
End Function<br />
<br />
it will mark it as DispId(-4) to make it usable for the VB6/VBA For Each ... Next loop.<br />
<br />
The COM Add-in will also create for you all the necessary GUID.<br />
<br />
As you create a COM Class using the template, the template automatically will tick for you<br />
1) Register for COM interop in Project Property/Compile/Register for COM Interop<br />
2) It will make the assembly COM Visible. It will tick Project Property/Application/Assembly Infomatin/Make Assemby COM Visible.<br />
<br />
The second part is usually a bad idea, this is because every type you include in the libray will be exported to COM. In case you do not provide some GUID for the type, each time you build the assembly the project will create some new ones for you, thus creating a dll hell.<br />
<br />
The best thing you can do is to Untick Make Assembly COM Visible (and do it every time you use the COM template) and add a <comvisible true="true">attribute </comvisible>ComVisible(true) <comvisible true="true"><comvisible true="true">on top of the class.</comvisible></comvisible><br />
<comvisible true="true">See an exampe <a href="http://www.productivebytes.blogspot.com/2012/05/example-of-com-dll-developed-in-vbnet.html" target="_blank">here</a> <br /><br /><br /></comvisible><br />
<pre class="csharpcode"><span class="kwrd">Imports</span> System.Collections
<span class="kwrd">Imports</span> System.Runtime.InteropServices
<ComClass(Employees.ClassId, Employees.InterfaceId, Employees.EventsId)> _
<span class="kwrd">Public</span> <span class="kwrd">Class</span> Employees
<span class="kwrd">Implements</span> System.Collections.IEnumerable
<span class="preproc">#Region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">' These GUIDs provide the COM identity for this class </span>
<span class="rem">' and its COM interfaces. If you change them, existing </span>
<span class="rem">' clients will no longer be able to access the class.</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> ClassId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"4999e186-4ea8-4ce1-8da4-12db6f8600e8"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> InterfaceId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"43ecbe2f-714b-4dc9-a76c-85a84320b66d"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> EventsId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"069d7776-4953-44c2-bd17-0ff75cb5748b"</span><span class="preproc">#End Region</span>
<span class="rem">' A creatable COM class must have a Public Sub New() </span>
<span class="rem">' with no parameters, otherwise, the class will not be </span>
<span class="rem">' registered in the COM registry and cannot be created </span>
<span class="rem">' via CreateObject.</span>
<span class="kwrd">Dim</span> _SortedList <span class="kwrd">As</span> SortedList
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> <span class="kwrd">New</span>()
<span class="kwrd">MyBase</span>.<span class="kwrd">New</span>()
_SortedList = <span class="kwrd">New</span> SortedList
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Default</span> <span class="kwrd">Public</span> <span class="kwrd">Property</span> Item(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList(key)
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">Set</span>(<span class="kwrd">ByVal</span> value)
_SortedList(key) = value
<span class="kwrd">End</span> <span class="kwrd">Set</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">ReadOnly</span> <span class="kwrd">Property</span> Count()
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList.Count
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Remove(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
_SortedList.Remove(key)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Add(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>, <span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">Object</span>)
_SortedList.Add(key, value)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Function</span> GetEnumerator() <span class="kwrd">As</span> System.Collections.IEnumerator <span class="kwrd">Implements</span> System.Collections.IEnumerable.GetEnumerator
<span class="rem">'Return _SortedList.GetEnumerator()</span>
<span class="kwrd">Dim</span> keys <span class="kwrd">As</span> ICollection = _SortedList.Keys
<span class="kwrd">Return</span> <span class="kwrd">CType</span>(keys.GetEnumerator, IEnumerator)
<span class="kwrd">End</span> <span class="kwrd">Function</span><span class="kwrd">End</span> <span class="kwrd">Class</span></pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-78742744961359919532012-05-07T16:28:00.000+01:002012-05-08T00:07:25.503+01:00Developing a COM Class Collection in VB.NET without using the COM Templateyou can find the code <a href="https://sites.google.com/site/bypaparo/storage/TestVBColLibraryManual.zip?attredirects=0&d=1" target="_blank">here</a> <br />
<br />
This is an example of a COM Class Collection written in VB.NET. <br />
<br />
You need to start a new project of type library, and set the project property Build -> Register for COM Interop<br />
Do not check: Application, Assembly Information, Make Class COM Visible.<br />
We are using the COMVisible attribute to decide which class is visible for us<br />
The class will have both a default property and an iterator. The iterator is exposed defining a public function.<br />
<br />
Function GetEnumerator() as IEnumerator<br />
End Function<br />
<br />
<br />
It is also a Good Idea having the class to implement IEnumerable<br />
<br />
Function GetEnumerator() as IEnumerator Implements IEnumerable.GetEnumerator<br />
<br />
End Function<br />
<br />
In order to get the new GUID use can either use the VB.NET COM template of the Tools- Create GUID tool.<br />
In addtion you can also use my C# Com template to start with, and translate the code with a C# to VB.NET tool.<br />
Other wise, you can just use the VB.NET COM tool. It is kind of easy to use and much faster<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Imports</span> System.Runtime.InteropServices
<span class="kwrd">Imports</span> System.Collections
<span class="rem">'Wee first define the interface of the Collection</span>
<Guid(<span class="str">"8beb176f-5357-4bb9-a5c1-38bdd0f7d3df"</span>), _
InterfaceType(ComInterfaceType.InterfaceIsDual), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Interface</span> INewEmployees
<span class="kwrd">Inherits</span> System.Collections.IEnumerable
<DispId(-4)> <span class="kwrd">Shadows</span> <span class="kwrd">Function</span> GetEnumerator() <span class="kwrd">As</span> IEnumerator <span class="rem">'Iterator</span>
<DispId(1)> <span class="kwrd">Sub</span> Add(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>, <span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">Object</span>)
<DispId(2)> <span class="kwrd">ReadOnly</span> <span class="kwrd">Property</span> Count()
<DispId(3)> <span class="kwrd">Sub</span> Remove(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
<DispId(0)> <span class="kwrd">Default</span> <span class="kwrd">Property</span> Item(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>)
<span class="kwrd">End</span> <span class="kwrd">Interface</span>
<span class="rem">'We define the event interface</span>
<Guid(<span class="str">"e96bda2f-596f-419b-840c-4bd165930c4d"</span>), _
InterfaceType(ComInterfaceType.InterfaceIsIDispatch), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Interface</span> INewEmployeesEvents
<span class="kwrd">End</span> <span class="kwrd">Interface</span>
<span class="rem">'<ComClass(NewEmployees.ClassId, NewEmployees.InterfaceId, NewEmployees.EventsId)> _</span>
<Guid(<span class="str">"67d85fea-43d6-457e-8db1-cc9601bdd9ec"</span>), _
ClassInterface(ClassInterfaceType.None), _
ComSourceInterfaces(<span class="kwrd">GetType</span>(INewEmployeesEvents)), _
ComDefaultInterface(<span class="kwrd">GetType</span>(INewEmployees)), _
ComVisible(<span class="kwrd">True</span>)> _
<span class="kwrd">Public</span> <span class="kwrd">Class</span> NewEmployees
<span class="kwrd">Implements</span> INewEmployees
<span class="preproc">#Region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">' These GUIDs provide the COM identity for this class </span>
<span class="rem">' and its COM interfaces. If you change them, existing </span>
<span class="rem">' clients will no longer be able to access the class.</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> ClassId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"67d85fea-43d6-457e-8db1-cc9601bdd9ec"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> InterfaceId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"8beb176f-5357-4bb9-a5c1-38bdd0f7d3df"</span>
<span class="kwrd">Public</span> <span class="kwrd">Const</span> EventsId <span class="kwrd">As</span> <span class="kwrd">String</span> = <span class="str">"e96bda2f-596f-419b-840c-4bd165930c4d"</span>
<span class="preproc">#End Region</span>
<span class="rem">' A creatable COM class must have a Public Sub New() </span>
<span class="rem">' with no parameters, otherwise, the class will not be </span>
<span class="rem">' registered in the COM registry and cannot be created </span>
<span class="rem">' via CreateObject.</span>
<span class="kwrd">Dim</span> _SortedList <span class="kwrd">As</span> SortedList
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> <span class="kwrd">New</span>()
<span class="kwrd">MyBase</span>.<span class="kwrd">New</span>()
_SortedList = <span class="kwrd">New</span> SortedList
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Default</span> <span class="kwrd">Public</span> <span class="kwrd">Property</span> Item(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>) <span class="kwrd">Implements</span> INewEmployees.Item
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList(key)
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">Set</span>(<span class="kwrd">ByVal</span> value)
_SortedList(key) = value
<span class="kwrd">End</span> <span class="kwrd">Set</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">ReadOnly</span> <span class="kwrd">Property</span> Count() <span class="kwrd">Implements</span> INewEmployees.Count
<span class="kwrd">Get</span>
<span class="kwrd">Return</span> _SortedList.Count
<span class="kwrd">End</span> <span class="kwrd">Get</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Remove(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>) <span class="kwrd">Implements</span> INewEmployees.Remove
_SortedList.Remove(key)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Sub</span> Add(<span class="kwrd">ByVal</span> key <span class="kwrd">As</span> <span class="kwrd">Object</span>, <span class="kwrd">ByVal</span> value <span class="kwrd">As</span> <span class="kwrd">Object</span>) <span class="kwrd">Implements</span> INewEmployees.Add
_SortedList.Add(key, value)
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Public</span> <span class="kwrd">Function</span> GetEnumerator() <span class="kwrd">As</span> System.Collections.IEnumerator <span class="kwrd">Implements</span> INewEmployees.GetEnumerator, System.Collections.IEnumerable.GetEnumerator
<span class="rem">'Return _SortedList.GetEnumerator()</span>
<span class="kwrd">Dim</span> keys <span class="kwrd">As</span> ICollection = _SortedList.Keys
<span class="kwrd">Return</span> <span class="kwrd">CType</span>(keys.GetEnumerator, IEnumerator)
<span class="kwrd">End</span> <span class="kwrd">Function</span>
<span class="kwrd">End</span> <span class="kwrd">Class</span>
</pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-15459951495650437622012-05-07T16:19:00.000+01:002012-05-07T16:19:40.866+01:00COM interop an interesting link<br />
Here you can find an interesting link about COM interop<br />
<br />
<a href="http://limbioliong.wordpress.com/2011/10/28/exposing-an-enumerator-from-managed-code-to-com/">http://limbioliong.wordpress.com/2011/10/28/exposing-an-enumerator-from-managed-code-to-com/</a><br />
<br />
This is one of his most important tips<br />
<br />
The interface type should be either :<br />
<br />
ComInterfaceType.InterfaceIsDual<br />
or ComInterfaceType.InterfaceIsIDispatch<br />
otherwise the GetEnumerator() method<br />
will not be marked with dispid -4.<br />
Without this dispid, this method<br />
will not be recognized by COM as<br />
returning an enumerator. It will not thus<br />
not be usable in a VB6.0 For Each Next<br />
statement.<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-7129023714158921282012-05-07T16:15:00.000+01:002012-05-07T16:15:18.473+01:00Example of a COM Class Collection Written in C#This is an example of a COM Class Collection written in C#.<br />
You can use as starting point my template or use the Tools - Create Guid Tool on VS 2008.<br />
You need to start a new project of type library, and set the project property Build -> Register for COM Interop<br />
Do not check: Application, Assembly Information, Make Class COM Visible.<br />
We are using the COMVisible attribute to decide which class is visible for us<br />
The class will have both a defaul property (the indexers) and an iterator<br />
<br />
<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Linq;
<span class="kwrd">using</span> System.Text;
<span class="kwrd">using</span> System.Runtime.InteropServices;
<span class="kwrd">using</span> System.Collections;
<span class="kwrd">namespace</span> TestEmployeesCol
{
<span class="rem">//Wee first define the interface of the Collection</span>
[Guid(<span class="str">"21C027E8-CF8C-4166-A63B-25D8E790F040"</span>), InterfaceType(ComInterfaceType.InterfaceIsDual), ComVisible(<span class="kwrd">true</span>)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> INewEmployees : System.Collections.IEnumerable
{
[DispId(-4)]
<span class="kwrd">new</span> IEnumerator GetEnumerator();
<span class="rem">//Iterator</span>
[DispId(1)]
<span class="kwrd">void</span> Add(<span class="kwrd">object</span> key, <span class="kwrd">object</span> value);
[DispId(2)]
<span class="kwrd">object</span> Count { <span class="kwrd">get</span>; }
[DispId(3)]
<span class="kwrd">void</span> Remove(<span class="kwrd">object</span> key);
[DispId(0)]
<span class="kwrd">object</span> <span class="kwrd">this</span>[<span class="kwrd">object</span> key] { <span class="kwrd">get</span>; <span class="kwrd">set</span>; }
}
<span class="rem">//We define the event interface</span>
[Guid(<span class="str">"5C6B8153-D2D6-4e98-80EF-D13A53CC9CDD"</span>), InterfaceType(ComInterfaceType.InterfaceIsIDispatch), ComVisible(<span class="kwrd">true</span>)]
<span class="kwrd">public</span> <span class="kwrd">interface</span> INewEmployeesEvents
{
}
<span class="rem">//<ComClass(NewEmployees.ClassId, NewEmployees.InterfaceId, NewEmployees.EventsId)> _</span>
[Guid(<span class="str">"1692DD4D-6F3E-4e77-AB50-5401F04306DC"</span>),
ClassInterface(ClassInterfaceType.None),
ComSourceInterfaces(<span class="kwrd">typeof</span>(INewEmployeesEvents)),
ComDefaultInterface(<span class="kwrd">typeof</span>(INewEmployees)),
ComVisible(<span class="kwrd">true</span>)]
<span class="kwrd">public</span> <span class="kwrd">class</span> NewEmployees : INewEmployees
{
<span class="preproc">#region</span> <span class="str">"COM GUIDs"</span>
<span class="rem">// These GUIDs provide the COM identity for this class </span>
<span class="rem">// and its COM interfaces. If you change them, existing </span>
<span class="rem">// clients will no longer be able to access the class.</span>
<span class="kwrd">public</span> <span class="kwrd">const</span> <span class="kwrd">string</span> ClassId = <span class="str">"1692DD4D-6F3E-4e77-AB50-5401F04306DC"</span>;
<span class="kwrd">public</span> <span class="kwrd">const</span> <span class="kwrd">string</span> InterfaceId = <span class="str">"21C027E8-CF8C-4166-A63B-25D8E790F040"</span>;
<span class="preproc">#endregion</span>
<span class="kwrd">public</span> <span class="kwrd">const</span> <span class="kwrd">string</span> EventsId = <span class="str">"5C6B8153-D2D6-4e98-80EF-D13A53CC9CDD"</span>;
<span class="rem">// A creatable COM class must have a Public Sub New() </span>
<span class="rem">// with no parameters, otherwise, the class will not be </span>
<span class="rem">// registered in the COM registry and cannot be created </span>
<span class="rem">// via CreateObject.</span>
SortedList _SortedList;
<span class="kwrd">public</span> NewEmployees()
: <span class="kwrd">base</span>()
{
_SortedList = <span class="kwrd">new</span> SortedList();
}
<span class="kwrd">public</span> <span class="kwrd">object</span> <span class="kwrd">this</span>[<span class="kwrd">object</span> key]
{
<span class="kwrd">get</span> { <span class="kwrd">return</span> _SortedList[key]; }
<span class="kwrd">set</span> { _SortedList[key] = value; }
}
<span class="kwrd">public</span> <span class="kwrd">object</span> Count
{
<span class="kwrd">get</span> { <span class="kwrd">return</span> _SortedList.Count; }
}
<span class="kwrd">public</span> <span class="kwrd">void</span> Remove(<span class="kwrd">object</span> key)
{
_SortedList.Remove(key);
}
<span class="kwrd">public</span> <span class="kwrd">void</span> Add(<span class="kwrd">object</span> key, <span class="kwrd">object</span> value)
{
_SortedList.Add(key, value);
}
<span class="kwrd">public</span> System.Collections.IEnumerator GetEnumerator()
{
ICollection keys = _SortedList.Keys;
<span class="kwrd">return</span> (IEnumerator)keys.GetEnumerator();
}
}
}
</pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-25014094518116809912012-05-05T07:41:00.003+01:002012-05-05T07:42:24.677+01:00Visual Studio 2008 Image library<br />
<br />
The image library should be at "C:\Program Files\Microsoft Visual
Studio 9.0\Common7\VS2008ImageLibrary\1033\VS2008ImageLibrary.zip" and
in your installation process you must ensure that you check the option
"Tools for redistributing Applications\Graphics Library" (<a href="http://img262.imageshack.us/my.php?image=vs2008pt1.jpg">See the attached image</a>)<br />
<br />
<br />
<br />Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-24347661897394796352012-04-27T12:19:00.002+01:002012-08-13T22:00:00.319+01:00Interface Implementation in VBA and VB6WARNING THIS CODE HAS A MEMORY LEAK: check UPDATE <a href="http://productivebytes.blogspot.ie/2012/08/vba-vb6-interface-implementation.html" target="_blank">HERE</a><br />
<br />
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.<br />
<br />
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.<br />
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.<br />
I will show you a very easy example on how to use it in VBA."<br />
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. <br />
<br />
Dim inst as IInstrument<br />
Dim sec as Security<br />
Dim fn as Fund<br />
<br />
Set sec = new Security<br />
Set fn = new Fund<br />
<br />
Set inst = sec<br />
Set inst = fn<br />
<br />
As you can see, bot a security and a fund can be assigned to an Instruments object!<br />
We first define a Class called IInstrument. The code is here<br />
<br />
<pre class="csharpcode"><span class="rem">'This is an Interface for the Generic Financial Instrument</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="rem">'Only Signature</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Id(strId <span class="kwrd">As</span> <span class="kwrd">String</span>)
<span class="rem">'Only Signature</span>
<span class="kwrd">End</span> <span class="kwrd">Property</span>
</pre>
<br />
<br />
We now create a new Class called Security that Implements the IInstrument one. This is a bit more tricky.<br />
Once we implement an Interface, the Class that implements it in VB6 will declare those method as Private like that.<br />
<br />
Private Property Get IInstrument_Id() As String<br />
IInstrument_Id = mId<br />
End Property<br />
<br />
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<br />
1) In the Class that implements the interface I declare at Class level a private object of the interface type<br />
<br />
Private mInstrument As IInstrument<br />
<br />
<br />
<br />
2) In the constructor I assing the Obj to this instance. This will allow me to call the interface methods of my class<br />
<br />
Private Sub Class_Initialize()<br />
'I need to Access the IInstruments Methods <br />
Set mInstrument = Me<br />
End Sub<br />
<br />
3) I implement the interface methods as I normally do.<br />
<br />
Private Property Get IInstrument_Id() As String<br />
<br />
IInstrument_Id = mId <br />
End Property<br />
<br />
Private Property Let IInstrument_Id(strId As String)<br />
mId = strId<br />
End Property<br />
<br />
4) I crate public properties / methods mirroring the interface delegating their implementation to the <br />
mInstrument object<br />
Public Property Get Id() As String<br />
<br />
Id = mInstrument.Id<br />
End Property<br />
<br />
Public Property Let Id(strId As String)<br />
mInstrument.Id = strId<br />
End Property<br />
<br />
Here you can find the Security Class Code<br />
<br />
<br />
<pre class="csharpcode"><span class="kwrd">Implements</span> IInstrument
<span class="kwrd">Private</span> mId <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Private</span> mInstrument <span class="kwrd">As</span> IInstrument
<span class="kwrd">Public</span> Ticker <span class="kwrd">As</span> <span class="kwrd">String</span>
<span class="kwrd">Private</span> <span class="kwrd">Sub</span> Class_Initialize()
<span class="rem">'I need to Access the IInstruments Methods</span>
<span class="kwrd">Set</span> mInstrument = <span class="kwrd">Me</span>
<span class="kwrd">End</span> <span class="kwrd">Sub</span>
<span class="kwrd">Private</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> IInstrument_Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
IInstrument_Id = mId
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Private</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> IInstrument_Id(strId <span class="kwrd">As</span> <span class="kwrd">String</span>)
mId = strId
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="rem">'Public Interface</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Get</span> Id() <span class="kwrd">As</span> <span class="kwrd">String</span>
Id = mInstrument.Id
<span class="kwrd">End</span> <span class="kwrd">Property</span>
<span class="kwrd">Public</span> <span class="kwrd">Property</span> <span class="kwrd">Let</span> Id(strId <span class="kwrd">As</span> <span class="kwrd">String</span>)
mInstrument.Id = strId
<span class="kwrd">End</span> <span class="kwrd">Property</span>
</pre>
We can now test the code<br />
<br />
<pre class="csharpcode"><span class="kwrd">Sub</span> TestSecurity()
<span class="kwrd">Dim</span> Sec1 <span class="kwrd">As</span> Security
<span class="kwrd">Dim</span> Inst <span class="kwrd">As</span> IInstrument
<span class="kwrd">Dim</span> Sec2 <span class="kwrd">As</span> Security
<span class="kwrd">Set</span> Sec1 = <span class="kwrd">New</span> Security
Sec1.Id = 10
Sec1.Ticker = <span class="str">"MXEU"</span>
<span class="kwrd">Set</span> Inst = Sec1 <span class="rem">'Upcast: A Security in an Instruments</span>
Debug.Print Inst.Id
<span class="rem">'DownCast, this should have been done explicit, but VBA does not support CType.</span>
<span class="rem">'VB6 does. So instead of CType(Inst, "Security") we can do</span>
<span class="kwrd">If</span> TypeName(Inst) = <span class="str">"Security"</span> <span class="kwrd">Then</span>
<span class="kwrd">Set</span> Sec2 = Inst
<span class="kwrd">End</span> <span class="kwrd">If</span>
<span class="kwrd">Set</span> Sec2 = Inst
Debug.Print Sec2.Id
Debug.Print Sec2.Ticker
<span class="kwrd">End</span> Sub</pre>
Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0tag:blogger.com,1999:blog-1013473611626255361.post-32976185851863373392012-04-27T11:48:00.003+01:002012-04-27T11:48:33.614+01:00VB6 and VBA EnumerationsAn Enumeration in VBA/VB6 is a special type of long Variable.<br />
A great resource can be found here <a href="http://www.cpearson.com/excel/Enums.aspx" target="_blank">on cpearson.com</a><br />
<br />
<pre class="csharpcode">Enum Position
[_First] = -1
RelativeDynamic = 0
RelativeStatic = 1
Absolute = 2
[_Last] = 3
End Enum
Enum FactorEngine
[_First] = -1
AA = 0
[_Last] = 1
End Enum
</pre>
The code above specify two kind of Enumerations, Position and FactorEngine.<br />
The [_First] and [_Last] are not necessary but they can be used to validate the Enumerated variables.<br />
The _ makes the Enumrated variable hidden to the intellisense, while the [ makes it a valid character for the VB6 interpreter.<br />
This is an example on how to Validate Enumerations<br />
<br />
<br />
<pre class="csharpcode">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 & <span class="str">" Is a valid Engine"</span>
Else
Debug.Print Fac & <span class="str">" Is NOT a valid Engine"</span>
End If
End Sub
</pre>Pierpaolohttp://www.blogger.com/profile/05055021547413261927noreply@blogger.com0