Help pasing cell reference to user-defined VBA function
 

Hello,

I would like to write a VBA function for Excel that takes a cell address as an argument. This function is intended to be invoked in other cells as a formula, for example: =NET_COST(A1).

What has made this difficult is that the function needs to read the formula that that is defined in the cell A1. I have no interest in the cell's value.

I have tried typing the argument as "Range" but that doesn't work. I guess this is because the cell's "Value" property is what's being passed?

What does work is passing the cell address as a string:
=NET_COST("A1")
and the using the "Range" object inside the function to access the formula.

This is not ideal because the cell address will not updated if I copy and paste that cell's formula into another cell.

The best I've been able to do is use the expression:
=NET_COST(FORMULATEXT(A1))
but it strikes me as too verbose and I'd prefer the simpler =NET_COST(A1).

So, my question is what data type should I use to make this work and if the answer is it can't be done, can you explain why not?

Thanks much!

-mike

Help pasing cell reference to user-defined VBA function
 

Hi,

Am Sun, 11 Oct 2020 10:12:11 -0700 (PDT) schrieb :

I would like to write a VBA function for Excel that takes a cell address as an argument. This function is intended to be invoked in other cells as a formula, for example: =NET_COST(A1).

What has made this difficult is that the function needs to read the formula that that is defined in the cell A1. I have no interest in the cell's value.

I have tried typing the argument as "Range" but that doesn't work. I guess this is because the cell's "Value" property is what's being passed?

What does work is passing the cell address as a string:
=NET_COST("A1")
and the using the "Range" object inside the function to access the formula.

This is not ideal because the cell address will not updated if I copy and paste that cell's formula into another cell.

The best I've been able to do is use the expression:
=NET_COST(FORMULATEXT(A1))
but it strikes me as too verbose and I'd prefer the simpler =NET_COST(A1).

try:
Function Net_Cost(myRng As Range)
Net_Cost = myRng.Formula
End Function


Regards
Claus B.
--
Windows10
Office 2016

Help pasing cell reference to user-defined VBA function
 

On Sunday, October 11, 2020 at 2:42:30 PM UTC-4, Claus Busch wrote:
Hi,

Am Sun, 11 Oct 2020 10:12:11 -0700 (PDT) schrieb :

I would like to write a VBA function for Excel that takes a cell address as an argument. This function is intended to be invoked in other cells as a formula, for example: =NET_COST(A1).

What has made this difficult is that the function needs to read the formula that that is defined in the cell A1. I have no interest in the cell's value.

I have tried typing the argument as "Range" but that doesn't work. I guess this is because the cell's "Value" property is what's being passed?

What does work is passing the cell address as a string:
=NET_COST("A1")
and the using the "Range" object inside the function to access the formula.

This is not ideal because the cell address will not updated if I copy and paste that cell's formula into another cell.

The best I've been able to do is use the expression:
=NET_COST(FORMULATEXT(A1))
but it strikes me as too verbose and I'd prefer the simpler =NET_COST(A1).

try:
Function Net_Cost(myRng As Range)
Net_Cost = myRng.Formula
End Function


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

Thanks for the response- it works.

What puzzles me is that I had tried this earlier as it was the plausible thing to do but it would fail (compilation error or object required or something else- can't recall). That's why I posted the question.

Much obliged!

-mike