VBA Statement - Redim and Redim Preserve

Redim statement is to reallocate storage space for dynamic array variables and declare at procedure level within your Sub and End Sub. Redim statement only can be use after we declare Dim Statement for array for example Dim MyVar() As String and data remain unchanged. Array number in bracket is not specify.

Syntax : Redim Variables Name As Data Type Or
               Redim Preserve Variables Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Redim and Variable name, to explain in detail refer below:

Case Study 1:
Dim myVar(5) As String
Lower Bound (LBound) control by Option Base 1 (Declare at module level) if present then LBound = 1 otherwise 0, Upper Bound (UBound) is 5. For this case we don't have to use ReDim statement because LBound and UBound already specify. If we try to use myVar(6) or more then Run time error '9' : Subscript out of range. If we try to resize by using Redim myVar(6) or more then Compile error: Array already dimensioned.

Case Study 2:
Dim myVar(5 to 10) As String
This case same as above except Lower bound already specify equal to 5 and Upper Bound equal to 10. Redim is not necessary.

Case Study 3:
Dim myVar() As String
This is refer to dynamic array and Redim Statement is compulsory. For example we already Redim myVar(1) and assigned the value, later we Redim myVar(2) and assigned the value. The myVar(1) value will be deleted or erase. To avoid this we must use Redim Preserve myVar(1) and Redim Preserve myVar(2). For Redim Preserve once we reverse the sequence the data also lost for the sub sequence number.

Note:
Redim Statement - Will erase all previous data.
Redim Preserve Statement - Will not erase previous data.

Read more about Redim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Redim-statement
Other Reference-Redim-statement
Other Reference-Redim-preserve

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Dim with Intellisense menu

To write Dim Statements:

To make your VBA coding more easy after typing Dim VarName As follow by space-bar the Intellisense drop-down menu will appeared as below picture. Continue typing this Intellisense menu is giving more narrow suggestion of Data type for you to select. Use mouse or arrow down key to select and press Space-bar or Tab button or Mouse Double click to confirm.

If the Intellisense drop-down menu not appeared just press Ctrl + Space-Bar Button Or Ctrl + J button.

To Access Variables in Project:

Same as above just click empty area below your variables declaration just press Ctrl + Space-Bar Button Or Ctrl + J button. Continue typing and from drop down menu use arrow down key or mouse to select and press Space-bar or Tab button or Mouse Double click to confirm. Refer below picture for details.

Note:
By using this method we reduce typo error in our coding. This Intellisense drop down menu also appear after "." (Dot).

Read more about Dim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Dim-statement
Other Reference-Dim-statement

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Dim

Dim statement is to declares variables and allocates storage space. For example Dim StrVar as String, Dim Int As integer and etc which is assigning any variables to data type or object.

Syntax : Dim Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Dim and Variable name, to explain in detail refer below:

1. Dim VarName
   This to declare VarName as default which is Variant.
   
2. Dim VarName as String
   This to declare VarName as String.
3. Dim VarName1 as string, VarName2 as string
   This to declare both VarName1 and 2 as String.
4. Dim VarName1,VarName2 As Integer,VarName3 as Integer
   This to declare VarName1 default (Variant),VarName2 as integer and VarName3 as integer.

 Basically these 4 methods we can use to declare Variables.

VBA Vode:

Option Explicit
Sub Examples_DimDeclares()
    
    Dim myVar
    Dim MyVar1, MyVar2
    Dim MyVar3 As String, myVar4 As Integer
    Dim MyVar5, MyVar6 As Integer, MyVar7 As String

End Sub

Note:

1. Don't use same Variable name with number because more likely become array. The above example just for reference.
2. If we declare Variables with same name then Compile error: Duplicate declaration in current scope.

Read more about Dim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Dim-statement
Other Reference-Dim-statement

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA function inStrRev

