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

User defined data type can be any data type using a Type statement which is more related to groups of variables. Declaration must be done at module level above the sub procedure, otherwise compile error: Invalid inside procedure as below.

Syntax : 

Type MyType
    Field1 as Any Data type
    Field2 as Any Data type
    .
    .
End Type

The above syntax must be declare at module level and below statement is inside sub procedure.

Dim MyVar as MyType

From here we can access Field1,Field2,... and etc.

Refer below for example:

VBA Vode:

Option Explicit
Type MyDetails
    Myname As String
    MyAge As Integer
    MyBirthDate As Date
    MyMarried As Boolean
End Type
Sub DataTypeExample_UserDefined()

    Dim MyBio As MyDetails
    
    MyBio.Myname = "Nazri"
    MyBio.MyAge = 50
    MyBio.MyBirthDate = "05/06/1978"
    MyBio.MyMarried = True
    
    MsgBox ("My name is " & MyBio.Myname & " my age is " & _
            MyBio.MyAge & " and married is " & MyBio.MyMarried)
    
End Sub

Note:
This User defined data type is more to static, we can't changed this inside our code only at design stage.

Read more about User defined data type, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-User-defined-data-type
Other Reference-User-defined-types.htm

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.

Data type - LongPtr

LongPtr data type is a variable declared type depend on platform or system used.
For 32-bit systems and the numbers ranging in value from -2,147,483,648 to 2,147,483,647
translate as Long.
For 64-bit systems and the numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807  translate as LongLong.

Declaration : Dim Var as LongPtr

Where Var refer as variable.

Note:
This data type is very seldom used and this data type should be used for pointers and handles..

Read more about Longptr data type, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Longptr-data-type
Other Reference-Longptr-data-type

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

Data type - LongLong

Longlong data type is a variable declared type only valid on 64-bit platforms and the numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Declaration : Dim Var as LongLong or
                       Dim Var^

Where Var refer as variable instead of LongLong we can use caret (^) to represent this data type.

Note:
This data type is very seldom used unless we know each user what platform they use, either 32bit or 64bit system. Long data type is more flexible.

Read more about Longlong data type, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Longlong-data-type
Other Reference-Longlong-data-type

Leave your comments if you have any requests.
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 - Public

Public statement is to declare public variables and allocate storage space at module level.Instead of using Dim Statement we can use Public Statement. The different is Dim Statement declare inside the Sub procedure and Public Statement on top of module follow by Sub procedures. The variables declare by using Public statement can be use by all Sub procedures in all modules.

Syntax : Public Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Public and Variable name, the rest is same as Dim Statement and Private Statement.

Note:
The only different between Private and Public statement is Public statement are available to all procedures in all modules but Private statement is limited to procedures inside specific module where variables is declare.

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

Microsoft Reference-Public-statement
Other Reference-Lifetime-scope-global-level

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

VBA Statement - Private

Private statement is to declare private variables and allocate storage space in module level. Instead of using Dim Statement we can use Private Statement. The different is Dim Statement declare inside the Sub procedure and Private Statement on top of module follow by Sub procedures. The variables declare using Private statement can be use by all Sub procedures inside the module where this variables are declares. This variables can't use outside the declare module.

Syntax : Private Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Private and Variable name, the rest is same as Dim Statement.

VBA Vode:

Option Explicit
Private MyVarA As String, MyVarB As String
Sub Macro1()
    MyVarA = "My Country"
    myVarB = "Malaysia"
    Debug.Print MyVarA, myVarB
End Sub
Sub Macro2()
    Debug.Print MyVarA, myVarB
End Sub

Note:
For the above example we have 2 variables MyVarA and MyVarB, we have 2 Sub procedures which is Macro1 and Macro2. If we run Macro1 then follow by Macro2 by using the same variables then we get the same answer unless we reset these variables each time before exit Sub procedures.

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

Microsoft Reference-Private-statement
Other Reference-Lifetime-scope-module-level

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

VBA Statement - Sub, Private Sub, Public Sub

Sub statement is subroutine also known as Procedures or Macros (When we record macro the default name is Sub Macro1() follow by series of code and close with End Sub. Macro1 is the default name of procedure recorded. Same case when we create on our own.we start with Sub Procedure Name() and Close with End Sub.

Syntax :
Sub Procedure Name (ArgList)
    Statements (Code)
    Exit Sub
    Statements (Code)
End Sub

Actually the syntax consist many optional parts but required only 3 which is Sub, Procedure name and End Sub to explain in detail refer below:

Case Study 1: 

Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This is the most typical of Sub Statement, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 2: 

Sub Procedure Name (ByVal and ByRef Variables)
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement in the project because we need to specify variables inside open and close bracket and not listed in macros.

Case Study 3: 

Public Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement same as typical Sub Statement above, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 4: 

Private Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement within the same module and not listed in macros.

Note:

1. There are another Sub which Friend Sub (only class module) and Static Sub (preserved variables) but seldom use for beginner.
2. The procedure name must be related with the macro task to ease accessible process and there are rules to follow ex. no space, certain characters and etc.

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

Microsoft Reference-Sub-statement
Other Reference-Subroutines

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