Saturday, February 25, 2023

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.

Monday, January 16, 2023

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.

Saturday, January 7, 2023

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.

Wednesday, December 21, 2022

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.