Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing cell reference and formatting cell in VBA function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing cell reference and formatting cell in VBA function
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 "graham_s" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing cell reference and formatting cell in VBA function
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 "graham_s" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing cell reference and formatting cell in VBA function
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 "graham_s" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference another cell in conditional formatting | Excel Worksheet Functions | |||
Formatting the value of a cell reference in a formula | Excel Worksheet Functions | |||
Hyperlink - Passing a cell reference | Excel Worksheet Functions | |||
CELL Function: cell reference by formula | Excel Worksheet Functions | |||
passing reference arguments to VBA function | Excel Programming |