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                MsgBox "Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero"
80                MsgBox "Parameter Number of Material/Cost is set to the default value of 10"
90                getParameterNumberOfMaterial = 10
100           End If
110       Else
120           MsgBox "Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero"
130           MsgBox "Parameter Number of Material/Cost is set to the default value of 10"
140           getParameterNumberOfMaterial = 10
150       End If
160       On Error GoTo 0
End Function

Edit: Yes and if it is possible to convert vba code into a .dll this would be fine also. Then I would not have to convert the code. But I think it is only possible to create a .dll out of vb6 code.

Answer 1

@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/