HomeProgram

How to Use Excel VBA Variable Data Types

Part 1 of 4:

Variable Data Types

  1. How to Use Excel VBA Variable Data Types Picture 1
    Learn from the above table the Excel VBA Variable Data Types.

  2. Learn that generally, it's best to use the data type that uses the smallest number of bytes yet still can handle all the data that will be assigned to it. When VBA works with data, execution speed is partially a function of the number of bytes that VBA has at its disposal. Thus, the fewer bytes used by data, the faster VBA can access and manipulate the data.
    1. The Decimal data type is rather unusual because you can't actually declare it. It is a subtype of a variant. You need to use the VBA CDec function to convert a variant to the Decimal data type.
  3. Learn various Assignment Statements. The following list contains some examples of assignment expressions that use various variable types. The variable names are to the left of the equals sign. Each statement assigns the value to the right of the equals sign to the variable on the left.
      1. DataEntered = True
      2. DateStart = #04/18/2015#
      3. Interest_Rate = 0.025
      4. LoanAmount = 2000000.00
      5. MyNumber = TheNumber * 1.25
      6. UserName = 'Terrence O'Malley'
      7. x = 10
      8. x = x + 1
  4. Learn that VBA has many reserved words, which are words that you can't use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next or True might make a very descriptive variable name, the following instructions generate a syntax error: Next = 64; True = True.
  5. Learn that syntax error messages aren't always descriptive. If an instruction produces a strange error message, check the VBA Help system to ensure that your variable name doesn't have a special use in VBA. If the Auto Syntax Check option is turned on you get the error: Compile error: Expected: variable. If Auto Syntax Check is turned off, attempting to execute this statement results in: Compile error: Syntax error. It would be more helpful if the error message were something like Reserved word was used as a variable.
  6. Learn that for worksheet calculation, Excel uses the Double data type, so that's a good choice for processing numbers in VBA when you don't want to lose any precision. For integer calculations, you can use the Integer type which is limited to values less than or equal to 32,767. Otherwise, use the Long data type. Using the Long data type even for values less than 32,767 is recommended, because this data type may be a bit faster than using the Integer type. When dealing with Excel worksheet row numbers, you want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.
Part 2 of 4:

Declaring Variables

  1. If you don't declare the data type for a variable that you use in a VBA routine, VBA uses the default data type, Variant. Data stored as a Variant changes type, depending on what you do with it.
    1. The following procedure demonstrates how a variable can assume different data types:
      1. Sub VariantDemonstration()
      2. TheVar = '124'
      3. TheVar = MyVar / 2
      4. TheVar = 'Answer: ' & TheVar
      5. MsgBox TheVar
      6. End Sub
      7. In the VariantDemonstration procedure, TheVar starts out as a three-character string. Then this string is divided by two and becomes a numeric data type. Next, TheVar is appended to a string, converting TheVar back to a string. The MsgBox statement displays the final string: Answer: 62.
    2. To further demonstrate the potential problems in dealing with Variant data types, try executing this procedure:
      1. Sub VariantDemonstration2()
      2. TheVar = '124'
      3. TheVar = TheVar + TheVar
      4. TheVar = 'Answer: ' & TheVar
      5. MsgBox TheVar
      6. End Sub
      7. The message box displays Answer: 124124. This is probably not what you wanted. When dealing with variants that contain text strings, the + operator performs string concatenation.
  2. You can use the VBA TypeName function to determine the data type of a variable. Here's a modified version of the previous procedure. This version displays the data type of TheVar at each step.
    1. You see that it starts out as a string, is then converted to a double, and finally ends up as a string again.
      1. Sub VariantDemonstration3()
      2. TheVar = '124'
      3. MsgBox TypeName(TheVar)
      4. TheVar = TheVar / 2
      5. MsgBox TypeName(TheVar)
      6. TheVar = 'Answer: ' & TheVar
      7. MsgBox TypeName(TheVar)
      8. MsgBox TheVar
      9. End Sub
Part 3 of 4:

User-Defined Data Types

  1. Learn that VBA lets you create custom, or user-defined, data types. A user-defined data type can ease your work with some types of data.
    1. For example, if your application deals with customer information, you may want to create a user-defined data type named CustomerData:
      1. Type CustomerData
      2. Company As String
      3. Contact As String
      4. RegionCode As Long
      5. Sales As Double
      6. End Type
  2. You define custom data types at the top of your module, before any procedures.
  3. After you create a user-defined data type, you use a Dim statement to declare a variable as that type. Usually, you define an array. For example:
    1. Dim Customers(1 To 100) As CustomerData
    2. Each of the 100 elements in this array consists of four components (as specified by the user-defined data type, CustomerData).
    3. You can refer to a particular component of the record as follows:
      1. Customers(1).Company = 'Ace Tools'
      2. Customers(1).Contact = 'Tim Roberts'
      3. Customers(1).RegionCode = 1
      4. Customers(1).Sales = 150000.00
    4. You can also work with an element in the array as a whole. For example, to copy the information from Customers(1) to Customers(2), use this instruction:
      1. Customers(2) = Customers(1)
    5. The preceding example is equivalent to the following instruction block:
      1. Customers(2).Company = Customers(1).Company
      2. Customers(2).Contact = Customers(1).Contact
      3. Customers(2).RegionCode = Customers(1).RegionCode
      4. Customers(2).Sales = Customers(1).Sales
