When and how to use the correct data type such as Integer/long/String in VBA?|VBA & Macros|Excel Forum|My Online Training Hub

Hi Chiew,

Normally, you should find the answer in a book, it's hard to condensate an answer that can take a few pages in a short message.

You can also refer to: https://docs.microsoft.com/en-.....pe-summary

Depends on the data type you assign to variables.

Use Double if your values have decimal places, like 123.48.

The Integer and Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647. 

Boolean (logical) can be used in data comparisons, for example: Dim Bol as Boolean Bol=Instr(1,"Sample text", "amp",vbtextcompare)>0. Bol will be true if "amp" is found in the other string.

A String holds text. SheetName="Sheet1" ThisWorkbook.Worksheets(SheetName).Range("A1").Value="text"

Use Variants for arrays, or when your data type changes during processing.

For example, SheetNames="Sheet1, Sheet2, Sheet3" is a string.

If you use SheetNames=Split(SheetNames, ", ") , then SheetNames becomes an array because of the Split Function that returns an array always.

Of course, you can use separate declarations that holds each specific data types:

Dim SheetNames as string: SheetNames="Sheet1, Sheet2, Sheet3"

Dim SplitNames as Variant: SplitNames=Split(SheetNames, ", ")

An Object can be anything: a button, a sheet, a cell, a range, a workbook, a dictionary, a shape. You can assign any reference type (string, array, class, or interface) to an Object variable. An Object variable can also refer to data of any value type (numeric, Boolean, Char, Date, structure, or enumeration).

If you don't know the type of your data, use Debug.Print TypeName(object)

It's faster to use early binding than late binding (late binding means that binding occurs in run time, in early binding the binding occurs when the program is compiled). Early binding example:

Dim xlApp As Excel.Application

Dim Dict as Dictionary 'needs a reference to scripting dictionary

Set xlApp = New Excel.Application

Set Dict = New Dictionary

Late Binding:

Dim xlApp As Object

Dim Dict as Object 'does not need a reference to scripting dictionary

Set xlApp = CreateObject("Excel.Application")

Set Dict= CreateObject("Scripting.Dictionary")