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 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

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

VBA Function IsEmpty

IsEmpty function is to check whether a variable has been initialized or assigned any value or not. The result is Boolean either True or False. Basically the variable is variant for example Dim StrVar as Variant, then we assigned StrVar = Null or anything then this variable no longer empty (False). 

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

1) Example IsEmpty for True.

VBA Vode:

Option Explicit
Sub Examples_IsEmptyTrue()
    
    Dim StrVar
    Dim MyTest As Boolean
    
    MyTest = IsEmpty(StrVar)
    Debug.Print MyTest 'Result is True

End Sub

Note: The variables (StrVar) is not yet assigned or initialized.

2) Example IsEmpty for False.

VBA Vode:

Option Explicit
Sub Examples_IsEmptyFalse()
    
    Dim StrVar As Variant
    Dim MyTest As Boolean
    
    StrVar = Null
    MyTest = IsEmpty(StrVar)
    Debug.Print MyTest 'Result is False
    
End Sub

Note: The variables (StrVar) already assigned or initialized as Null.

Microsoft Reference-Isempty-function
Other Reference-Variant-empty.htm

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

VBA Function Isdate

Isdate function is to check whether variables is valid date or not and the return is Boolean either True or False.

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

1) Below example for True in Result.

VBA Vode:

Option Explicit
Sub Examples_IsDateTrue()
    Dim MyDate() As Date
    
    ReDim Preserve MyDate(1): MyDate(1) = "01/07/2022"
    ReDim Preserve MyDate(2): MyDate(2) = "01/99" '= 1000000 True?
    ReDim Preserve MyDate(3): MyDate(3) = "01/13/2022"
    ReDim Preserve MyDate(4): MyDate(4) = "13/01/2022"
    ReDim Preserve MyDate(5): MyDate(5) = "12-01-2022"
    ReDim Preserve MyDate(6): MyDate(6) = CDate(42565) '14/7/2016
    
    Debug.Print IsDate(MyDate(1))   '= True
    Debug.Print IsDate(MyDate(2))   '= True
    Debug.Print IsDate(MyDate(3))   '= True
    Debug.Print IsDate(MyDate(4))   '= True
    Debug.Print IsDate(MyDate(5))   '= True
    Debug.Print IsDate(MyDate(6))   '= True
    
End Sub

Note: With variables original declare as date may different result compare with direct test. For example direct test IsDate(1) False but declare as date MyDate(1) = 1, IsDate(MyDate(1)) become True. Even with number 1 to 1,000,000 still true but more than that become error "Run-time error '6': Overflow".

2) Below example for False in Result.

VBA Vode:

Option Explicit
Sub Examples_IsDateFalse()

    Debug.Print IsDate(42565)          '= False
    Debug.Print IsDate("8")            '= False
    Debug.Print IsDate(15)             '= False
    Debug.Print IsDate("24")           '= False
    Debug.Print IsDate("String")       '= False

End Sub

Note: Without Dim declaration the result will be different.

Microsoft Reference-Isdate-function
Other Reference-Isdate-function.htm

Practice makes perfect. Thank You.

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

VBA Function Isarray

Isarray function is to check whether a variable is an array form or not and the return is Boolean which is True or False.

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

1) Below example is declare as variant but we assigned as array. The return is true.

VBA Vode:

Option Explicit
Sub ExamplesIsArray()

    Dim StrVar As Variant
    Dim i As Integer

    StrVar = Array(1, 2, 3)
    If IsArray(StrVar) Then 'The answer is True
        For i = LBound(StrVar) To UBound(StrVar)
            Debug.Print StrVar(i) ' The answer 1,2,3
        Next
    Else
        Debug.Print "This variables is not an array"
    End If

End Sub

Note: When the return is true we can loop thru this array from lower to upper bound.

2) Below example originally declare as array even we don't assigned any array the result still True.

VBA Vode:

Option Explicit
Sub Examples_IsArray()
    
    Dim MyArray() As String
    If IsArray(MyArray) Then
        Debug.Print True 'The answer is True
    Else
        Debug.Print False
    End If
    
End Sub

Note: For the above example we have to use redim statement to assigned array.

Microsoft Reference-Isarray-function
Other Reference-Isarray-function.htm

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Get started with all Function and Formula in Excel

To be a good programmer in Microsoft Excel we should understand every function in excel itself. To open all these function:

1. Click Home tab.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Under Home tab Click at Arrow down button beside sum symbol.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Click at More Functions.
   
   
4. Select a category.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Select a function and this function or formula will be inserted into selected cell.
   

All this functions already build in and can be use instantly or call using VBA code. To create new function we must create using VBA code inside Excel workbook and this will appeared under this insert function dialog box.

The most importance function is under Math & Trig which is related to number  and Text which is related to string.