Objects in VBA and Excel

[Stephen Bianchi]

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!!

 

[keepitcool]

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 >

 

[Tushar Mehta]

What you want to do is doable...kinda...

You would have to retain the information in some kind of a global to
pull this off. Also, a function cannot return an object to XL. So,
something like the foll. would work.

In a class module, Class1:

Option Explicit

Public i As Long
Public Sub doCalculations(p1, p2, p3)
i = p1 * p2 * p3
End Sub

In a standard module:
Option Explicit
Dim x As Class1

Public Function Func1(p1, p2, p3)
Set x = New Class1
x.doCalculations p1, p2, p3
Func1 = x.i
End Function
Public Function Func2()
Func2 = x.i
End Function

Using Func1 in a worksheet with three parameters does all the heavy
duty calculation and stores the result in a global variable. Func2
simply returns an acceptable data type to XL.

For example, in C5, enter =func1(2,3,4) and in C6 =func2()

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions