DLL - Using a DLL to update cells in excel

[Ian Parker]

Good day all,

I have been experimenting with using DLL's to update cells in excel. I am
rewriting a huge VBA application.
I have created a very simple dll in VB 6 that I have included below and I am
calling it from a command button on my spreadsheet that is equally simple
(copy below). Having no experience with DLLS or calling excel from VB 6
makes life a little tricky but once I get started I'll be fine.

In this simple example, I am trying to use a DLL to update a cell in Excel.
The dll function is Public Function fnPlaceNumber below.
When I run the code I following happened

(1) Msgbox - Class started (as expected)
(2) Error
Run-time error '1004':
Method '~' of object '~' failed
(3) Msgbox - Class ended(as expected)

I had hoped that miraculously the number 5 would appear in cell A2 (as per
the code)
but alas the error message mentioned above (2) was the result. I imagine
that this error has occurred as the DLL knows nothing about the open
workbook or worksheet.
Can someone please advise me the best way to solve this ie What do I need to
pass to the DLL so the number 5 is placed in the calling spreadsheet.

Thanks in advance
Ian Parker

' code behind the command button on the Excel Spreadsheet
Option Explicit
Private WithEvents TEST As MyDll.MyInterface
Private Sub CommandButton1_Click()
Set TEST = New MyDll.MyInterface
TEST.fnPlaceNumber
Set TEST = Nothing
End Sub

' Code for simple DLL written in VB6 and compiled as a DLL
Option Explicit
Private p_MyProperty As String
Private p_MyNumbers(5) As Long

Public Event PropertyChanged()

Private Sub Class_Initialize()
MsgBox "Class started", vbOKOnly, " "
End Sub

Private Sub Class_Terminate()
MsgBox "Class ended", vbOKOnly, " "
End Sub

Public Function fnPlaceNumber() As Integer
Range("A2").Select
ActiveCell.FormulaR1C1 = "5"
Range("A3").Select
End Function

Public Property Get MyProperty() As String
MyProperty = p_MyProperty
End Property

Public Property Let MyProperty(ByVal strNewValue As String)
p_MyProperty = strNewValue
RaiseEvent PropertyChanged
End Property

 

[Rob Bovey]

Hi Ian,

The problem is that your VB6 DLL has no idea what Excel object it's
dealing with unless you tell it. Using an unqualified reference to the Excel
Range property won't succeed unless the program knows what object that
property should be applied to.

I've pasted an abbreviated version of your code below, modified so that
it does what you're asking for. Notice that the DLL has a new MySheet
property that you use to tell it what worksheet you want it to modify, and
that you set this property in Excel prior to calling your function.

------------
In the DLL
------------
Private xlSheet As Excel.Worksheet

Public Event PropertyChanged()

Public Function fnPlaceNumber() As Integer
xlSheet.Range("A2").Value = 5
End Function

Public Property Set MySheet(ByRef NewValue As Excel.Worksheet)
Set xlSheet = NewValue
End Property

--------------------------------------
In the Excel Worksheet CodeModule
--------------------------------------
Private WithEvents TEST As MyDll.MyInterface

Private Sub CommandButton1_Click()
Set TEST = New MyDll.MyInterface
Set TEST.MySheet = Me
TEST.fnPlaceNumber
Set TEST = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Ian Parker]

Thank you for your help.

Ian Parker

Hi Ian,

The problem is that your VB6 DLL has no idea what Excel object it's
dealing with unless you tell it. Using an unqualified reference to the Excel
Range property won't succeed unless the program knows what object that
property should be applied to.

I've pasted an abbreviated version of your code below, modified so that
it does what you're asking for. Notice that the DLL has a new MySheet
property that you use to tell it what worksheet you want it to modify, and
that you set this property in Excel prior to calling your function.

------------
In the DLL
------------
Private xlSheet As Excel.Worksheet

Public Event PropertyChanged()

Public Function fnPlaceNumber() As Integer
xlSheet.Range("A2").Value = 5
End Function

Public Property Set MySheet(ByRef NewValue As Excel.Worksheet)
Set xlSheet = NewValue
End Property

--------------------------------------
In the Excel Worksheet CodeModule
--------------------------------------
Private WithEvents TEST As MyDll.MyInterface

Private Sub CommandButton1_Click()
Set TEST = New MyDll.MyInterface
Set TEST.MySheet = Me
TEST.fnPlaceNumber
Set TEST = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


I need