How to pass active workbook to external DLL?

[MarkDev]

I need to send a reference(?) to active workbook to external DLL. The DLL is
written in VB6. In the external DLL I'll be updating this workbook. Sample code is provided below:

Here is excel macro code:
Sub test3()
Dim TC As ClassA
Dim wbCodeBook As Workbook
Set TC = New ClassA
Set wbCodeBook = ThisWorkbook
TC.GetCellA1(wbCodeBook)
Set TC = Nothing
End Sub


Below is provided my VB6 code (this sub is part of CalssA):
Public Sub GetCellA1(locWB As Workbook)
Dim CellValue As String
CellValue = locWB.Worksheets(1).Range("A1")
MsgBox "Cell A1 = " + CellValue, "FROM DLL"
End Sub

It fails on the line TC.GetCellA1(wbCodeBook). The error message says:
"#438: Object doesn't support this property or method."

Please help

[NickHK]

Your VB6 DLL does not know what a Workbook is.
However it does know Excel.Workbook, assuming you have a reference to the
Excel library, using early binding.

NickHK

"MarkDev" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
>
> I need to send a reference(?) to active workbook to external DLL. The
> DLL is
> written in VB6. In the external DLL I'll be updating this workbook.
> Sample code is provided below:
>
> Here is excel macro code:
> Sub test3()
> Dim TC As ClassA
> Dim wbCodeBook As Workbook
> Set TC = New ClassA
> Set wbCodeBook = ThisWorkbook
> TC.GetCellA1(wbCodeBook)
> Set TC = Nothing
> End Sub
>
>
> Below is provided my VB6 code (this sub is part of CalssA):
> Public Sub GetCellA1(locWB As Workbook)
> Dim CellValue As String
> CellValue = locWB.Worksheets(1).Range("A1")
> MsgBox "Cell A1 = " + CellValue, "FROM DLL"
> End Sub
>
> It fails on the line TC.GetCellA1(wbCodeBook). The error message says:
> "#438: Object doesn't support this property or method."
>
> Please help
>
>
> --
> MarkDev
> ------------------------------------------------------------------------
> MarkDev's Profile:
> http://www.excelforum.com/member.php...o&userid=35572
> View this thread: http://www.excelforum.com/showthread...hreadid=570387
>

[MarkDev]

I've changed definition of the DLL function
from Public Sub GetCellA1(locWB As Workbook)
to Public Sub GetCellA1(locWB As Excel.Workbook)
but still receving the same error message.

[NickHK]

Mark,
This works for me:
'------------------------
'DLL <ExcelTest> Class <cTest>
'Reference to Excel library
Public Function GetCellA1Value(argWB As Excel.Workbook) As Variant
GetCellA1Value = argWB.Worksheets(1).Range("A1").Value
End Function
'------------------------
'In Excel
'Reference to ExcelTest.dll
Dim DLLTest As ExcelTest.cTest

Private Sub CommandButton1_Click()
Set DLLTest = New ExcelTest.cTest
With DLLTest
MsgBox "The value in cell A1 of WS(1) is " &
..GetCellA1Value(ThisWorkbook)
End With
End Sub
'------------------------

NickHK


"MarkDev" <[email protected]> wrote in
message news:[email protected]...
>
> I've changed definition of the DLL function
> from Public Sub GetCellA1(locWB As Workbook)
> to Public Sub GetCellA1(locWB As Excel.Workbook)
> but still receving the same error message.
>
>
> --
> MarkDev
> ------------------------------------------------------------------------
> MarkDev's Profile:
http://www.excelforum.com/member.php...o&userid=35572
> View this thread: http://www.excelforum.com/showthread...hreadid=570387
>

[MarkDev]

I had to remove brackets to make it working on my computer (?):
GetCellA1Value ThisWorkbook

Thank you Nick

[NickHK]

That makes sense because you are not using the return value or "Call".

NickHK

"MarkDev" <[email protected]> wrote in
message news:[email protected]...
>
> I had to remove brackets to make it working on my computer (?):
> GetCellA1Value ThisWorkbook
>
> Thank you Nick
>
>
> --
> MarkDev
> ------------------------------------------------------------------------
> MarkDev's Profile:
http://www.excelforum.com/member.php...o&userid=35572
> View this thread: http://www.excelforum.com/showthread...hreadid=570387
>