New blog address

Hi,

Please note that I have split my old blog argurosblog in two parts.
I have noticed that looking at some computer programming code with the next post being about a pasta or rice dish was not working very well.

This is my blog about computing
Arguros Computing

This is my blog about cooking
Arguros Cooking

I left this blog on the web because google has already indexed it and you might not have found anymore a live link in case you searched by blog using the search engine.


I hope you will enjoy the change

Matlab Excel Link from VBA configuration

To create macros that use Excel Link functions, you must first configure Excel to reference the functions from the Excel Link add-in. From the Visual Basic environment pull down the Insert menu and select Module.When the Module page opens, pull down the Tools menu and select References.... In the References window, check the box for EXCLLINK.XLA and click OK. You may have to use Browse to find the EXCLLINK.XLA file.

If you use MLGetMatrix in a macro subroutine, enter MatlabRequest on the line after MLGetMatrix. MatlabRequest initializes internal Excel Link variables and enables MLGetMatrix to function in a subroutine. For example,

Sub Get_RangeA()
  MLGetMatrix "A", "RangeA"
  MatlabRequest

End Sub

Do not include MatlabRequest in a macro function unless the function is called from a subroutine.

Excel how to show all value in a filtered table in vba

Hi,

If you want to show all value from a filter table, you need to check if there the table is filtered first, otherwise the Sheet1.ShowAllData will fail.
This is the solution

With  Sheet1
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With

Formatting code for HTML display

Hi,

This is really a great link
http://www.manoli.net/csharpformat/


to format c# or VB.NET/VBA/VB code in HTML 4 format to display on the web

How to Create a task or appointment using VBA Code in outlook

Hi,
I have changed the original code at this link to show you how to create either a task or an appointment from an e-mail item.
This is very handy when you want quickly create task/appointment without wasting your time to attach the original mail.
The only problem is that the item is attached at the end of the file. This is due to a bug in Outlook 2008/2010 for which the postion item does not work.
Just copy and paste the code below into an outlook module to make it work.

Sub CreateTaskFromMail()
  Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
     
    Set OE = Application.ActiveExplorer
     
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create a task" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
     
    Set MI = OE.Selection(1)
    Set TI = Application.CreateItem(olTaskItem)
    With TI
            .Subject = MI.Subject
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"

    
    Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
       vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
    Case vbYes
         TI.Body = "View Original Mail attacched at the bottom" & vbCrLf & TI.Body
         TI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
         TI.Display
    Case vbNo
         TI.Display
    Case vbCancel
         Exit Sub
    End Select
            
            
           
    End With
    
End Sub



Sub CreateAppointmentFromMail()
    Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
     
    Set OE = Application.ActiveExplorer
     
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create an appointment" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
     
    Set MI = OE.Selection(1)
    Set AI = Outlook.CreateItem(olAppointmentItem)
    With AI
            .Subject = MI.Subject
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"

    
    Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
       vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
    Case vbYes
         AI.Body = "View Original Mail attacched at the bottom" & vbCrLf & AI.Body
         AI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
         AI.Display
    Case vbNo
         AI.Display
    Case vbCancel
         Exit Sub
    End Select
            
            
           
    End With
    
End Sub




Sub NewTaskOrAppoitmentFromMail()
  Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
     
    Set OE = Application.ActiveExplorer
     
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create an appointment or task" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
     
    Set MI = OE.Selection(1)
    'Beep
    Select Case MsgBox("Do you want to create a Task?" & vbLf & _
        "To Add Task (Yes) / To Add Appointment (No) / To Quit (Cancel)" & _
        vbCrLf, vbYesNoCancel + vbQuestion, "Create a task or appointment ...")
    Case vbNo 'If No, create appointment
        Set AI = Outlook.CreateItem(olAppointmentItem)
        With AI
            .Subject = MI.Subject
            .Body = "View Original Mail attacched at the bottom"
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            .Attachments.Add MI, , 1
            .Display
        End With
    Case vbYes
         'If Yes, create task with no due or start date
        Set TI = Application.CreateItem(olTaskItem)
        With TI
            .Subject = MI.Subject
            .Body = "View Original Mail attacched at the bottom"
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            .Attachments.Add MI, , 1
            
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"
            '.Save
            .Display
            
                        
            
        End With
         'Case vbCancel
         '   Exit Sub
    End Select
