@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:Exceldllname.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/

Converting to VB6 is easy.

1. Create a VB6 DLL project. Search on the web for instructions how to do this and how to expose methods, classes, and functions.

2. Add a reference to "Microsoft Office Excel ## library".

3. In a procedure of the project that will be exposed as a method in the DLL, add the following code:

   Dim E As Excel.Application
   Set E = GetObject(, "Excel.Application")
    or if Excel is not running use CreateObject("Excel.Application")
    You can use error handling to figure out which one you need.
   

4. Proceed with your normal VBA code, with one modification: Globally accessed objects such as ActiveSheet or ActiveWorkbook or Sheets must become E.ActiveSheet, E.ActiveWorkbook and E.Sheets.

If you have forms in your VBA project it will be a bit more work to convert them because forms are completely different in VB6 and VBA (they don t work the same by a long shot).

@Tom Tom

You should have no trouble in converting the code from .VBA to vb6.as a matter of fact you virtually don t have to.

The problem is in VB6 context, the langugae cannot understand what

"Application.Worksheets(a).range("C3").Value)" means,

the object Application has a different meaning in VB6

VBA (the VBA version you have) is almost a customized implementation of VB6 in Excel(or word , or anything that comes with MSO).

It would be a bad Idea to try to access Excel UI from VB6 (I m not even sure if its possible )

what you should do is seperate the bussiness logic from the Code then make it into a VB6 library.

For example your code (as I understand) returns the value of the cell C3

It is very much tied to the Excel UI, so it would be very counter productive if not impossible to convert you code to VB6.

because even if you convert the code, most of the variables like (Application.Worksheets(a).range("C3").Value) must be called from VBA, which is pointless

however if you have any other pure Bussiness logic, that can be ported (its funny because theres really nothing to port) easily