How to make Class Module universal

[Darren Hill]

[Excel 2007 & 2003, WinXP SP2]
I have several class modules that are used to control groups of similar
controls. I also have several forms which have some similar controls,
and I have created different class modules for each form.
Is there a way to identify which form has triggered the class module,
and use that as a variable in the class module?
Here's an example of some code in one of my spinbutton class modules:
can I identify the form that triggered the event, and use that as a
variable to replace "frmStatistics" ?

Private Sub StatSpinGroup_SpinUp()
Dim strName As String
Dim iStat As Integer
strName = "txt" & Replace(StatSpinGroup.Name, "spn", "")
iStat = Val(frmStatistics.Controls(strName).Text)
frmStatistics.Controls(strName).Text = iStat + 1
End Sub


Thanks.
Darren

 

[Bill Renaud]

Have you tried Application.Caller?

 

[Darren Hill]

Have you tried Application.Caller?

I tried
msgbox application.caller
in the class module, and got a mismatch error. On some webpage I got the
impression that application.caller only worked if called from a
worksheet. (I'm using it on a Userform.) Is this wrong? If so, what is
the correct syntax for using it?

Darren

 

[Bill Renaud]

I tried it inside a class module of mine that processes firewall log data,
and it seems to work, but you might be correct in that it might not work
for forms.

Try:

Dim varCaller as Variant

varCaller = Application.Caller

Set a breakpoint right after the final line above, then inspect varCaller
in the Locals window to see what you get.

Visual Basic Help for the Caller Property only lists a few types of
callers, and says an error is returned for any other type not listed, but
maybe this information is incomplete. (I am running Excel 2000.)

I know it will work for a macro called from a CommandBar button, as
varCaller returns a 2 element array, one element gives the button number,
and the other element lists the name of the CommandBar.

 

[Darren Hill]

I've now tried that, and it give me "Error 2023" - whatever that means!

However, in a flash of inspiration I found something that works: using
the Parent property.

Thanks for your help.
Darren

 

[Bill Renaud]

<<I found something that works: using the Parent property.>>

That would make perfect (more logical) sense!

 

[Chip Pearson]

Application.Caller can be used only in Function procedure that was called
from a worksheet cell (in which case it returns a Range reference to the
cell(s) from which the function was called), or in a procedure called via
the OnAction property of a Shape object (in which case it is a String value
containing the name of the Shape). In any other context, Application.Caller
is an Error-type Variant containing a #REF error value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Bill Renaud]

In Excel 2000, for shapes that are controls on a CommandBar,
Application.Caller returns a 2 element array: element 1 is the control that
called the routine, and element 2 is the name of the CommandBar.

For example, if the first button on a CommandBar named "CommandBar Name"
calls the following routine:

Public Sub Test()
Dim varCaller as Variant

varCaller = Application.Caller
End Sub

....then varCaller will be the following:
varCaller(1) = 1
varCaller(2) = "CommandBar Name"

This is true, even if Sub Test is a method inside a class module.

Has this changed in later versions?

 

[Darren Hill]

Thanks for the explanation, Chip.
By the way, I love your website - that's an astounding collection of
Excel support materials.

Darren