passing cell reference and formatting cell in VBA function

[graham_s]

I want to format the cell that contains a function. The function is passed
the contents of anothe cell and removes an "A" or "*" as required. I want to
format the cell containing the function to be date format. This can obviously
be done manually but I would like to do it automatically. The format command
below has no effect.

How can I do this?
Can I pass or somehow obtain the cell ref of the cell containing the function?



Public Function fncExtractDate(str As String) As Date

'22/04/07 A
'22/04/07 *
'22/04/2009

Dim d As Date

If InStr(str, "A") = 0 And InStr(str, "*") = 0 Then
'valid date
d = CDate(str)
Else
'have "A" or "*". read the first 8 characters
d = CDate(Left(str, 8))
End If

fncExtractDate = Format(d, "dd/mm/yyyy")

End Function



TIA

Graham_s

 

[Jacob Skaria]

Public Function fncExtractDate(str As String) As Date
Dim d As Date

If InStr(str, "A") = 0 And InStr(str, "*") = 0 Then
d = CDate(str)
Else
lngYear = IIf(CInt(Mid(str, 7, 2)) <= CInt(Format(Date, "YY")), _
Left(Year(Date), 2) & Mid(str, 7, 2), _
(CInt(Left(Format(Date, "YYYY"), 2)) - 1) & Mid(str, 7, 2))
d = CDate(Left(str, 6) & CStr(lngYear))
End If

fncExtractDate = Format(d, "dd/mm/yyyy")

End Function

If this post helps click Yes

 

[Jacob Skaria]

Public Function fncExtractDate(str As String) As Date
Dim d As Date

If InStr(str, "A") = 0 And InStr(str, "*") = 0 Then
d = CDate(str)
Else
lngYear = IIf(CInt(Mid(str, 7, 2)) <= CInt(Format(Date, "YY")), _
Left(Year(Date), 2) & Mid(str, 7, 2), _
(CInt(Left(Format(Date, "YYYY"), 2)) - 1) & Mid(str, 7, 2))
d = CDate(Left(str, 6) & CStr(lngYear))
End If

fncExtractDate = Format(d, "dd/mm/yyyy")

End Function

 

[Charles Williams]

Worksheet functions cannot change the formatting of the calling cell (or any
other cell).
They can only return a value.

You could return a formatted string, but then any other formula that
referenced the result would not work unless it converted the string back to
a date

regards
Charles