Calling a Function from .NET DLL in EXCEL.

[JOESWAR]

I had a complex function to Implement in excel .
( It is some thing related to Data warehousing)

We have prepared a webservice in .NET to Implement that Function and we used to call that webservice from Excel till date.

We have Installed that webservice in one server and I used to point to that webservice.

For some reason we were asked not to use that webservice and server.
So we have come up with a solution.

We were asked to deploy that dll in to the local machine and call the function locally.


To see if we can do that or not we have prepared a sample dll for the webservice which has a HelloWorld function.


I was given a WebService.dll file and WebService.tlb file.

I could successfully call the function in this dll as a webservice from excel.

I have made an entry in the regisry in VB --> Tools --> references ( I dont see any Issues here)

Here is the VBA code that I have written

Public Declare Function HelloWorld Lib "WebService1.dll" () As String

Dim WebSerObj As WebService1.Service1
WebSerObj = CreateObject(WebService1.Service1)
Dim fResult As String
fResult = WebSerObj.HelloWorld()
HelloWorld = fResult
End Function


This function should print the "HelloWorld" Statement.


I am getting the following error : Complile Error. Invailid outside procedure. And the world "WebService1" in 3rd line of the code is Highlighted.