End Sub

How to call a parametric stored procedure from Microsoft Excel Query

Hi,
This is a very nice trick to call a stored procedure with parameters from excel.

If you type for example

exec model.GetPrices (?,?,?,?)

or

CALL model.GetPrices (?,?,?,?)




you will get this message

"Parameters are not allowed in queries that can't be displayed graphically"

while instead if you put the second Call within {} like that


{CALL model.GetPrices (?,?,?,?)}

it will work!!!

How to use build a mail system framework in Excel using OOP: Example

I will explain how to use the framework to build a new mail object using the framework I have build

Let'us suppose we want to build a mail to trade a Total Return Swap to be send to a broker

 1) Interface Layer: We first create a ITrsMailDataProvider and define its interface

Option Explicit

Public Function RetrieveBody(irsId As String, fundId As String) As Parameters

'No Code Here
End Function

Public Function RetrieveHeader(irsId As String, fundId As String) As HeaderDTO
'No Code Here
End Function

 2) DAL:  We then create a TrsMailDataExcelProvider that implements teh ITrsMailDataProvider interface. You   can use some mock data for testing. If you are getting data from Access just go ahead and create your TRSMailDataAccessProvider that implements the same interface

2) Interface Layer: Then we need to Create a IProviderFactory. This will be the abstract factory that define the abstract methods that return our interface ITrsMailDataProvider or a  IIrsMailDataProvider. The idea is that the abstract providers will be produced by your abstract factory. The business layer will use only those interface and will know nothing about which concrete provider it is in use

   This class will look like
   Class IProviderFactory
       GetTrsMailDataProvider as ITrsMailDataProvider  //only method signature
       GetIrsMailDataProvider as  IIrsMailDataProvider  //only method signature
   End Class

3) DAL Layer: We then need a class that implement the IProviderFactory. For Example a ExcelProviderFactory will take care to create of the MailDataProvider that have Excel as source.

Option Explicit

Implements IProviderFactory

Private mTrsMailDataProvider As TrsMailDataExcelProvider
Private mIrsMailDataProvider As IrsMailDataExcelProvider

Private Function IProviderFactory_GetIrsMailDataProvider() As IIrsMailDataProvider
      If mIrsMailDataProvider Is Nothing Then
         Set mIrsMailDataProvider = New IrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      Set IProviderFactory_GetIrsMailDataProvider = mIrsMailDataProvider
      
End Function

Private Function IProviderFactory_GetTrsMailDataProvider() As ITrsMailDataProvider
     If mTrsMailDataProvider Is Nothing Then
         Set mTrsMailDataProvider = New TrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      
      Set IProviderFactory_GetTrsMailDataProvider = mTrsMailDataProvider
End Function

