Call ActiveX DLL from VBA

[m_john]

I want to create some code in VBA (EXcel) that can call an Active X DL
that I created in VB6. This DLL basically is a class that onl
contains a Sub procedure. Inside this Sub, I referenced to the curren
workbook and worksheet .The DLL is complied ok. However, when I calle
this class in VBA code, it gave me error . I did reference the DLL i
the "References" under "Tools" in VBE. Can anyone give me example ho
to do this ?thank

 

[Dr. Stephan Kassanke]

I want to create some code in VBA (EXcel) that can call an Active X DLL
that I created in VB6. This DLL basically is a class that only
contains a Sub procedure. Inside this Sub, I referenced to the current
workbook and worksheet .The DLL is complied ok. However, when I called
this class in VBA code, it gave me error . I did reference the DLL in
the "References" under "Tools" in VBE. Can anyone give me example how
to do this ?thanks

some sample code & error description would be helpful ...

 

[m_john]

in VB6, I created a project called "TestWorkBook " and a clas
"clsTest". This is the code for the Sub Test()
*********************************************
Sub Test()

dim ex As excel.Application
dim wbk As excel.Workbook
dim wsh As excel.Worksheet

Set ex = New excel.Application
Set wbk = ex.ActiveWorkbook

' My_sheet is the current active sheet in the active workbook
Set wsh = wbk.worksheets("My_Sheet")

'Set a value in the active sheet at cell (1,1)
wsh.Cells(1,1) = 2

Set wsh = Nothing
Set wbk= Nothing
Set ex= Nothing

End Sub
*********************************************


In VBA, in the active workbook an in the active worksheet "My_Sheet",
wrote some code :

Sub TestDLL()

Dim testd As TestWorkBook.clsTest

Set testd = New TestWorkBook.clsTest

testd.Test

End Sub

And I did reference the "TestWorkBook" project in the "References" i
VBE.
Let me know if I should verify some more

 

[m_john]

The error description is :
Run -time error '91':
Object variables or With block variable not se

 

[Dr. Stephan Kassanke]

in VB6, I created a project called "TestWorkBook " and a class
"clsTest". This is the code for the Sub Test()
*********************************************
Sub Test()

dim ex As excel.Application
dim wbk As excel.Workbook
dim wsh As excel.Worksheet

Set ex = New excel.Application
Set wbk = ex.ActiveWorkbook

' My_sheet is the current active sheet in the active workbook
Set wsh = wbk.worksheets("My_Sheet")

'Set a value in the active sheet at cell (1,1)
wsh.Cells(1,1) = 2

Set wsh = Nothing
Set wbk= Nothing
Set ex= Nothing

End Sub
*********************************************


In VBA, in the active workbook an in the active worksheet "My_Sheet", I
wrote some code :

Sub TestDLL()

Dim testd As TestWorkBook.clsTest

Set testd = New TestWorkBook.clsTest

testd.Test

End Sub

And I did reference the "TestWorkBook" project in the "References" in
VBE.
Let me know if I should verify some more.


--
m_john
------------------------------------------------------------------------
m_john's Profile: http://www.officehelp.in/member.php?userid=322
View this thread: http://www.officehelp.in/showthread.php?t=711077
Visit - http://www.officehelp.in |
http://www.officehelp.in/archive/index.php |
http://www.officehelp.in/index/index.php

Hi m_john,

Your VBA code seems to be ok. My guess is that the error occurs in your DLL.

Set ex = New excel.Application
Set wbk = ex.ActiveWorkbook

' My_sheet is the current active sheet in the active workbook
Set wsh = wbk.worksheets("My_Sheet")

I might be wrong, but you create a *new* Excel instance and in the next step
you try to refer to the ActiveWorkbook of that instance which does not have
a workbook open yet. Your calling VBA code runs in a distinct Excel instance
thus "set wbk=..." and subsequent variable assignments fail.

Try to get a grip on the running Excel instance, e.g. via GetObject (I am
not sure whether you can pass a handle to the DLL).

Stephan

 

[Howard Kaikow]

There are several examples in the MSFT KB.
For example, see MSFT KB article 185731.

--
http://www.standards.com/; See Howard Kaikow's web site.

I want to create some code in VBA (EXcel) that can call an Active X DLL
that I created in VB6. This DLL basically is a class that only
contains a Sub procedure. Inside this Sub, I referenced to the current
workbook and worksheet .The DLL is complied ok. However, when I called
this class in VBA code, it gave me error . I did reference the DLL in
the "References" under "Tools" in VBE. Can anyone give me example how
to do this ?thanks

http://www.officehelp.in/archive/index.php |
http://www.officehelp.in/index/index.php

 

[m_john]

hi Dr. Stephan Kassanke,
you are right about the GetObject . I was able to resolve the proble
by using:
Set ex = GetObject(, "Excel.Application")
instead:
Set ex = New excel.Application
However, I also have to change the type of "ex",wbk, wsh1 t
"Object".
I was misled by the fact that the error always occur when I try t
"grab" to the "My_sheet":
Set wsh = wbk.worksheets("My_Sheet")
Now it makes sense. But I still wonder is it true that if I do thi
way , it is late bidnding while the previous way is early binding.
I have not check out the article, but thank to Howard too

 

[RMA]

hi Dr. Stephan Kassanke,
you are right about the GetObject . I was able to resolve the problem
by using:
Set ex = GetObject(, "Excel.Application")
instead:
Set ex = New excel.Application
However, I also have to change the type of "ex",wbk, wsh1 to
"Object".
I was misled by the fact that the error always occur when I try to
"grab" to the "My_sheet":
Set wsh = wbk.worksheets("My_Sheet")
Now it makes sense. But I still wonder is it true that if I do this
way , it is late bidnding while the previous way is early binding.
I have not check out the article, but thank to Howard too.

m_John,

You can also pass the active workbook as the first parameter to the
GetObject method - this way you are cetain to grab the precise instance of
the Excel that you need - otherwise GetObject can attach itself to any active
Excel instance (you could have more than one running), defeating the original
purpose.

RMA

 

[m_john]

hi RMA,

what do you mean by "first parameter" ? I thought the first paramete
is the pathname ? I am not sure this is what you mean ,can you give m
an example.Thank

 

[RMA]

hi RMA,

what do you mean by "first parameter" ? I thought the first parameter
is the pathname ? I am not sure this is what you mean ,can you give me
an example.Thanks

hi,

Yes, the pathname includes the name of the workbook you want to work with, i.e

Set ex = GetObject("C:\Temp\Testwb.xls","Excel.Application")

Alternatively, if you want early binding, you can do it thus:

Dim wb As Excel.Workbook
Set wb = GetObject("C:\Temp\Testwb.xls")