Given a Row and a Column I would like the Range reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 17 Sep 2007 20:27 GMT

I'm inserting a custom user defined function into a cell.

As a parameter to the function is a cell which is the cell in which
the function is located

i.e.

in cell A5 I have the following function

=myCustomFunction(A5)

Now, I want to insert this function automatically into that cell but
the way I have that cell refrenced is in terms of Rows and Columns,
that is A5 = Row 5 Column 1

Is there a way I can do this

Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
somehow' & ")"

Reply to this Message

Bernard Liengme - 17 Sep 2007 20:40 GMT

Except in very special circumstances, a cell cannot contain a function that
refers to it own cell. That is called a circular reference. One can use
circular reference in a properly design worksheet but they are unusual.

Further more no formula (UDF or otherwise) cannot <insert> any formula into
a cell. A formula has the form =FUNCTION_NAME (arguments) and returns a
value (or several values in the case of an array formula)

You need to re-think the task.
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> I'm inserting a custom user defined function into a cell.
>
[quoted text clipped - 15 lines]
> Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> somehow' & ")"

Reply to this Message

Chris - 17 Sep 2007 21:33 GMT

Maybe I wasn't clear in my explanation.

Within VBA I wrote my own function with the following signature

Function myCustomFunction(myRange As Range) As Integer
myCustomFunction = 5 ' just for example
End Function

Now I had another sub which I called from a button within a worksheet
that did this

Sub insertMyCustomFunction(xValue As Integer, yValue As Integer)
Cells(xValue, yValue) = "=myCustomFunction("& _
' Now here is where I'm having the issue, I have an xValue and a
yValue that I need to convert to a range i.e. A5
")"
End Sub

Either way, I fixed this by just changing
myCustomFunction(myRange As Range)

to

myCustomFunction(xCoord As Integer, yCoord As Integer)

and just passing the coordinates through the insert sub

On Sep 17, 3:40 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Except in very special circumstances, a cell cannot contain a function that
> refers to it own cell. That is called a circular reference. One can use
[quoted text clipped - 30 lines]
> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> > somehow' & ")"

Reply to this Message

Bernard Liengme - 17 Sep 2007 21:58 GMT

Here is a short in the dark.
The VBA Help has this example:
Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"

So here is a suggestion (untested):
Cells(xValue, yValue).FormulaR1C1 =_
"=myCustomFunction(R" & xValue & "C" & yValue & ")"

But it still looks like a circular reference
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Maybe I wasn't clear in my explanation.
>
[quoted text clipped - 61 lines]
>> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
>> > somehow' & ")"

Reply to this Message

Chris - 17 Sep 2007 22:39 GMT

Alright,

You've convinced me that it is a circular reference. Is there a way
then to find out what cell the function is being ran in? That is
really the only reason why I need the x, coordinates.

In my previous example I would much rather have

Sub myCustomFunction()
Cell_In_Which_MyCustomFunction_Is_In = 5 ' just for example
End Sub

Is there any way to get this info

On Sep 17, 4:58 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Here is a short in the dark.
> The VBA Help has this example:
[quoted text clipped - 80 lines]
> >> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> >> > somehow' & ")"

Reply to this Message

Bernard Liengme - 18 Sep 2007 16:25 GMT

I think Dave has answered this.
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Alright,
>
[quoted text clipped - 99 lines]
>> >> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
>> >> > somehow' & ")"

Reply to this Message