Instrrev function is to get the position of the first occurrence of one string within another. The search String is from right to left inside another String to get the position for example we wish to get the position of back slash "\" from path "C:\Users\UserName\Desktop\MyBook.xlsx", if start from -1 then the position of back slash is 26 but if we start from 5 then the position is 3.

Syntax : InStr(String Check, String Match, Start, Compare Value) , Returns : Long

Constant	Value	Description
vbUseCompareOption	-1	Performs a comparison by using the setting of the Option Compare statement.
vbBinaryCompare	0	Performs a binary comparison.
vbTextCompare	1	Performs a textual comparison.
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

Normally for Compare Value we use 0 (case sensitive and default) or 1.Usually we omitted this value to let default which is equal to 0 (binary comparison). For more clear explanation refer below picture.

VBA Vode: (Example to get Postion of Back slash \)

Option Explicit
Sub Examples_InStrrev()
    Dim StrLink As String
    StrLink = "C:\Users\UserName\Desktop\MyBook.xlsx"
    
    'To Get MyBook.xlsx from above string
    Debug.Print Right(StrLink, Len(StrLink) - InStrRev(StrLink, "\", -1, 1))
    
    'To Get xlsx from above string
    Debug.Print Right(StrLink, Len(StrLink) - InStrRev(StrLink, ".", -1, 1))
End Sub

VBA Vode:

Option Explicit
Sub Examples_InStrrevTest()
    Dim StrLink As String
    Dim iPos As Long
    
    StrLink = "C:\Users\UserName\Desktop\MyBook.xlsx"
    iPos = InStrRev(StrLink, "Name", -1)
    If iPos = 0 Then
        MsgBox "Keyword Name not found."
    Else
        MsgBox "Keyword Name found Start at= " & iPos
    End If
End Sub

Note:
Usually we use InStrrev Function combine with other functions like Left,Right,Mid,Len and etc.

Read more about Instrrev function, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Instrrev-function
Other Reference-Instrrev-function.htm

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Function InStr

Instr function is to get the position of the first occurrence of one string within another. The search String is from left  to right inside another String to get the position for example we wish to get the position of back slash "\" from path "C:\Users\UserName\Desktop\MyBook.xlsx", if start from 1 then the position of back slash is 3 but if we start from 5 then the position is 9.

Syntax : InStr(Start,String1,String2,Compare Value) , Returns : Long

Constant	Value	Description
vbUseCompareOption	-1	Performs a comparison by using the setting of the Option Compare statement.
vbBinaryCompare	0	Performs a binary comparison.
vbTextCompare	1	Performs a textual comparison.
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

Normally for Compare Value we use 0 (case sensitive and default) or 1.Usually we omitted this value to let default which is equal to 0 (binary comparison). For more clear explanation refer below picture.

VBA Vode: (Example to get Postion of Back slash \)

Option Explicit
Sub Examples_InStr()
    Dim StrLink As String
    StrLink = "C:\Users\UserName\Desktop\MyBook.xlsx"
    
    'To Get C:\ from above string
    Debug.Print Left(StrLink, InStr(1, StrLink, "\", 1))
    
    'To Get xlsx from above string
    Debug.Print Right(StrLink, Len(StrLink) - InStr(1, StrLink, ".", 1))
End Sub

VBA Vode: (Example to check existance of Name keyword)

Option Explicit
Sub Examples_InStrTest()
    Dim StrLink As String
    Dim iPos As Long
    
    StrLink = "C:\Users\UserName\Desktop\MyBook.xlsx"
    iPos = InStr(1, StrLink, "Name")
    If iPos = 0 Then
        MsgBox "Keyword Name not found."
    Else
        MsgBox "Keyword Name found Start at= " & iPos
    End If
End Sub

Note:
Usually we use InStr Function combine with other functions like Left,Right,Mid,Len and etc.

Read more about Instr function, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Instr-function
Other Reference-Instr-function.htm

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Methods - Workbook Add with Header

Excel.workbooks.add is to create new  Workbook with template.

Syntax : expression.Add (Template) , Returns : New Workbooks with template

Refer previous post for more details Here.

Below Example is to Create New Workbook with Header:

VBA Vode:

Option Explicit
Sub Examples_WorkBookAdd_WithHeader()
    Dim MstWB As Workbook
    Dim SumWs As Worksheet
    Dim StrHdr As String
    Dim j As Integer
    
    Set MstWB = Workbooks.Add(1)
    Set SumWs = MstWB.Sheets(1)
    SumWs.Name = "Summary"
    StrHdr = "No.,Date,Code,Stock Name,Open,Close,Qty"
    For j = LBound(Split(StrHdr, ",")) To UBound(Split(StrHdr, ","))
        SumWs.Cells(1, j + 1) = Split(StrHdr, ",")(j)
    Next j
    
    'You May Include any code project Here
    
    With SumWs
        .Rows(1).Font.Bold = True
        .Cells.EntireColumn.AutoFit
    End With
    
    Set MstWB = Nothing
    Set SumWs = Nothing
    StrHdr = ""

End Sub

Note: The Example we use Template XlWBATemplate which is equivalent to 1. 

Read more about Excel.workbooks.add, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Excel.workbooks.add
Other Reference-Excel.workbooks.add

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Function Strconv

Strconv function another option to convert String to Lower Case, Upper case even Proper case with more option available.

Syntax : StrConv(String, Constant/Value), Returns : String

The Conversion setting as below table:

Constant	Value	Function detail
vbUpperCase	1	Converts the string to uppercase characters.
vbLowerCase	2	Converts the string to lowercase characters.
vbProperCase	3	Converts the first letter of every word in a string to uppercase.
vbWide	4	Converts narrow (single-byte) characters in a string to wide (double-byte) characters.
vbNarrow	8	Converts wide (double-byte) characters in a string to narrow (single-byte) characters.
vbKatakana	16	Converts Hiragana characters in a string to Katakana characters.
vbHiragana	32	Converts Katakana characters in a string to Hiragana characters.
vbUnicode	64	Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode	128	Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)

For example if we wish to convert from Lower case to Upper case then we use Syntax:
StrCov(String,1) or StrCov(String,vbUpperCase) the result will the same.

VBA Vode:

Option Explicit
Sub Examples_Strconv_Function()
    Dim StrTxtA As String
    
    StrTxtA = "My Car Number PRS123"
    
    Debug.Print StrConv(StrTxtA, 1) 'MY CAR NUMBER PRS123
    Debug.Print StrConv(StrTxtA, 2) 'my car number prs123
    Debug.Print StrConv(StrTxtA, 3) 'My Car Number Prs123
    'Or
    Debug.Print StrConv(StrTxtA, vbUpperCase) 'MY CAR NUMBER PRS123
    Debug.Print StrConv(StrTxtA, vbLowerCase) 'my car number prs123
    Debug.Print StrConv(StrTxtA, vbProperCase) 'My Car Number Prs123

End Sub

Note:
Basically to convert String to Upper case we use Ucase function, String to Lower case we use Lcase function and to convert String to Proper case then we use StrConv function and the rest is not so importance.

Read more about Strconv function, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Strconv-function
Other Reference-Strconv-function

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Function Ucase

Ucase function is to converted String from Lowercase to Uppercase or big capital letters for example your text String is "My Name" then returns "MY NAME".

Syntax : Ucase(String) , Returns : Uppercase String

All Lowercase letters with be changed to Uppercase and others remain.

VBA Vode:

Option Explicit
Sub Examples_Ucasefunction()

    Dim StrTxtA As String, StrTxtB As Variant
    
    StrTxtA = "My Car Number PRS123"
    StrTxtB = Null
    Debug.Print UCase(StrTxtA) ' MY CAR NUMBER PRS123
    Debug.Print UCase(StrTxtB) ' Null
    
End Sub

Note: 

For Null consider not string even we use Ucase on Null it will remain unchanged. This function is very useful when we try to compare or find String inside String and we wish to ignore the case letters by forcing both String either Ucase or LCase before compare.

Read more about Ucase function, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Ucase-function
Other Reference-Ucase-function

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Function Lcase

Lcase function is to converted String from Uppercase to Lowercase or small capital letters for example your text String is "My Name" then returns "my name".

Syntax : Lcase(String) , Returns : Lowercase String

All Uppercase letters with be changed to Lowercase and others remain.

VBA Vode:

Option Explicit
Sub Examples_Lcasefunction()

    Dim StrTxtA As String, StrTxtB As Variant
    
    StrTxtA = "My Car Number PRS123"
    StrTxtB = Null
    Debug.Print LCase(StrTxtA) ' my car number prs123
    Debug.Print LCase(StrTxtB) ' Null
    
End Sub

Note:

For Null consider not string even we use Lcase on Null it will remain unchanged.

Read more about Lcase function, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Lcase-function
Other Reference-Lcase-function

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Function UBound

Ubound function is to identify highest number for array sequence normally specify by user. For example Dim myArry(10) then Ubound = 10, another example Dim myArray(5 to 10) then Ubound = 10. In case dynamic array Dim myArray() then Ubound depend on Redim statement assigned in our code. Usually we Ubound pair with Lbound function to loop array from lowest to highest value.

Syntax : Ubound(arrayname,Dimension) , Returns : Numeric

Example of Dimension
MyArray(10) = Single Dimension
MyArray(5 to 10) = Single Dimension
MyArray(2 to 10, 5 to 12) = 2 Dimension (Key in 1 or 2 for dimension)
MyArray(2 to 10, 5 to 12, 4 to 10) = 3 Dimension (Key in 1, 2 or 3) and etc

VBA Vode:

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_UBoundArray()
    
    Dim myArray1(10)
    Dim myArray2(5 To 8)
    Dim myArray3(2 To 5, 3 To 15)
    
    Debug.Print UBound(myArray1) 'Answer = 10
    Debug.Print UBound(myArray2) 'Answer = 8
    Debug.Print UBound(myArray3, 1) 'Answer = 5
    Debug.Print UBound(myArray3, 2) 'Answer = 15

End Sub

Note: 

For single dimension array we don't to specify dimension in syntax.

Microsoft Reference-Ubound-function
Other Reference-Ubound-function

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

VBA Function LBound

Lbound function is to identify lowest number for array sequence by default is 0, with Option Base 1 on top of module then become 1 otherwise specify. For example Dim myArry(10) then Lbound = 0, with Option Base 1 then LBound = 1, another example Dim myArray(5 to 10) then Lbound = 5. Usually we Lbound pair with Ubound function to loop array from lowest to highest value.

Syntax : Lbound(arrayname, Dimension) , Returns : Numeric

Example of Dimension
MyArray(10) = Single Dimension
MyArray(5 to 10) = Single Dimension
MyArray(2 to 10, 5 to 12) = 2 Dimension (Key in 1 or 2 for dimension)
MyArray(2 to 10, 5 to 12, 4 to 10) = 3 Dimension (Key in 1, 2 or 3) and etc

VBA Vode:

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_LBoundArray()
    
    Dim myArray1(10)
    Dim myArray2(5 To 10)
    Dim myArray3(5 To 10, 3 To 15)
    
    Debug.Print LBound(myArray1) 'Answer = 1 (Option Base 1)
    Debug.Print LBound(myArray2) 'Answer = 5
    Debug.Print LBound(myArray3, 1) 'Answer = 5
    Debug.Print LBound(myArray3, 2) 'Answer = 3

End Sub

Note: 

For single dimension array we don't have to specify dimension in syntax.

Microsoft Reference-Lbound-function
Other Reference-Lbound-function

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

VBA Function Join

Join function is joining a number of sub strings contained in an array. This string joint by delimiter specified in our syntax. Therefore we must understand well what array is before used. The detail array is Here. There are rules to follow:

1) Only single dimension array are allow.
2) Only Sting or Variant data type otherwise error "Run-time error '5': Invalid procedure call or argument.
3) To joint with space then we use " ".
4) To joint with nothing then we use "".
5) Be careful with start point either 0 or1 (Option Base 1), if we use Option Base 1 then we must start with 1 otherwise start with 0. Joining result is not accurate when start point is not matched.