4) BLL: Finally we will have the client of the Abstract Factory, as for the Abstract factory Pattern. We will call this class the DataProvider (this can be a static class in c# ore moduel in VBA) and it will sit in the business Layer. This Class has the main objective to
use the IProviderFactory interface to produce our Trs and Irs MailDataProvider. The diffuclt part here is that this class should istantiate a Real istance of the abstract class IProviderFactory, but as we know we want the business Layer to be agnostic of the DAL. To get this result in C# we can use reflection. We can just write a method so that it loads a .dll specified in a config file as a string, and create an istance of a class from this dll.
This way to reference to the DAL Layer are necessary. In VB 6.0 this is can be done with CreateObject.

Option Explicit

Private mFactory As IProviderFactory

Public Function IrsMail() As IIrsMailDataProvider
  Set IrsMail = Factory.GetIrsMailDataProvider()
End Function

Public Function TrsMail() As ITrsMailDataProvider
  Set TrsMail = Factory.GetTrsMailDataProvider()
End Function

Private Function Factory() As IProviderFactory
'Insert Code here
'This cose should use a config file and reflection to choose
'which concrete factory instantiate. Createobject could be used in VB 6.0
'to make this class decouple with the DAL Layer
'We keep things ease here.

 If (mFactory Is Nothing) Then
     Set mFactory = New ExcelProviderFactory 'I really should be using CreateObject
                                             'to keep the class decoupled from the DAL
 End If
 Set Factory = mFactory
 
End Function

4) So fare we have done the following
    a) Business Layer -->  Interface Layer -->; DataBase Layer
    b) In the Interface Layer we have implemented the Model Provider Pattern and the Abstract Factory Pattern. Those class are just interface that define the methods that the business Layer can call to accesss the data
    c) In the DAL we have the real providers that just implement the provider interfaces and the real factory that just implement the factory methods.
    d) The transfer of the data between DAL and Business Layer is done using some object that are on the common layer such as MailDTO (data transfer object) or the Parameters collections (this is an utility object)
    e) The client of the abstract factory, the one we called DataProvider, is in the BLL and it stores in as a private field a reference to the IProviderFactory. This is where the magic happen: we can just switch the RealProvider withouth having to change any of the code that regards the BLL or Interface Layer.

How to get the scripting dictionary enumerator to use in the for each loop in visual basic

A common practice is writing VB 6.0 or VBA code to wrap the Collection object in order to create strongly type Collections.

An alternative to the collection object is the scripting.Dictionary object which you can find adding a reference to the Microsoft Scripting Runtime.

The Dictionary Object is an Hash Table, so it is preferred to the Collection object when you need to access elements in the collection by key.
In addtion it has few properties and methods that the Collection object is lacking.

Keys() returns all the keys as an array
Items() returns all the Items as an array
Exists(key): returns true if a key is in the dictionary.

The major draw back is that it does not have an enumerator so you cannot do something like

for Each v in objDictionary
'Do Something
End

Fortunately there is a work around. You can loop the Items or the Keys array

Dim v as variant
For Each v in objDictionary.Items
'Do Something
End

Not that being Items an array, v must be declared as a variant type.
It would be much nicer if we could loop using a strongly typed objected instead.
You can do this by wrapping the scripting.Dictionary class in a customized class and letting the Items method return a Collection object.
You can find here an example

Parameter Class

Option Explicit

Private mValue As Variant
Private mName As String
Private mFormat As String


Private Sub Class_Initialize()
  Me.format = ""
  Me.Name = ""
  Me.value = Empty
End Sub

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

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

Public Property Get value() As Variant
  If IsObject(mValue) Then
     Set value = mValue
  Else
     value = mValue
  End If

End Property



Public Property Let value(varValue As Variant)
    
    If IsObject(varValue) Then
       Set mValue = varValue
    Else
       mValue = varValue
    End If
    
End Property


Public Property Get format() As String
  format = mFormat
End Property

Public Property Let format(strFormat As String)
   mFormat = strFormat
End Property

Then create a Parameters.cls file and paste the code here.

Parameters Class

Option Explicit

Private Const ErrItemIsMissingNum = vbObjectError + 1001
Private Const ErrItemIsMissingSrc = "FFM:Parameters:Item"
Private Const ErrItemIsMissingDes = "Item is missing form the collection"

Private mKeys As Collection
Private mParsDic As Dictionary

Private Sub Class_Initialize()
  
  Set mParsDic = New Dictionary
  
  
End Sub

Private Sub Class_Terminate()
   Set mParsDic = Nothing
End Sub


