Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DLL - Using a DLL to update cells in excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DLL - Using a DLL to update cells in excel
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DLL - Using a DLL to update cells in excel - Thank you
Thank you for your help.
Ian Parker "Rob Bovey" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel how do I update cells in multiple sheets simultaneously? | Excel Discussion (Misc queries) | |||
Excel doesn't update cells | Excel Discussion (Misc queries) | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
Linked cells don't automatically update in Excel 2003. Why? | Excel Worksheet Functions | |||
How do I link a row of cells in wks 1 to update diff cells wks 2 | Excel Worksheet Functions |