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
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.
No comments:
Post a Comment