Data type - Type conversion functions

Type conversion functions

Each function coerces or forces an expression to a specific data type. The expression will be converted to a specific data type. For example, CBool(expression). This function converted expression to a Boolean data type which is TRUE or FALSE with condition expression is a numeric or valid string, otherwise Rune-time error '13': Type mismatch. The function name determines the return type as shown in the following:

Function	Returns the expression converted to a
CBool	Boolean data type (Boolean).
CByte	Byte data type (Byte).
CCur	Currency data type (Currency).
CDate	Date data type (Date).
CDbl	Double data type (Double).
CDec	Decimal data type (Decimal).
CInt	Integer data type (Integer).
CLng	Long data type (Long).
CLngLng	LongLong data type (LongLong).
CLngPtr	LongPtr data type (LongPtr).
CSng	Single data type (Single).
CStr	String data type (String).
CVar	Variant data type (Variant).

VBA Vode:

Option Explicit
Sub ExampleTypeOfConverstion()

    Debug.Print CBool(1) 'Return True
    Debug.Print CBool(0) 'Return False
    Debug.Print CBool(-1) 'Return True
    Debug.Print CBool(100) 'Return True
'    Debug.Print CBool("TEST") 'Rune-time error '13': Type mismatch

End Sub

Read more about Type conversion functions, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Type-conversion-functions
Other Reference-Data-types-category

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

Data type - Object

Object data type is refer to any objects in excel and store as 32-bit (4-byte) and Set statement must be use after declares otherwise Run-time error '91': Object variable or with block variable not set in syntax.

Syntax : Dim MyVar as Object
               Set MyVar = Object

Object can be any objects assigned to it for example: Workbook, Worksheet, Chart and etc
(Refer below link for Object model).

Below Example is to check whether active sheet is empty or not?

VBA Vode:

Option Explicit
Sub ToCheckActiveSheetEmpty()
    
    Dim MyWS As Worksheet
    Dim MyRng As Range
    Set MyWS = ActiveSheet
    Set MyRng = MyWS.UsedRange
    
    If WorksheetFunction.CountA(MyRng) = 0 And _
    MyWS.Shapes.Count = 0 Then
        MsgBox "Sheet " & """" & MyWS.Name & """" & " is empty", _
        vbInformation, "https://mrvba.blogspot.com"
    Else
        MsgBox "Sheet " & """" & MyWS.Name & """" & "  is not empty", _
        vbInformation, "https://mrvba.blogspot.com"
    End If

End Sub

Note: There are 2 objects inside the example. 1st Worksheet and 2nd is Range.

Read more about Object data type, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Object-data-type
Microsoft Reference-Object-model

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

VBA Range Method - Find

Range find is to finds specific information in a range.

Syntax : Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Returns : Not xxx Is Nothing(True)/Nothing(False)

Under find we have to specify What is Required and the rest is optional but for me LookIn and LookAt also importance.
LookIn:=xlFormulas, xlValues, xlComments, or xlCommentsThreaded
LookAt:=xlWhole or xlPart

If the information found then we can get the 1st location only base on search direction for example row and column index. If we need to find next location then we have to combined with FindNext.

Below example will loop keyword in activesheet at column B and find this keyword in reference sheet at column E and get additional information at B and G.

VBA Vode:

Option Explicit
Sub FindMyBlogLink()

    Dim LstRow As Long
    Dim i As Integer, j As Integer
    Dim ActSht As Worksheet, RefSht As Worksheet
    Dim FndRng As Range, FndKey As Range
    Dim StrKey As String

    'To start with active sheet
    Set ActSht = ActiveSheet
    
    'To check reference sheet available or not?
    For j = 1 To Sheets.Count
        If Sheets(j).Name = "mrvba" Then
            Set RefSht = Sheets("mrvba")
        End If
    Next j
    
    'If reference sheet exist then start searching for keyword
    If Not RefSht Is Nothing Then
        Set FndRng = RefSht.Columns("E:E")
        LstRow = ActSht.Cells.Find("*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row + 1
        i = 2
        Do
            StrKey = ActSht.Range("B" & i)
            Set FndKey = FndRng.Find(StrKey, LookAt:=xlWhole)
'            Set FndKey = FndRng.Find(StrKey, LookAt:=xlPart)
            If Not FndKey Is Nothing Then
                ActSht.Range("C" & i) = RefSht.Cells(FndKey.Row, 2)
                ActSht.Range("D" & i) = RefSht.Cells(FndKey.Row, 7)
            End If
            StrKey = ""
            Set FndKey = Nothing
            i = i + 1
        Loop Until i = LstRow
    'Exit with mesaage if reference sheet not exist.
    Else
        MsgBox "Sorry! Reference sheet name mrvba not found."
        Exit Sub
    End If
End Sub

VBA Vode: (Sample from Microsoft with FindNext)

Sub FindValue()
    
    Dim c As Range
    Dim firstAddress As String
    
    'To find number 2 in replace with 5
    With ActiveSheet.Range("A1:A500")
        Set c = .Find(2, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = 5
                Debug.Print c.Address
                Debug.Print c.Row
                Debug.Print c.Column
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
    
End Sub

Note:
By using range find is much more faster than loop entire sheet looking for keyword match.

Read more about Excel.range.find, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Excel.range.find
Other Reference-Find-and-replace-extensibility

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

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

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

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

VBA Function IsNumeric

IsNumeric function is to check whether variables is number or not and return result in Boolean either True of False. For example IsNumeric(10) result is True and IsNumeric(ABC123) of course False. If we combine with Not then the result in reverse way. For example Not IsNumeric(10) then result is False.

Syntax : IsNumeric(expression) , Return : True/False

1) Below example for True case and mostly when data type are Byte, Integer, Long, Double, Decimal, Currency and Boolean.

VBA Vode:

Option Explicit
Sub Examples_IsNumeric_True()

    Dim BytNum As Byte
    Dim CurNum As Currency
    Dim IntNum As Integer
    Dim LngNum As Long
    Dim DblNum As Double
    Dim DecNum As Variant
    Dim BlnTxt As Boolean
    
    BytNum = 5
    CurNum = 12000000
    IntNum = 25000
    LngNum = -50000
    DblNum = 5.25
    DecNum = CDec(7 / 8)
    BlnTxt = True
    
    Debug.Print IsNumeric(BytNum)
    Debug.Print IsNumeric(CurNum)
    Debug.Print IsNumeric(IntNum)
    Debug.Print IsNumeric(LngNum)
    Debug.Print IsNumeric(DblNum)
    Debug.Print IsNumeric(DecNum)
    Debug.Print IsNumeric(BlnTxt)

End Sub

Note: 

As you can see Boolean data type become True because when False is refer to 0 and True is -1.

2) Below example for False case and mostly when data type are String, Date and Other than Number.

