How to Launch VBA Editor in Excel
Steps:
- Press Alt + F11 to open your Microsoft Visual Basic.
- Press Insert > Module to open a blank module.
Solution 1 – Initialize Object Before Use
To understand the case better, consider that we want to open and activate a workbook using VBA code. As the attached video suggests, we have encountered an Object Variable or Block Variable Not Set error.
Sub Initialize_Object_Before_Use()
Dim wb As Workbook
wb = Workbooks.Open _
("C:\Users\User\Documents\Softeko\Update 95\Book1.xlsm")
wb.Activate
End SubReason:
We encountered this error because we did not set our Workbook type object variable wb here. We need to modify the code.
Solution:
We will use a keyword Set to solve the problem.
Sub Initialize_Object_Before_Use()
Dim wb As Workbook
Set wb = Workbooks.Open _
("C:\Users\User\Documents\Softeko\Update 95\Book1.xlsm")
wb.Activate
End SubCode Breakdown
- A variable wb is declared as a Workbook object.
- The Set keyword assigns the wb variable to the result of the Workbooks.Open method.
- The Workbooks.Open method opens the “Book1.xlsm” workbook file.
- The Activate method is called on the wb workbook object to make it the active workbook.
Read More: [Fixed!] Subscript Out of Range Error in Excel VBA
Solution 2 – With Block Variable Not Set
Reason:
We have encountered the Object variable or the With block variable not set error as we did not set ws variable before executing Set rng = ws.Range(“B4:C10”) line.
Sub Initialize_Object()
Dim ws As Worksheet
Dim rng As Range
Set rng = ws.Range("B4:C10")
With rng
.Value = "ExcelDemy"
.Font.Bold = True
End With
End SubSolution:
You need to set a ws variable to fix your code. Follow the image and code given below.
Sub Initialize_Object()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("B4:C10"
With rng
.Value = "ExcelDemy"
.Font.Bold = True
End With
End SubCode Breakdown:
- We declared two variables: ws as a Worksheet object and rng as a Range object.
- We used the Set keyword to assign the ws variable to the worksheet named Sheet1 in the current workbook.
- Then, we used the Set keyword again to assign the rng variable to the range B4:C10 on the ws worksheet.
- We used the With statement to apply multiple changes to the rng range object.
- Next, we set the .The value property of the rng object to ExcelDemy populates all cells in the range with the same value.
- The .Font.Bold property of the rng object is set to True, which makes the font bold for all cells in the range.
You will get the output, as given below.
Read More: Reasons And Solutions for Excel Object Required Error in VBA
Download the Practice Workbook
You can download and practice the dataset.
Related Articles:
- How to Fix Compile Error in Hidden Module in Excel
- [Fixed!] Invalid Forward Reference in VBA
- [Fixed!] Unable to Set the Visible Property of the Worksheet Class
- Because of Your Security Settings Macros Have Been Disabled
- [Fixed!] Method Open of Object Workbooks Failed
- [Solved]: User Defined Type Not Defined in Excel VBA
- Sub or Function Not Defined in Excel VBA
- [Solved!] Excel VBA “Argument Not Optional” Error