Part 4 of 4:

Arrays

  1. An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number. For example, you can define an array of 12 string variables so that each variable corresponds to the name of a month. If you name the array MonthNaming, you can refer to the first element of the array as MonthNaming(0), the second element as MonthNaming(1), and so on, up to MonthNaming(11).
  2. You declare an array with a Dim or Public statement, just as you declare a regular variable. You can also specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index number — all inside parentheses.
    1. For example, here's how to declare an array comprising exactly 100 integers:
      1. Dim TheArray(1 To 100) As Integer
  3. When you declare an array, you need specify only the upper index, in which case VBA assumes that 0 is the lower index.
    1. Therefore, the two statements that follow have the same effect:
      1. Dim TheArray(0 to 100) As Integer
      2. Dim TheArray(100) As Integer
      3. In both cases, the array consists of 101 elements.
  4. By default, VBA assumes zero-based arrays. If you would like VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module: Option Base 1
  5. When you want to declare multidimensional arrays, do the following:
    1. The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimensions, although you'll rarely need more than three dimensions (a 3-D array).
    2. The following statement declares a 100-integer array with two dimensions (2-D):
      1. Dim TheArray(1 To 10, 1 To 10) As Integer
      2. You can think of the preceding array as occupying a 10-x-10 matrix. To refer to a specific element in a 2-D array, you need to specify two index numbers. For example, here's how you can assign a value to an element in the preceding array:
      3. TheArray(3, 4) = 125
  6. Following is a declaration for a 3-D array that contains 1,000 elements (visualize this array as a cube):
    1. Dim TheArray(1 To 10, 1 To 10, 1 To 10) As Integer
      1. Reference an item within the array by supplying three index numbers:
      2. TheArray(4, 8, 2) = 0
  7. When you want to declare a dynamic array, use a blank set of parentheses. A dynamic array doesn't have a preset number of elements.
    1. Dim TheArray() As Integer
    2. Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array. You can use a variable to assign the number of elements in an array.
    3. Often the value of the variable isn't known until the procedure is executing. For example, if the variable x contains a number, you can define the array's size by using this statement:
      1. ReDim TheArray (1 to x)
    4. You can use the ReDim statement any number of times, changing the array's size as often as you need to. When you change an array's dimensions the existing values are destroyed. If you want to preserve the existing values, use ReDim Preserve. For example:
      1. ReDim Preserve TheArray (1 to z)
3.9 ★ | 21 Vote
dataarraytype

You should read it

May be interested

  • Steps to use Pareto chart in ExcelSteps to use Pareto chart in Excel
    pareto charts in excel are also commonly used in excel with other excel chart types. this chart type is created from the pareto principle, including vertical bars and horizontal lines to represent data in excel data tables.
  • MS Excel 2003 - Lesson 2: Format spreadsheet dataMS Excel 2003 - Lesson 2: Format spreadsheet data
    tipsmake.com will guide you how to format data in excel 2003, detailing font options, data types, how to align data in cells, spreadsheets.
  • How to Automatically Report on ExcelHow to Automatically Report on Excel
    today's tipsmake will guide you how to automatically report data on microsoft excel. this article also talks about how to query and create reports from external sources (mysql, postgres, oracle, etc.) right on your spreadsheet using the excel plug-in to link your spreadsheet to the source. that data. as for data stored on excel spreadsheets, we will use macro commands to create and export reports into many different file types with just one keystroke. luckily, excel has this feature built in, so you won't need to create your own algorithm.
  • Data types in SQL ServerData types in SQL Server
    below are the data types (data types) in sql server, including character strings, numbers and times / dates.
  • Types of data hiding in Excel - Part 2: How to hide cells, overflow textTypes of data hiding in Excel - Part 2: How to hide cells, overflow text
    continuing the 'extremely useful guide to all types of data hiding in excel'. tipsmake would like to introduce to you part 2: how to hide cells, overflow text ...
  • limits.h in Climits.h in C
    the file header named limits.h in library c defines the various attributes of different variable types. macros, defined in this header, limit the value of various variable types such as char, int, and long.
  • 'Moving' chart in Excel'Moving' chart in Excel
    do you see a motion chart in excel? with a simple, self-created viual basic program, you can make your chart move to look lively. suppose, you have a data table with column a containing the variable x (from a4: a19).
  • Instructions to stamp negative numbers in ExcelInstructions to stamp negative numbers in Excel
    during the spreadsheet process on excel, we will have to work with many types of numbers, including negative numbers. and if you want to differentiate negative numbers from other numbers in the data sheet, you can format close or red brackets to distinguish them.
  • Reference in C ++Reference in C ++
    a reference variable is an alias, which is another name for an existing variable. when a reference is initialized with a variable, then either the variable name or the reference name can be used to reference that variable.
  • Complete guide to Excel 2016 (Part 9): Working with multiple spreadsheetsComplete guide to Excel 2016 (Part 9): Working with multiple spreadsheets
    excel workbook file is a collection of spreadsheet types (can contain from 1 to 255 spreadsheets) such as: data, graphs, macro, . and often related to each other.

Program