Syntax : Join(Array, [delimiter]), Return : Array(0) delimiter Array(1) delimiter Array(2) ....

Below example is to joint String data type with delimiter comma (,):

VBA Vode:

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_JoinString()

    Dim StrVar() As String

    ReDim Preserve StrVar(1): StrVar(1) = "Tutorial VBA"
    ReDim Preserve StrVar(2): StrVar(2) = "Excel VBA"
    ReDim Preserve StrVar(3): StrVar(3) = "Programming"
    ReDim Preserve StrVar(4): StrVar(4) = "Excel Formula"
    ReDim Preserve StrVar(5): StrVar(5) = "Visual Basic"
    
    Debug.Print Join(StrVar, ",")
    
End Sub

Note:
The answer for the above is:

Tutorial VBA,Excel VBA,Programming,Excel Formula,Visual Basic

If we never put Option Base 1 at module level the result will different and not accurate because we never assigned StrVar(0) value. The result as below:

,Tutorial VBA,Excel VBA,Programming,Excel Formula,Visual Basic

Microsoft Reference-Join-function
Other Reference-Join-function

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Excel specification and limitation

Before getting serious in our coding it is better to know detail specification and limitation of Excel Workbook and Worksheet. For example:

Total Number of Rows : 1,048,576
Total Number of Columns : 16,384
Column width : 255 characters
Row Height : 409 points

