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

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

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.

@Rick

x3 = Format$(1234)

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

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).

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

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

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'.

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

---

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