create .dll out of vba or convert vba to vb6 and create then a .dll

[IkeMike]

Hi,


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.

<code>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
</code></pre> Thanks for your help!


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.

 

[BrianB]

I use Excel and VB6. There is now a free MS Visual Studio 2008 which I have not done much with.

Although much of the code is similar they are not the same - and we cannot just copy/paste whole chunks of code across. The main difference is that we have to create an Excel object in VB6 and use that. (Similar to how we use Word etc. from Excel and vice versa).

My experience is that I tend to use Excel when there is the need to see and manipulate large amounts of data, VB6 for things that just need forms.

Don't forget that it is possible to completely hide the Excel interface.
eg. Application.Visible = False

 

[Legacy 98055]

It's fairly straight forward. You must consider the scope of your code. "What" will use your code? For example, if your code will only be used at the workbook level, your parent reference need not go any higher than the workbook object. A single worksheet only? Then the worksheet level. A range? Then a range... I edited your code and qualified the references to the top object in the hierarchy. This being the application object. The lower the scope, the more efficient the code because the references do not have to go through multiple levels to qualify their members.

I understand that the code you posted is simply an example but some pointers to consider when compiling code is to allow yourself the flexibility of passing arguments. You should rarely hard code range names, range addresses, or the like into a foriegn function. For one thing, you lose flexibility. Also, you don't want your function to have to resolve references or at least resolve as few as possible. Even so, for now, using your self-same example and using the highest level object, here is how you would compile your function and call it from Excel.

1. Open Visual Studio and create a new ActiveX.dll project.

2. Paste the function below, as is, into Class1

3. Add a reference to the lowest version of the Excel library that you expect will be used to call your dll.

4. Create an argument in your function (xlApp As Excel.Application)

5. Replace every instance of "Application" with the reference variable xlApp (see code)

6. Rename Class1, "Functions"

7. [MENU] Project, Project1 Properties, rename your project "MyExcelFunctions"

8. Save your project.

6. [MENU] File, make MyExcelFunctions.dll

Public Function getParameterNumberOfMaterial(xlApp As Excel.Application) As Integer
10      On Error Resume Next
        Dim a As String
20      a = Application.ThisWorkbook.Sheets("Parameters").Name

30      If IsNumeric(Application.Worksheets(a).Range("C3").Value) Then
40          If Application.ThisWorkbook.Worksheets(a).Range("C3").Value > 0 Then
        
50              getParameterNumberOfMaterial = Application.ThisWorkbook.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

To use from VBA:

1. Set a reference to your newly create DLL ("MyExcelFunctions"). Visual Studio has already registered the DLL.

2. Provide a reference of the proper scope. In this case, just add a variable to a standard module. (see code below)

Option Explicit

Dim MyFunction As MyExcelFunctions.Functions

Public Function getParameterNumberOfMaterial() As Integer
    If MyFunction Is Nothing Then Set MyFunction = New MyExcelFunctions.Functions
    getParameterNumberOfMaterial = MyFunction.getParameterNumberOfMaterial(Application)
End Function

You can call this function from a worksheet cell as such: =getParameterNumberOfMaterial() or use it just as you would in your vba code.

Search MSDN for "Automation Servers". These are activeX dll's that are preloaded with a few things that work with office and allow you to call functions from worksheet cells without any VBA code at all. You can see this type of project by selecting the "Add-In" Project Template when you create a new project in Visual Studio.

The file below contains the VB project files and an example workbook.

<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/Example%20Dll.zip" TARGET="_blank">Download Example Project</A>

If you have any questions, please post back to this thread. Tom

 

[hjpal]

downloaded the example project.
regsvr32 on MyExcelFunctions.dll gives error: loadlibrary .... module not found.
I don't have vb6 or visual studio on the computer. What do I need to do?