Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 11th 20, 06:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2020
Posts: 2
Default 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

  #3   Report Post  
Old October 11th 20, 08:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2020
Posts: 2
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 05: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 07:24 PM
User-defined function creating circular reference Lesa Richmond Excel Programming 4 July 17th 03 01:44 PM


All times are GMT +1. The time now is 02:34 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017