Convert vba to vb6 and create a .dll - how to - hints, tips and risks

Question

I should convert a huge load of code which was written in VBA (Excel) to VB6. But I really do not know what I have to take care of or where to begin. Therefore it would be great to get some hints here from the VB6 experts. I installed already MS Visual Studio and played a bit around. But I'm not a VB6 expert and do not really know what I have to do. The final goal is to have all the VBA code, which is currently placed in one excel vba macro into a VB6 project and create a .dll out of it. This .dll should be referenced by the excel and the excel should run like it does now :-) For example what do I have to do to convert this vba code to VB6. ``` Public Function getParameterNumberOfMaterial() As Integer 10 On Error Resume Next Dim a As String 20 a = Sheets("Parameters").name 30 If IsNumeric(Application.Worksheets(a).range("C3").Value) Then 40 If Application.Worksheets(a).range("C3").Value > 0 Then 50 getParameterNumberOfMaterial = Application.Worksheets(a).range("C3").Value 60 Else 70 M…

Accepted Answer

@Tom

Ok, I'm actually learning this with you, so here goes,

VB.Net code (I am using .net 2.0)

In Visual Studio 2005 open a new Class Library Project Then remove all the garbage already written there and paste the code

```
'First thing to do is add a reference the Excel Runtime

Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Namespace ExcelExample

' the following is an Attribute spcifying that the class can be accesses in a unmanaged (non-.net) way

Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Public Class ExcelVB

Public Function getParameterNumberOfMaterial() As Integer
        On Error Resume Next
        Dim a As String
        Dim appInst As New Microsoft.Office.Interop.Excel.Application
        a = appInst.Sheets("Parameters").name

If IsNumeric(appInst.Worksheets(a).range("C3").Value) Then
            If appInst.Worksheets(a).range("C3").Value > 0 Then

getParameterNumberOfMaterial = appInst.Worksheets(a).range("C3").Value
            Else
                MsgBox("Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero")
                MsgBox("Parameter Number of Material/Cost is set to the default value of 10")
                getParameterNumberOfMaterial = 10
            End If
        Else
            MsgBox("Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero")
            MsgBox("Parameter Number of Material/Cost is set to the default value of 10")
            getParameterNumberOfMaterial = 10
        End If
        On Error GoTo 0
    End Function
End Class

End Namespace
```

Build the solution by pressing F6 go to Project->Project Proerties and Check Register for COm interop

So the output is a .DLL and a .tlb , the Excel file should reference the .tlb file,

you have to register the DLL by regasm /codebase c:\Excel\dllname.dll

Then you can access the Function from Excel.

Heres a link to my project folder unrar it, and you'll find a an excel workbook that contains a reference to the .dll via the .tlb

http://cid-4af152a1af4d7db8.skydrive.live.com/self.aspx/Documents/Debug.rar

Heres another great article

http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/

Convert vba to vb6 and create a .dll - how to - hints, tips and risks

Solution

Problem