About Us

[email protected]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cell reference in User Defined Function.	Neal Carron[_2_]	Excel Programming	19	September 19th 09 02:40 AM
Which cell is calling a user-defined function?	Randy in Calgary	Excel Programming	1	February 11th 08 04:04 PM
User Defined Function - Using Cell Range	I need help please	Excel Programming	3	May 2nd 07 11:14 PM
current cell in user-defined function	Julio Kuplinsky	Excel Programming	3	December 8th 03 06:24 PM
User-defined function creating circular reference	Lesa Richmond	Excel Programming	4	July 17th 03 01:44 PM

Menu

About Us