How do I get this VB Call to work?
 

I have the following call to a VB Subroutine in my code:
=Gen_Note(A7)

The only way I can get it to execute is to that I move my cursor to the
particlar cell, hit the left mouse key
twice, and hit return to calculate.

I've tried copying the call into another line; that doesn't force it to make
a call. <F9 doesn't get it to calculate either.

What can I do?

Thanks for your help,
Mac

How do I get this VB Call to work?
 

I missed where you said you tried application.Volatile as the first line of
the function. I know you discussed this with Jerry Lewis, so I don't know
if that is something you have tried or not. You can do a full recalc with
Ctrl+Alt+F9

Another problem is that the function may result in an error if it isn't in
the active cell - However, I would think you would get a #Value result in
that case although I can't say for sure.

--
Regards,
Tom Ogilvy

"Mac Lingo" wrote in message
hlink.net...
I have the following call to a VB Subroutine in my code:
=Gen_Note(A7)

The only way I can get it to execute is to that I move my cursor to the
particlar cell, hit the left mouse key
twice, and hit return to calculate.

I've tried copying the call into another line; that doesn't force it to
make
a call. <F9 doesn't get it to calculate either.

What can I do?

Thanks for your help,
Mac

How do I get this VB Call to work?
 

The problem is that there is difference between the Active Cell and the
Calling Cell.

When you copy cells, the Active Cell seems to stay constant for every call.

Use
Calling_Address = Application.Caller.Address

to figure out the actual calling address.

Mac

How do I get this VB Call to work?
 

Guess you missed the point Mac.

I am well aware of application.caller. That says nothing for how your code
is written, however. I suggested that a possible problem is that your
function may only work when evaluated as the activecell. It is just a guess
as you have faithfully hidden any nuance of your function. I imagine you
will figure it out eventually.

--
Regards,
Tom Ogilvy

"Mac Lingo" wrote in message
hlink.net...
The problem is that there is difference between the Active Cell and the
Calling Cell.

When you copy cells, the Active Cell seems to stay constant for every
call.

Use
Calling_Address = Application.Caller.Address

to figure out the actual calling address.

Mac

How do I get this VB Call to work?
 

Yes, you're right, Tom; the function only evaluates when called from the
active cell.

My problem is that I need to know the row number that the function is being
processed for, and I apparently only know how to get the row number of the
active cell.

One solution I can think of would be to move the whole process into a macro
and take over row control explicitly.

But is there a way to figure out the row number from the function? It make
the coding much cleaner to do it this way.

Thanks,
Mac Lingo

How do I get this VB Call to work?
 

you show yourself how to do that

Application.Caller returns the address of the cell containing the formula.
so just get the row of that cell

Public Function MyFunction()
Application.Volatile
Dim rng1 as Range
set rng1 = Application.Caller
MyFunction = "I am located in row " & rng1.row
End Function

This should update each time the sheet calculates.

--
Regards,
Tom Ogilvy



"Mac Lingo" wrote in message
hlink.net...
Yes, you're right, Tom; the function only evaluates when called from the
active cell.

My problem is that I need to know the row number that the function is
being
processed for, and I apparently only know how to get the row number of the
active cell.

One solution I can think of would be to move the whole process into a
macro
and take over row control explicitly.

But is there a way to figure out the row number from the function? It
make
the coding much cleaner to do it this way.

Thanks,
Mac Lingo