All these data will help us to decide what data type to use for example when we work with rows then we use long and column integer is enough and etc. This also help us to prevent error and our code more accurate. For more detail please refer below link.

Microsoft Reference-Excel-specifications-and-limits

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Data type - Variant

Data type Variant is represent all data type for variables even more widen or general. If we declare variables without data type at the end then system consider as variant. We can choose either Dim VrtVar as Variant or Dim VrtVar only. In case we don't know what data type to use then Variant is the only choice we have.

For more example refer below table and code.
Below example we try to group the data either number, string, date or empty.

VBA Vode:

Option Explicit
Sub Examples_DataType_Variant()
    
    Dim i As Integer, StrVar
    With ActiveSheet
        i = 2
        Do
            StrVar = .Range("B" & i)
            If IsEmpty(StrVar) Then
                Range("C" & i) = "Empty"
            ElseIf IsDate(StrVar) Then
                Range("C" & i) = "Date"
            Else
                If IsNumeric(StrVar) Then
                    Range("C" & i) = "Number"
                Else
                    Range("C" & i) = "String"
                End If
            End If
            Set StrVar = Nothing
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:
Please remember Variant data type is not easy to handle because we need to test before proceed otherwise it will generate error.

Microsoft Reference-Variant-data-type
Other Reference-Variant-data-type

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

VBA Function IsObject

IsObject function is to check whether variables or identifier already assigned as object or not and return as Boolean either True or False.

Syntax : IsObject(Variables) , Return : True/False

1) Below example is for True case.

VBA Vode:

Option Explicit
Sub Examples_IsObject_True()

    Dim ObjVar
    
    Set ObjVar = ActiveWorkbook
    Debug.Print IsObject(ObjVar) 'True

End Sub

Note:
As you can see ObjVar already assigned as ActiveWorkbook, then the result is True.

1) Below example is for False case.

VBA Vode:

Option Explicit
Sub Examples_IsObject_False()

    Dim ObjVar

    Debug.Print IsObject(ObjVar) 'False
    
End Sub

Note:
The ObjVar is not assigned to anything, then the result is False.

Microsoft Reference-Isobject-function
Other Reference-Isobject-function

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba