The VBA Format function allow for more compact coding....
Thank you Rick - I learned a bit more. I did not realise that @ in Format
only one character.
Yes, there is a big differecnce between how the spreadsheet's FORMAT
function and VBA's Format function perform; and for me, that difference is
annoyingly large. In the VBA world, each @ symbol stands for one characters
from the text in the first argument; other characters appearing in the
pattern string, with a few exceptions, simply get placed in the output
string where shown (in this case, the blank spaces).
Also, is there a reason for the trailing space in the format?
It is not like you to have surplus characters <g>
<g> Nope, I don't like surplus characters... and I haven't include any this
time either. Before I can tell you why the space is not surplus, I have to
explain the way the @ symbols get used in the pattern string.
There are two ways to fill the format pattern string when using the @
symbol... right-to-left and, of course, left-to-right. But, believe it or
not, the two methods do not work the same way when it comes to a string
longer than the number of @ symbols. First, the normal method of filling in
the @ symbols with characters from the first argument is from right to left.
This is usually used to right-justify text in a column. If you start the
pattern string off with an exclamation point (!), then the @ symbols are
filled in from left to right. Now, the difference... if you use the
exclamation point in front of the pattern string, and if your text is longer
than the number @ signs to be filled in, any excess characters are truncated
away and not displayed. However, for the non-exclamation pattern string,
characters fill from the right with each newly added character pushing the
previous character over one @ symbol position to the left to make room for
it in the right-most @ symbol's position. This goes on until the last @
symbol has been filled in. At this point, if the text contains more
characters than @ symbols, they are NOT truncated away... they are simply
concatenated on the end of the pattern string.
Okay, armed with this explanation, notice I did not use an exclamation point
in front of the pattern string AND that I provided a pattern string with
only 8 @ symbols in it even though I know there text string will be more
than 8 characters long. Doing this guarantees the excess characters will be
concatenated after the pattern string. The trailing blank space is the one
used to separate the trailing letters from the last digit in the inputted
text string.
Rick
")) Is Nothing Then Exit Sub