Public Function Keys() As Collection
      Dim v As Variant
      Dim h As Collection
      Set h = New Collection
      For Each v In mParsDic.Keys
         Call h.Add(v)
      Next
      Set Keys = h
End Function



Public Function Items() As Collection
      Dim v As Variant
      Dim h As Collection
      Set h = New Collection
      For Each v In mParsDic.Items
         Call h.Add(v)
      Next
      Set Items = h
      
End Function

Public Function Item(Index As Variant) As Parameter

           Set Item = mParsDic.Item(Index)
           
End Function

Public Sub Add00(Item As Parameter)
      Call mParsDic.Add(Item.Name, Item)
End Sub

Public Sub Add01(Name As String, value As Variant, Optional format As String = "")
    Dim objParameter As Parameter
    Set objParameter = New Parameter
    
    objParameter.Name = Name
    objParameter.value = value
    objParameter.format = format
    Call Me.Add00(objParameter)
End Sub


Public Function Count() As Long
     Count = mParsDic.Count
End Function


Public Function Remove(key As String)
     
     mParsDic.Remove (key)
          
End Function


Public Function IsInCollection(Name As String) As Boolean
   

    IsInCollection = mParsDic.Exists(Name)

End Function




Public Function Duplicate() As Parameters
    'This function Create a New Parameter Collection
    Dim objDuplicate As Parameters
    Dim par As Parameter
    Set objDuplicate = New Parameters
    For Each par In Me.Items
        Call objDuplicate.Add01(par.Name, par.value, par.format)
    Next
    Set Duplicate = objDuplicate
    
End Function

Once you have done that you will be able to write code like

Dim colPars As Parameters
  Dim aa As Parameter, bb As Parameter, cc As Parameter
  Dim h As Variant
  
  
  Set colPars = New Parameters
  Set aa = New Parameter
  Set bb = New Parameter
  Set cc = New Parameter
  
  aa.Name = "Mario"
  bb.Name = "Gennaro"
  Call colPars.Add00(aa)
  Call colPars.Add00(bb)

  
  For Each cc In colPars.Items
     Debug.Print cc.Name
  Next
  
  

How to send a mail message in C#

Here you can find a piece of code to send mail using c#.
Please not that you need to know your Smtp Host server name to be able to sent out any mail.
In addtion I added a couple of lines to get the current user name.
You can map the current user name to an email address so that you can send the mail out from the current user mail address

ex: mailAddress = abeti then use mrblue.abeti@companyname.com as mail address

using System.Net.Mail;
using System.Security.Principal;

        private void button1_Click(object sender, EventArgs e)

        {
            string mailAddress;
            mailAddress = WindowsIdentity.GetCurrent().Name;
            Console.Write(mailAddress);
            MailMessage message = new MailMessage();
            message.From = new MailAddress("mrblue.abeti@companyname.com");
            message.To.Add(new MailAddress("mrred.pini@companyname.com"));
            message.CC.Add(new MailAddress("mryellow.ciliegi@companyname.com"));
            message.Subject = "Message From .Net";
            message.Body = "This is the content";
            string pdfPath = "C:\\test\\";
            string pdfFileName = "prova.pdf";
            Attachment pdfFile = new Attachment(pdfPath + pdfFileName);
            SmtpClient client = new SmtpClient();
            client.Host="MAILSERVER";
            //client.Send(message);
        }

How to read write and save to a config file in C#

Here you can find some c# code to read/write/save to a config file in c#.
Please note that you need to add a reference to
References -> Add Reference -> System.Configuration

 and

using System.Configuration.
In Addtion you need to add an application configuration file



Application -> Add -> New Item -> Application Configuration File
Than you need to add an <appSettings> session to it

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="cnnString" value="prova"/>
    <add key="LastUpdateDate" value="10 Jan 2012"/>
  </appSettings>
</configuration>


When you run the application and debug the code you find that it is not working !! You can sse the effect when you run the exe generated in release directory.

