Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

A Format function for VbScript

A Format function for VbScript

A Format function for VbScript

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:


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
and some examples of using it:


MsgBox vbsFormat(Now(), "dd-MMM-yyyy") ' basic example
    MsgBox vbsFormat(Now(), "MM_dd_yyyy_HHmm") ' as per the format wanted in thread329-1566954: How to figure out Type mismatch: 'cdate' error?
    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)
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.

RE: A Format function for VbScript

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=""http://www.w3.org/1999/XSL/Transform"">" _
               & "<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")

RE: A Format function for VbScript

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.

RE: A Format function for VbScript

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

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: How to get the best answers before post

RE: A Format function for VbScript

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' winky smile

RE: A Format function for VbScript

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. winky smile

RE: A Format function for VbScript

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.
            Set .Fields(0).DataFormat = SDFMT
        End With
    End Sub

    Private Sub Class_Terminate()
    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


Set FMT = New Formatter

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

RE: A Format function for VbScript

>using an absurd amount of it

Indeed! smile 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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close