DATES IN VBA
Last issue we discussed the way Microsoft Office software handles
dates. However, VBA handles dates a little bit differently. Well, I take
that back. The dates and times are handled the same way, but representing
them may be a little different sometimes. For example, suppose you wanted
to keep track of the finish times in your local “Tortoise and Hare
Marathon Run.” In Excel you would set the cell format to keep track of
total hours and minutes (custom format [H]:mm).
In VBA the format doesn’t work. In other words, if you have a textbox and
try to run the following code, you’d expect to see 30:43 since that is
1.28 days:
TextBox1.Text = Format(1.28, "[H]:mm").
However, :12 shows up in the textbox. Where did that come
from?
There are a couple of ways to “smudge” the answer and display what you
really think should be in there. I use a formula similar to:
TextBox1.Text = Int(1.28 * 24) & ":" & Format(1.28 * 1440 - Int(1.28
* 24) * 60, "00")
Like I say, this is just one of many ways. I’d be interested to see
how you’ve handled similar problems. Please email them to me.
Remember, $5.00 to anyone who sends me a question or idea that I can
use in this article.
Chad K. Welch works as a technician/enabler in
Utah. He is available for
consulting or application programming with Microsoft Office and VBA.
Do you have a question or
tip you’d like to have Chad address in this column? Send an
email to linda@personal-computer-tutor.com
or contact him directly at chad@welchkins.com for more information.
,
