Format Function In VBA

Printable View

07-02-2013, 01:30 PM
Transformer

Format Function In VBA

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 […]http://stats.wordpress.com/b.gif?hos...an&ref=&feed=1

Function For Strings
07-02-2013, 04:34 PM
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.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
ttps://www.youtube.com/watch?v=LP9fz2DCMBE
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
ttps://www.youtube.com/watch?v=bFxnXH4-L1A
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
ttps://www.youtube.com/watch?v=GqzeFYWjTxI
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
07-03-2013, 09:38 AM
Rick Rothstein

Quote:

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.
07-03-2013, 03:00 PM
snb

@Rick

x3 = Format$(1234)

results in the same kind of variable as Format(1234) (see the locals window).
07-03-2013, 07:12 PM
Rick Rothstein

Quote:

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).
07-03-2013, 08:21 PM
snb

That text doesn't match the locals window in Excel's VBeditor.
So MS has bugs in documentation too.
07-03-2013, 09:36 PM
Rick Rothstein

Quote:

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
07-04-2013, 02:19 AM
snb

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'.
07-04-2013, 09:16 AM
Transformer

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
07-04-2013, 09:28 AM
Transformer

Quote:

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 :)
Actually Theodoulus decided to write it in parts....so this time he has written about Strings in future he will be writing about others.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
07-04-2013, 09:37 AM
Rick Rothstein

Quote:

Originally Posted by Transformer

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

I am afraid that does not show what you think it shows. TypeName is reporting the data type the variables were declared as, not the variable type being returned from the Format/Format$ functions. I think you will be able to see this more clearly from this modification to the above code...

Code:

Sub Test2() Dim dteToday As Date Dim strToday As String dteToday = "1/23/2013" strToday = DateSerial(2013, 1, 23) MsgBox dteToday & vbTab & TypeName(dteToday) & vbNewLine & strToday & vbTab & TypeName(strToday) End Sub

I am not sure there is a good way to show that Format returns a Variant with a sub-type of String and that Format$ returns a pure String without invoking some Windows API functions (which I am not really all that fluent with anymore). I would point out to snb, though, that the first link I gave was to the online documentation for Office 2000, so it was referring to VBA, not the compiled version of VB. Of course, snb seems to believe the documentation is in error while I do not. The compiled version of VB and VBA share a common core functionality and those string functions (with and without the $ sign) is part of that core... I find it hard to believe that Microsoft would have tinkered with the core functionality of the language, when there would have been no compelling reason to do so, just to make use of it in VBA. Hence, I believe my original statement in this thread to be accurate, but I cannot find a good way to "prove" it.
07-04-2013, 10:20 AM
Transformer

Ohhh %p.....got it
07-04-2013, 12:37 PM
Transformer

When we start typing a function then its description is displayed with it....in case of Format$, it can be seen that it is declared as String and for Format there is no data type means Variant.
As of now, i dont have any example for format function but difference can be seen in the following example:

Left(NULL,3) returns NULL and Left$(NULL,3) throughs a run time error.

Because a Variant can be Null but String can not be NULL.
07-04-2013, 01:00 PM
Rick Rothstein

Quote:

Originally Posted by Transformer

When we start typing a function then its description is displayed with it....in case of Format$, it can be seen that it is declared "as String" and for Format there is no data type means Variant.

YES! PERFECT! You are absolutely correct... VBA's intellisense does make clear the difference in exactly the way you noted. Format$ is clearly identified as returning a String data type whereas Format (no $ sign) shows no data type meaning it returns a default Variant data type, the fact that its sub-type is a String value can be seen from something like this....

MsgBox TypeName(Format(1))

That was very observant of you to notice the intellisense description like that... I must have looked at it thousands upon thousands of times across the years and it just never registered with me. I would also point out that what you observed for Format/Format$ also is displayed for the other String functions that return a text value (Mid/Mid$, String/String$, Chr/Chr$, etc.).
07-04-2013, 03:51 PM
snb

1 Attachment(s)

I think this only illustrates that the restraints for the argument of format are different from the restrainst of format$ 's argument.
That has no relation to the type of the resulting variable, which this discussion was originally about.

Attachment 947