Format Function In VBA

**Transformer** · 07-02-2013, 01:30 PM

UsefulGyaan Has Posted the Following On 07-02-2013 01:51 PM:

Many times we encouter with such a situation when we have a “String” and from this string we need to fetch a value in fromat of *Date,Percentage,Currnecy,Fixed, Scientific,Yes/No,On/Off etc. Here comes the most used function of VBA, The “Format() Function”. Syntax: The syntax for Format() function is: Format(Expression,[Format]) Expression is the String which you have […]

Function For Strings

**snb** · 07-02-2013, 04:34 PM

**Rick Rothstein** · 07-03-2013, 09:38 AM

Originally Posted by snb

The result of format is always a string.

Technically, the result of Format is a Variant with a sub-type of String. If you affix a $ sign after it, Format$, then it returns a pure String. By the way, this Variant/String result structure is true for all (at least I think it is all) the VBA String functions (such as Mid, Chr, Str, etc.) that return text values.

**snb** · 07-03-2013, 03:00 PM

@Rick

x3 = Format$(1234)

results in the same kind of variable as Format(1234) (see the locals window).

**Rick Rothstein** · 07-03-2013, 07:12 PM

Originally Posted by snb

@Rick

x3 = Format$(1234)

results in the same kind of variable as Format(1234) (see the locals window).

Wow, either Google has gotten worse at finding stuff, or Microsoft pulled most of the non-VB.NET documentation, but it used to be much easier to find online documentation about the difference between VB/VBA functions with and without the $ sign. I did locate this from Microsoft's Office 2000 documentation, however...

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

...see the second paragraph (starts with the word Note in bold letters).

**snb** · 07-03-2013, 08:21 PM

That text doesn't match the locals window in Excel's VBeditor.
So MS has bugs in documentation too.

**Rick Rothstein** · 07-03-2013, 09:36 PM

Originally Posted by snb

That text doesn't match the locals window in Excel's VBeditor.

I do not think that is true. The locals window says Variant/String which I believe means that the variable was declared as, or default to being, a Variant and it currently holds a String value... that says nothing about what Format or Format$ returned, only what ended up in the Variant variable in the end. Assigning a pure String value to a Variant won't convert the Variant to a String... it is still a Variant and that Variant currently contains a String value. For example, if you did this...

x4 = "123"

the locals window still reports Variant/String. Similarly, if you did this...

x5 = CInt(123#)

the locals window would say Variant/Integer meaning the variable was declared as, or defaulted to being, a Variant which currently holds an Integer value. The testing at the following link was for the compiled version of VB, so I do not know how convincing its result will be to you given we are talking about VBA within Excel, but I offer it to you to show that, at least originally, there was a difference between using the $ and leaving it off of String functions...

Faster Visual Basic Programs

**snb** · 07-04-2013, 02:19 AM

Code:

Sub M_snb()
    Dim x00 As String, x01 As String, x02, x03
    
    x00 = Format(1234)
    x01 = Format$(1234)
    
    x02 = Format(1234)
    x03 = Format$(1234)
    
    x04 = Format(1234)
    x05 = Format$(1234)
    MsgBox VarType(x00) & vbTab & TypeName(x00) & vbLf & VarType(x01) & vbTab & TypeName(x01) & vbLf & VarType(x02) & vbTab & TypeName(x02) & vbLf & VarType(x03) & vbTab & TypeName(x03) & vbLf & VarType(x04) & vbTab & TypeName(x04) & vbLf & VarType(x05) & vbTab & TypeName(x05)
    
End Sub

In VBA at least any 'difference' is absent; see also the locals window.
Of course in the local window only x00 and x01 are 'real 'strings'.

**Transformer** · 07-04-2013, 09:16 AM

Hi snb,

There is a difference.Check the following code:

Code:

Sub Test()
    
    Dim dteToday    As Date
    Dim strToday    As String
    
    dteToday = Format(Now())
    strToday = Format$(Now())
    
    MsgBox dteToday & vbTab & TypeName(dteToday) & vbNewLine & strToday & vbTab & TypeName(strToday)
    
End Sub

**Transformer** · 07-04-2013, 09:28 AM

Originally Posted by snb

You mistakenly think format is designed for 'strings'. It isn't. It also applies to numbers (including date/times).
The result of format is always a string.

Hi snb thanks for the feedback