Function Arguments

[Bob]

Hi Everyone:

In VB or VBA, is there anyway to send the name of a function as an argument
into a sub, and have that sub execute the function. For example

Private Sub Main()
call MySub(2,y,"F") 'or call MySub(2,y,F)
cells(1,1).value=y
End sub

Private sub MySub(x,y,FName)
y = 2*FName(x)
more code
End Sub

Private function F(x)
Some code
End function

I know I can write all these routine as one. However, the function F(x) is
a generic one that the user will supply. Is there any way of doing this?
Thanks for your help.

Bob

 

[J. Blauth]

Hi Everyone:

In VB or VBA, is there anyway to send the name of a function as an argument
into a sub, and have that sub execute the function. For example

Private Sub Main()
call MySub(2,y,"F") 'or call MySub(2,y,F)
cells(1,1).value=y
End sub

Private sub MySub(x,y,FName)
y = 2*FName(x)
more code
End Sub

Private function F(x)
Some code
End function

I know I can write all these routine as one. However, the function F(x) is
a generic one that the user will supply. Is there any way of doing this?
Thanks for your help.

Bob

take a look at the CallByName function. this might help you to solve the
problem.

Greets,
J. Blauth

 

[Bob]

Thanks. However, I do not understand what is the object in the arguments of
this function. in my code, what do I use for the object argument?

Thanks;

Bob

 

[Clif McIrvin]

Thanks. However, I do not understand what is the object in the
arguments of this function. in my code, what do I use for the object
argument?

Bob, have you ever written a class module?

It looks to me like if FName was a method of a user created object
(class module) then you could use CallByName to call that method.

This is just a comment based on what I think I understand ... I've never
had occasion to play with creating a user defined object.

 

[Bob]

Hi Clif:

Thanks for the reply. Unfortunately, I have never written a class module,
and I do not understand it well. For regular functions, not real objects, I
cannot understand what is the difference between a regular module that lists
all the functions, and a class module that list the same objects. To me
they seem to be the same thing.

Bob

 

[Clif McIrvin]

Hi Clif:

Thanks for the reply. Unfortunately, I have never written a class
module, and I do not understand it well. For regular functions, not
real objects, I cannot understand what is the difference between a
regular module that lists all the functions, and a class module that
list the same objects. To me they seem to be the same thing.

Bob

FWIW, it's my understanding that the fundamental (ie, practical?)
difference between a standard module and a class module is that the
class module allows you to create a user defined object, complete with
properties, methods and event handling. (The big one here I think is
event handling.)

It's not much more than an 'educated guess' on my part, but I'm pretty
sure that if you create a class module with events you will be able to
trap a sheet recalculate or sheet changed event and do the direct cell
manipulation that you cannot do from a user defined worksheet function.

Several years ago I recall having some difficulty wrapping my brain
around user defined data types in GW BASIC; after I got them figured out
I used them a lot (after all, they were just COBOL & RPG type
declarations <g>). Something tells me that if I were actually a
developer instead of an end user forced into making the app work because
the boss wants to develop in house (another topic, there) I'd be using
class modules frequently.


Aside: have you looked at microsoft.public.excel.programming for help? 9
months ago or so when I was at the front end of this project I found a
lot of help there ... got my feet under me in VBA for Excel and have
since moved my focus more towards Access. I suspect you'll find a lot
more assistance there than has shown up here.

HTH

 

[Bob]

Hi Clif:

After going on the web reading about it and reading some books, I finally
got the thing to work with a class. the only problem is that the user has
to put the function inside my class module, which would make it a bit
awkward. I was hoping for the user to put the function inside any module,
and then from the spreadsheet to enter the name of the function as one of
the arguments of the main function. However, I guess, this does not work.
anyway, it is better than nothing. Thanks for your help, and I will give
the excel newsgroup a try.

Bob

 

[Clif McIrvin]

Hi Clif:

After going on the web reading about it and reading some books, I
finally got the thing to work with a class. the only problem is that
the user has to put the function inside my class module, which would
make it a bit awkward. I was hoping for the user to put the function
inside any module, and then from the spreadsheet to enter the name of
the function as one of the arguments of the main function. However, I
guess, this does not work. anyway, it is better than nothing. Thanks
for your help, and I will give the excel newsgroup a try.

Glad I was able to help -- you taught me something about user defined
worksheet functions too.

I'm confident you'll find a high degree of expertise on the
excel.programming newsgroup.