Trainers' thoughts on Microsoft Office products

When declaring variables in VBA, you also assign a data type. But there is a trick to it: you want to choose a data type that uses the least amount of memory. In the VB Editor, which is found in the VBA help menu, I found a great chart of the data types, including what they are used for and how much memory they use.

Data Type Summary

The following table shows the supported data types, including storage sizes and ranges.

Naming Variables in VBA

Wait, that’s not it. When you declare variables, please name them properly! It will help in the long run, I promise. So, how do you name them properly? Well, first, you should put a three-letter prefix at the beginning of the variable that describes the data type. I know, you are probably asking yourself “why?” Well, let me explain. Let’s say you have an input box that pops up when the end user opens a WORD document. The input box asks the end user for her birthday. What we want the program to do is take the birthday and add 5 days to it, then display the result. So, Susie Q enters August 10, 1965. That sure does look like a date. Does VBA think it’s a date? Probably not. When Susie Q entered her birthday, you saved the result in the variable strBirthday. So, it’s a string. You now have to convert the string to a date and save it in a new variable, such as dtmBirthday. Now it’s a date! Now you can use the DateAdd function to add 5 days to it. So, by putting the three-letter prefix in front of the variable, it lets us know what type of data is in it.

Suggested VBA Variable Prefixes

Here is a list of suggestions for the three-letter prefixes:

  • byt for a BYTE
  • bln for a BOOLEAN
  • int for an INTEGER
  • lng for a LONG
  • sng for a SINGLE
  • dbl for a DOUBLE
  • cur for a CURRENCY
  • dec for a DECIMAL
  • dtm for a DATE/TIME
  • obj for an OBJECT
  • str for a STRING
  • vrt for a VARIANT
  • typ for a TYPE

To hear about the latest Office 2010 news, blogs, and training, subscribe to our newsletter. Click here to subscribe.

To learn more about data types and other VBA tricks, consider our VBA training.

No TweetBacks yet. (Be the first to Tweet this post)

One Response to “Data Types in VBA”

  1. You are missing the NEW data types from Excel 2010.

Tweetbacks

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | UK: 0808-101-3484 | From outside the USA: 315-849-2724 | Fax: 315-849-2723