Visual Basic for Application

Data type - Type conversion functions

2023-02-25T07:19:00.003-08:00

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

2023-01-16T06:48:00.000-08:00

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

2023-01-07T06:30:00.006-08:00

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

2022-12-21T06:59:00.000-08:00

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

2022-12-21T06:37:00.004-08:00

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

2022-12-20T07:15:00.000-08:00

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

2022-12-09T06:42:00.001-08:00

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

2022-12-09T06:28:00.001-08:00

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

2022-12-03T06:59:00.003-08:00

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:

There are another Sub which Friend Sub (only class module) and Static Sub (preserved variables) but seldom use for beginner.
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

2022-11-29T07:10:00.006-08:00

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

2022-11-24T07:02:00.011-08:00

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

2022-11-24T05:29:00.003-08:00

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:

Dim VarName
This to declare VarName as default which is Variant.
Dim VarName as String
This to declare VarName as String.
Dim VarName1 as string, VarName2 as string
This to declare both VarName1 and 2 as String.
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:

Don't use same Variable name with number because more likely become array. The above example just for reference.
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

2022-11-22T05:49:00.000-08:00

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

2022-11-21T07:05:00.010-08:00

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.

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

2022-11-13T20:44:00.004-08:00

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

2022-11-13T06:10:00.002-08:00

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

2022-11-08T07:08:00.001-08:00

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

2022-11-08T06:58:00.006-08:00

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

2022-11-04T03:46:00.005-07:00

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

2022-11-03T07:49:00.002-07:00

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

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

2022-11-02T07:28:00.000-07:00

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

2022-11-01T08:06:00.001-07:00

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

2022-11-01T07:48:00.001-07:00

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

2022-11-01T07:01:00.006-07:00

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

2022-10-31T08:00:00.001-07:00

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