All,

VBE Help for Format: "Returns a Variant (String) containing an expression
formatted according to instructions contained in a format expression." So,
you supply the expression and the format and VBE kicks back your desired
result. Is there a way to go the opposite direction, i.e. you supply the
result and VBE kicks back the format? (A quickly coded conceptual example is
below, which illustrates this idea with a date. Simply run TestDateFormat
and view the Immediate Window for the results).

In other words, think of how NumberFormat works; you can, for example, point
to a range object and apply the NumberFormat property to return (or set) the
number format for the given range. This is great if you are interacting with
the spreadsheet, but not so great if you are working internally within VBE.

Thanks,

Matthew Herbert

Sub TestDateFormat()
Dim strDte As String
Dim dteTest As Date

dteTest = 40109
strDte = DateFormat(CStr(dteTest))
Debug.Print "Ex 1: "; strDte
strDte = "01/22/04"
strDte = DateFormat(strDte)
Debug.Print "Ex 2: "; strDte
End Sub

Function DateFormat(strDate As String) As String
Dim intCnt As Integer
Dim intCntMo As Integer
Dim intCntDy As Integer
Dim intCntYr As Integer
Dim lngPosOne As Long
Dim lngPosTwo As Long
Dim strChr As String * 1
Dim strFormat As String

strChr = "/"
lngPosOne = 1
lngPosOne = InStr(lngPosOne, strDate, strChr, vbTextCompare)
lngPosTwo = InStr(lngPosOne + 1, strDate, strChr, vbTextCompare)

intCntMo = lngPosOne - 1
intCntDy = (lngPosTwo - lngPosOne) - 1
intCntYr = Len(strDate) - lngPosTwo

For intCnt = 1 To intCntMo
strFormat = strFormat & "m"
If intCnt = intCntMo Then
strFormat = strFormat & strChr
End If
Next intCnt

For intCnt = 1 To intCntDy
strFormat = strFormat & "d"
If intCnt = intCntDy Then
strFormat = strFormat & strChr
End If
Next intCnt

For intCnt = 1 To intCntYr
strFormat = strFormat & "y"
Next intCnt

DateFormat = strFormat

End Function