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