Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Objects in VBA and Excel
Does anyone know how to return a reference to an object
(from a VBA function) to a cell on an Excel worksheet, and then use that reference (that is sitting in the worksheet cell) as an argument to another VBA function? Something like: Public Function fun1() as myClass Dim myObject as new myClass ...bunch of code that does stuff fun1 = myObject End Function Public Function fun2(myObj as myClass) as double fun2 = myObj.getDblValue End Function Let's say I want to call fun1 from cell A1 on Sheet1 in my Excel workbook. Then I want to call fun2 with the reference to the object created by fun1 (let's say from cell A2): in cell A1: =fun1() in celll A2: =fun2(A1) Why? Well, maybe my fun1 does a tremendous amount of work. So, I would like to execute it only once, but then be able to query the object (created by fun1) several times with functions like fun2, without having to rerun fun1 every time. I am pretty sure it is possible to do this, because I have seen the functionality. But I don't know the technical details of how to actually do it. Any help would be greatly appreciated!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Objects in VBA and Excel
Stephen..
Try CALLBYNAME function (see vba help for details), Note that you need VBA6 (xl2000+) for this Have fun1 store the string to be used in fun2 Hide the display of the string (it's TEXT property) by giving the cell that uses fun1 a custom number format like ",,," Sub FillMeUp() [a1].Formula = "=fun1()" [a2].Formula = "=fun2(a1)" [a1].NumberFormat = String(3, _ Application.International(xlListSeparator)) End Sub Function fun1() As String fun1 = "myclass!myproc!2" End Function Function fun2(r As Range) as Double Dim s As Variant s = Split(r.Value, "!") fun2 = CallByName(s(0), s(1), VbGet, s(2)) End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Stephen Bianchi" wrote: Does anyone know how to return a reference to an object (from a VBA function) to a cell on an Excel worksheet, and then use that reference (that is sitting in the worksheet cell) as an argument to another VBA function? Something like: Public Function fun1() as myClass Dim myObject as new myClass ...bunch of code that does stuff fun1 = myObject End Function Public Function fun2(myObj as myClass) as double fun2 = myObj.getDblValue End Function Let's say I want to call fun1 from cell A1 on Sheet1 in my Excel workbook. Then I want to call fun2 with the reference to the object created by fun1 (let's say from cell A2): in cell A1: =fun1() in celll A2: =fun2(A1) Why? Well, maybe my fun1 does a tremendous amount of work. So, I would like to execute it only once, but then be able to query the object (created by fun1) several times with functions like fun2, without having to rerun fun1 every time. I am pretty sure it is possible to do this, because I have seen the functionality. But I don't know the technical details of how to actually do it. Any help would be greatly appreciated!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enlarge objects in excel | Excel Discussion (Misc queries) | |||
Pasting objects in Excel | Excel Discussion (Misc queries) | |||
Excel and Busines Objects . . | Excel Discussion (Misc queries) | |||
PowerPoint objects in Excel | Excel Discussion (Misc queries) | |||
ReOrder Excel Objects. | Excel Discussion (Misc queries) |