VBA Vode:

Option Explicit
Sub Examples_IsNumeric_False()
    
    Dim StrTxt As String
    Dim DteTxt As Date
    
    StrTxt = "Abc879"
    DteTxt = "21/10/2022"
    
    Debug.Print IsNumeric(StrTxt)
    Debug.Print IsNumeric(DteTxt)
    
End Sub

Note: 

This function is very useful to avoid calculation error because when we try to calculate other than number then system will generate error.

Microsoft Reference-Isnumeric-function
Other Reference-Isnumeric-function

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

VBA Function IsNull

IsNull Function is to check whether an expression contains no valid data (Null). The return value in Boolean either True or False. If we assigned specific variables with Null then the result is True else False. 

Syntax : IsNull(expression) , Return : True/False

Refer below example:

VBA Vode:

Option Explicit
Sub Examples_IsNull()

    Dim StrVarA, StrVarB, StrVarC
    
    StrVarB = ""
    StrVarC = Null
    
    Debug.Print IsNull(StrVarA)    ' Returns False.
    Debug.Print IsNull(StrVarB)    ' Returns False.
    Debug.Print IsNull(StrVarC)    ' Returns True.

End Sub

Note: StrVarA we don't assigned anything, StrVarB as empty and StrVarC is Null.

Microsoft Reference-Isnull-function
Other Reference-Isnull-function

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

VBA Function IsMissing

IsMissing function is to check whether Option Variant has been past through from procedure to called procedure or not. The return value is Boolean either True or False. If really missing then True else False.

Syntax : IsMissing(Variables) , Return True/False

1) Below example Main procedure Called Sub procedure but did not assigned StrVar.

VBA Vode:

Option Explicit
Sub Examples_IsMissingCalling_True()

    Examples_IsMissingCalled 'Cause IsMissing is True
    
End Sub
Public Sub Examples_IsMissingCalled(Optional ByVal StrVar As Variant)

    Debug.Print IsMissing(StrVar) 'Result is True
    
End Sub

Note: The above case StrVar is Missing then result is True.

2) Below example Main procedure Called Sub procedure but assigned StrVar = StrVarA.

VBA Vode:

Option Explicit
Sub Examples_IsMissingCalling_False()
    
    Dim StrVarA As Variant
    Examples_IsMissingCalled StrVar:=StrVarA
    
End Sub
Public Sub Examples_IsMissingCalled(Optional ByVal StrVar As Variant)

    Debug.Print IsMissing(StrVar) 'Result is False
    
End Sub

Note: The above case StrVar is Not Missing then result is False.

Microsoft Reference-Ismissing-function
Other Reference-Ismissing-function

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

VBA Function IsError

IsError function is to check any expression contain error value or not and return in Boolean either True or False. 

Syntax : IsError(expression) , Return : True/False

Below example Range D2~D6 contain formula which create error because of formula.When we test this range with IsError function cells contain #NULL error show TRUE result and others FALSE.

VBA Vode:

Option Explicit
Sub Examples_IsError()
    
    Dim i As Integer
    i = 2
    With ActiveSheet
        Do
            .Range("E" & i) = IsError(Range("D" & i))
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

The error generate in Excel included #DIV/0,#N/A,#NAME?,#NULL!,#NUM!,#REF!,#VALUE! and #####. May be circular error also included.

Microsoft Reference-Iserror-function
Other Reference-Iserror-function.htm
Other Reference-Iserror.php

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code