--------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string cnnString;

            // Get the current configuration file.
            Configuration config =ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            cnnString = config.AppSettings.Settings["cnnString"].Value;
            MessageBox.Show(cnnString);
            config.AppSettings.Settings["cnnString"].Value = "Cavolo";
            cnnString = config.AppSettings.Settings["cnnString"].Value;
            config.Save(ConfigurationSaveMode.Modified);
            ConfigurationManager.RefreshSection("appSettings");



        }
    }
}

Exposing a .NET interface to COM as an interface

---

When you define a NET interface along with a ComVisible class with your interface set as the default one , VB6 hides this default interface entirely from you. Then, when you want to work with that interface in VB6, you use the name of the implementing class. 

The Default interface is set either with ComDefaultInterface attribute or if this is left out, C# will make the default interface the first Interface that the Class implements.

So if you want to see the interface in VB 6.0 you should not have any class implement directly the default interface, but an interface that derives from the default one. Remember that for COM all the interface members of the base interface should be redefined in the derived interface using the new keyword.

How to quick wrap a .NET Collection in a COM component

Here is the code on how to quickly wrap up a .NET Collection and exposed it to COM so that it has a default Item property and a working For Each Loop.

These are the main changes I did to the code compared to my previous post ( see point 9 )

2) The Class NetProva inherits directly form SortedList, while before I used encapsulation and delegation to mimic inheritance. This means that we just need to code the GetEnumerator(). This is because we need this method to return the most generalized interface Collections.IEnumerator. Note the use of the new keyword in the code. We could not ovverride otherwise we would have got a System.Collections.IDictionaryEnumerator return type. Also note that we are return the Enumerator of the Keys becasue COM does not support the DictionaryEntry type.

3) The other method that we might need to implement.ovverride is the indexer. We did not need to do anything here.


TIP
You might be tempted to omit the inheritance relationship when defining a COM
interface because the base methods need to be defined anyway and you don’t have to deal with the
mess of multiply defined members. However, don’t omit the relationship because it’s
still important for proper operation on both .NET and COM sides. Not only does it
provide the expected behavior in .NET clients using such interfaces (such as implicitly
converting an INetProva type to an IEnumerable type, but for COM as well because
a CCW makes QueryInterface calls on the derived interface succeed for any of its
base interfaces.

namespace Collections01
{
    //  To expose properties and methods to COM, you must declare them on the class 
    //  interface and mark them with a DispId attribute, and implement them in the class. 
    //  The order in which the members are declared in the interface is the 
    //  order used for the COM vtable.
    //  ex:
    //  [DispId(1)]
    //  void Init(string userid , string password);
    //  [DispId(2)]
    //    bool ExecuteSelectCommand(string selCommand);

    //Class Interface
    [Guid("2c280db5-99d0-4b5d-a014-13f6a3dfe271"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface INetProva : IEnumerable {
        [DispId(-4)] //Iterator
        new IEnumerator GetEnumerator();
        [DispId(2)]
        void Add(object key, object value);
        [DispId(3)]
        int Count { get; }
        [DispId(4)]
        void Remove(object key);
        [DispId(0)] //Default Property
        object this[object key] { get; set; }
   
    }



    // To expose events from your class, you must declare them on the events 
    // interface and mark them with a DispId attribute. 
    // The class should not implement this interface. 

    //Events Interface
    [Guid("23aaa0da-ba82-48a1-95fb-789e8e061be5"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface INetProvaEvents
    {
    }



    //The Class can also implement other interfaces. But only
    //the first one will be exposed to COM.
    //COM Class do not support inheritance beyond interface implementation
    //Class Employees : List<Employee> is not COM compatible

    //Class Implement the Class Interface
    [Guid("4a37e6a5-2efc-42d5-91db-52787a258d85"),
     ComVisible(true),
     ClassInterface(ClassInterfaceType.None),
     ComDefaultInterface(typeof(INetProva)),
     ComSourceInterfaces(typeof(INetProvaEvents)),
     ProgId("Collections01.NetProva")]
    public class NetProva : SortedList,  INetProva
    {
        public new IEnumerator GetEnumerator()
        {
            ICollection keys = this.Keys;
            return (IEnumerator)keys.GetEnumerator();
         
        }
    }
}

How to Deploy a .NET COM Assembly with VS 2008 Quick Guide

This is a very quick guide on how to deploy a COM exposed .NET dll using VS 2008.
For details please visit
 Build and Deploy a .NET COM assembly
Guidelines for COM interoperability from .NET: Heath Stewart Blog
Exposing .NET Framework Compoents to COM



1) Add to the Solution a Setup Up project:
   Right-click Colution -- Add -- New Project
   Other Project Types -- Setup and Deployment -- Setup Project
   Name it

2) In Fyle System on Target machine go to Application Folder
3) Add Assembly...
4) Navitate to the MyLibrary.dll COM expose assembly that you want to deploy
5) Click Ok. The .tlb file associate to the .dll will be imported too. The Detected Dependencied folder will show you also the Microsoft .Net Framework and the .tlb file

