A Format function for VbScript

[strongm]

As someone who comes from a VB background I sometimes get frustrated with some of the design decisions taken with VBScript. Why, for example, did MS decide not include the rather useful Format function and instead include the less powerful and less flexible Formatxxxxx functions?

And, looking at some of the sometimes convoluted methods other people in this forum have adopted to get a date or number just how they want it, it looks like I'm not the only one.

Whilst I was investigating leveraging some of .Net's various collection classes for use in VB (and VBScript) it suddenly occurred to me that we also have access to .Net's System.Text.StringBuilder ... which means we can write ourselves a fairly succint Format function that works much like VBs and includes a bunch of additional functionality:

[blue]Public Function vbsFormat(Expression, Format)
    vbsFormat = CoreFormat("{0:" & Format & "}", Expression)
End Function

' Allows more of the .NET formatting functionality to be used directly if required
Public Function CoreFormat(Format, Expression)
    CoreFormat = Expression
    On Error Resume Next
    With CreateObject("System.Text.StringBuilder")
        .AppendFormat Format, Expression
        If Err=0 Then CoreFormat = .toString
    End With
End Function[/blue]

and some examples of using it:

[blue]MsgBox vbsFormat(Now(), "dd-MMM-yyyy") ' basic example
    MsgBox vbsFormat(Now(), "MM_dd_yyyy_HHmm") ' as per the format wanted in thread329-1566954
    MsgBox vbsFormat(12.5, "Numeric example: 0.00") ' basic numeric
    Msgbox vbsFormat(02075551234,"(0###)###-####")
    ' demonstrate a few more advanced numerical custom format
    MsgBox vbsFormat(6, "+00.00;(0.00);Eek - it's Nil!")
    MsgBox vbsFormat(-6, "+00.00;(0.00);Eek - it's Nil!")
    MsgBox vbsFormat(0, "+00.00;(0.00);Eek - it's Nil!")
    ' Now demonstrate fractionally more advanced formatting capability provided by .NET
    MsgBox CoreFormat("Various formats all in the same line: {0:##0.0} or {0:£##0.00} and even {0:00.00%}", 1.25)[/blue]

For some of the addtional formatting features you'll need to look at the .Net formatting documentation on MSDN. This is probably as good a place as any to start

Hope some of you find this useful.

 

[dilettante]

Lots of wild alternatives out there:

Option Explicit

Function FormatNum(ByVal Value, ByVal Style)
    Dim docStyle, docValue

    Set docStyle = CreateObject("MSXML2.DOMDocument.4.0")
    With docStyle
        .async = False
        .LoadXML "<xsl:stylesheet version=""1.0"" " _
               & "xmlns:xsl=""[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"">"[/URL] _
               & "<xsl:output method=""text"" />" _
               & "<xsl:template match=""/"">" _
               & "<xsl:value-of select='format-number(v, """ _
               & Style _
               & """)' />" _
               & "</xsl:template>" _
               & "</xsl:stylesheet>"
    End With
    Set docValue = CreateObject("MSXML2.DOMDocument.4.0")
    With docValue
        .async = False
        .LoadXML "<v>" & CStr(Value) & "</v>"
        FormatNum = .transformNode(docStyle)
    End With
End Function

MsgBox FormatNum(123456.789, "###,##0.00")

 

[strongm]

Surely that only formats numbers? It doesn't accept literals, support any exponential formatting, and doesn't support the zero result grouping. Nor does it support any predefined, 'standard' numeric formats. And certainly doesn't handle dates or times or any of their predefined formats

In other words it is only fractionally more useful than VBScript's FormatNumber function. Which is where we started ... well, it's where I started: none of the (simple)solutions in the wild that I found matched the capability of VB's all-purpose Format function, nor alternatively added enough additional functionality to make up for any shortcomings that they suffered.

 

[HarleyQuinn]

Hmm ,hadn't thought about using the particularly useful StringBuilder class in VB. I like it...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[mrmovie]

thanks Gents, will pilfer both of those. you can always rely on dilettante for something on the exotic side. to be honest strongm your solution looks too 'german' ;-)

 

[dilettante]

You could do things like date/time formatting using XSL as well... using an absurd amount of it. It was just meant as an example of a trip into the Twilight Zone to get a simple task accomplshed, and not a really serious approach.

It would be fantastically lighter weight than loading the (40MB now?) .Net runtime though. Lighter, but not buying you as much by far of course.

Too bad I can't think of a way to leverage the Jet Expression Service to get at a few of the functions in there. ;-)

 

[dilettante]

Another oddball approach:

'Requires msstdfmt.dll, part of Visual Studio 6.0 and
'not meant for general redistribution.
Option Explicit

Class Formatter
    Private SDFMT, RS

    Private Sub Class_Initialize()
        Set SDFMT = CreateObject("MSSTDFMT.StdDataFormat")
        Set RS = CreateObject("ADODB.Recordset")
        With RS
            .Fields.Append "V", 12 'adVariant.
            .Open
            .AddNew
            Set .Fields(0).DataFormat = SDFMT
        End With
    End Sub

    Private Sub Class_Terminate()
        RS.Close
    End Sub

    Public Function Format(ByVal Value, ByVal Style)
        SDFMT.Format = Style
        With RS.Fields(0)
            .Value = Value
            Format = .Value
        End With
    End Function
End Class

Dim FMT

Set FMT = New Formatter

MsgBox FMT.Format(Now(), "yyyy-mmm-dd hh:nn:ss")
MsgBox FMT.Format(123456.789, "###,##0.00")

 

[strongm]

>using an absurd amount of it

Indeed! Hence my very deliberate reference to (simple) solutions. Sure, I found loads of long, complex functions that tried to get close to the Format function