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

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

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' & ")"
 
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
 
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
 
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
 
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
 
Back
Top