6) Click MyLibary.dll and in the Property Window select
    Register  = vsdraCOM
7) Click MyLibray.tlb and in the Property Window select
    Register = vsdrfCOM
 
Build the solution.

If you have a problem with the MS VS Regestry Capture utility see here

You will find in the Setup Folder a setup.exe and msi package ready to be deployed.

How to make VS 2008 to register a C# COM DLL

To make C# create and register the typelibrary go to
Project/Properties/Build tick Register for COM interop. (Reccomended Choice)

Otherwise you need to use REGASM (see here  for the asembly registration tool). The Assembly Registration tool reads the metadata within an assembly and adds the necessary entries to the registry, which allows COM clients to create .NET Framework classes transparently. This utility is necessary when you need to expose to COM e .exe (winform application) .net assembly. You can create a type libray in this way. MyAssembly.dll can be also MyAssembly.exe

It is important that you do not check the
Project/Properties/Application/Assembly Information... Make assembly COM-Visible.
This option will set [assembly: ConVisible(true)] in the AssemblyInfo.cs file
Which will make all the Class in the Project COM visible. This in practice will make C# to generate new Guids for each class on which we did not specify a Guid attribute each time we recompile, causing a registry bloat.
It is much better to set
[assembly: ConVisible(false)], and use ComVisible(true) at class level to specify which class should be visible for COM interop .

How to Register / UnRegister a C# COM Exposed DLL manually an with VS 2008

To Register a C# COM Exposed Dll you I reccomend to create a Register.Bat file with the following instruction

 ------------------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;

REM we first Unregister the .NET dll and then register it
regasm.exe /u "FullPath\MyLibrary.dll" /tlb
regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb
pause

-----------------------------------------------------------------------------------------------------

This insturction set the search path for the regasm.exe file. I have included the most common directory that should have it

path=%path%; .......

This line unregister the Dll and its typelibrary

regasm.exe /u "FullPath\MyLibrary.dll" /tlb


This one creates the type library and register the dll with its full Path and along the type library.
If you do not use the /codebase option VB will complain that it cannot find the library.

regasm.exe /codebase  "FullPath\MyLibrary.dll" /tlb

 To unregister make an unregister.bat file
 ---------------------------------------------------------------------------------------------
REM Set the search directories
path=%path%;C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\Program Files\Microsoft.NET\SDK\v1.1\Bin;C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322;

REM we Unregister the .NET dll
regasm.exe /u "FullPath\MyLibrary.dll" /tlb

pause

-----------------------------------------------------------------------------------------------------


When you use the  code base option you need to have a strongly type Assembly (Dll) To do this
follow these steps

