UDF

[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

 

[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

 

[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

 

[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.