UDF

H

Hans Knudsen

A long time ago someone gave me this UDF which has been of great value to
me.

Function myCell(Optional Cell)
Application.Volatile
If IsMissing(Cell) Then
myCell = Application.Caller. _
Address(rowabsolute:=False, _
columnabsolute:=False)
Else
myCell = Cell. _
Address(rowabsolute:=False, _
columnabsolute:=False)
End If
End Function

If I type for example: =mycell(K353) returns K353. If I type =mycell($K$353)
it still returns K353.

Question:
What should the UDF look like if I want:

=mycell(K353;1) should return K353
=mycell(K353;2) should return $K353
=mycell(K353;3) should return K$353
=mycell(K353;4) should return $K$353

Hans Knudsen
 
I

incre-d

Function myCell(Optional Cell, optional Style = 1)
Application.Volatile
If IsMissing(Cell) Then
myCell = Application.Caller. _
Address(rowabsolute:=False, _
columnabsolute:=False)
Else

columnStyle = (Style Mod 2 = 0)
rowStyle = Style > 2

myCell = Cell. _
Address(rowabsolute:=rowStyle, _
columnabsolute:=columnStyle)
End If
End Function
 
M

Mike H

Maybe this

You have to put the non-optional argument first

=mycell(1,A1)
or Mycell(1)

Function myCell(mytype As Integer, Optional Cell)
If mytype > 4 Then myCell = "Error": Exit Function
Application.Volatile
If IsMissing(Cell) Then
Select Case mytype
Case Is = 1
myCell = Application.Caller.Address(rowabsolute:=False,
columnabsolute:=False)
Case Is = 2
myCell = Application.Caller.Address(rowabsolute:=False,
columnabsolute:=True)
Case Is = 3
myCell = Application.Caller.Address(rowabsolute:=True,
columnabsolute:=False)
Case Is = 4
myCell = Application.Caller.Address(rowabsolute:=True,
columnabsolute:=True)

End Select
Else

Select Case mytype
Case Is = 1
myCell = Cell.Address(rowabsolute:=False, columnabsolute:=False)

Case Is = 2
myCell = Cell.Address(rowabsolute:=False, columnabsolute:=True)

Case Is = 3
myCell = Cell.Address(rowabsolute:=True, columnabsolute:=False)

Case Is = 4
myCell = Cell.Address(rowabsolute:=True, columnabsolute:=True)
End Select
End If
End Function


Mike
 
M

MuppetMan

Hi Hans - try this amendment -

Option Explicit

Function myCell(Optional Cell, Optional My_Type As Integer = 1)
Dim My_Row As Boolean, My_Col As Boolean

My_Row = (My_Type = 3) Or (My_Type = 4)
My_Col = (My_Type = 2) Or (My_Type = 4)

Application.Volatile
If IsMissing(Cell) Then
myCell = Application.Caller. _
Address(rowabsolute:=My_Row, _
columnabsolute:=My_Col)
Else
myCell = Cell. _
Address(rowabsolute:=My_Row, _
columnabsolute:=My_Col)
End If
End Function

Regards,
Muppet Man.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top