Go to Properties/Signing. Tick Sign the assembly check box.
Then go to Choose a stroing name key file
Choose a name  ex   "MyLibrary_COM_Key"
This will create a MyLibray_COM_Key.snk file in the project folder.

Lastly you also need to make sure that the Assembly Version is not someting like 1.0.* which means that each time you rebuild the project the assembly name changes. Just give the assembly a version, something like 1.0.0.0 and stick with it. The call to the COM exposed DLL is also connected to the assembly version registered in the registry.

Microsoft Visual Studio Registy Capture Utility has stop working Error

If you have this error while you are trying to use VS 2008 Project Setup and Development, do the following:

1. Locate regcap.exe here: C:\Program Files(x86)\Microsoft Visual Studio 9.0\Common7\Tools\Deployment
2. Right click and select properties.
3. Select Compatibility tab
4. Check box to Run this program in compatibility mode.
5. Select Windows Vista SP2 in the OS drop-down and Run as Administrator.
6. Click Ok and Recompile.

his also works for 64 bit Windows 7 with VS 2010. The path for regcap is C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Tools\Deployment 

MS scripting run time and MS Regular Expression in VB 6.0 or VBA

Hi,
Two nice library that help you speed up your VB 6.0 / VBA development time are the
1) Microsoft Scripting Run Time library

   The two most interestinjg objects here are the
   Dictionary object which is an hashtable. Its only major draw back  is that it does not expose the   NewEnum function, which means that while you can wrap it up to create a strongly type dictionary object, you cannot create a NewEnum method to loop through the collection using the For Each statement

   FileSystemObject which let you easily access to files and folders

2) Microsoft VBScript Regural Expression 5.5.

    This is a really nice library to use to test a string using regular expression.
    This is an example on how it works:

Function TestRegExp(myPattern As String, myString As String)
   'Create objects.
   Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim colMatches   As MatchCollection
   Dim RetStr As String
   
   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern

   ' Set Case Insensitivity.
   objRegExp.IgnoreCase = True

   'Set global applicability. It search the entire string
   objRegExp.Global = True

   'Test whether the String can be compared.
   If (objRegExp.Test(myString) = True) Then

   'Get the matches.
    Set colMatches = objRegExp.Execute(myString)   ' Execute search.

    For Each objMatch In colMatches   ' Iterate Matches collection.
      RetStr = RetStr & "Match found at position "
      RetStr = RetStr & objMatch.FirstIndex & ". Match Value is '"
      RetStr = RetStr & objMatch.Value & "'." & vbCrLf
    Next
   Else
    RetStr = "String Matching Failed"
   End If
   TestRegExp = RetStr
End Function

You can test your regual expression here
 An excellent introduction to regular expression can be found in this code project article

C# COM Exposed Collection

Code can be found here and here 
Visual Studio 2008 and Excel 2003-2008 required
Further details con be found on this post

Hi,
You can find attache a project that contains code that will allow you to create a C# COM exposed collection so that
1) It has Item as default property. So from VB 6.0 you can just do employees(1).Name
2) It is a IEnumerable object
3) It has a GetEnumerator() function that can be used to create VB 6.0 strongly typed collection that can be iterated using the For Each loop

 Public Function NewEnum() As IUnknown
   Set NewEnum = mNetList.GetEnumerator
End Function

Please do not to register the CLASS for COM interop just go to Project/Properties/Build Register for COM Interop.

Do not use the option Project/Properties/Application/Assembly Information/Make assembly COM Visible.
I am using the attribute  ComVisible(true) to decide which class should be visible to COM on individual basis.

How to access .Net library from VBA and VB 6.0

Download Example from Microsoft  here

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

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

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

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

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

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

More interestingly:

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

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

 To use this classe just add

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

and you are ready to go.

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

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

How to create custom collection in VBA tricks

This solution was taken from a forum entry I found on the web



You can, but the process is a bit more manual. If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

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


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...