How to use Immediate Window in Excel VBA

 In this blog post, you will learn about the use of “Immediate Window” in Excel VBA.

An immediate window is an excellent tool for VBA users. It can alone execute many queries and run macros without creating it and get the result immediately.

This tool can execute many queries and task, such as:-

Summing a value, creating and renaming sheets, change cell color, changing or renaming a cell value, and many more.

To find Immediate Window, Go to the Developer tab and then go to the “View” tab and click on “Immediate window” or press Ctrl + G as a keyboard shortcut.

Executing Simple Mathematics Quires

In Immediate Window, we can write simple questions such as see below:-

Type these questions in the Immediate Window by initiating “?” (Question mark) and type your query as we did in the below image.

? 5 + 3 and hit enter, it will show the answer like 8.

Image - 01

Count of Total Worksheets in a Workbook 

We can execute a query about the total worksheet count in a workbook.

See in the image below:-

Image -02

We can run the query as follows:-

? Worksheets.Count

An Immediate Window will respond immediately with the answer. See the above image for the result.

Changing or Renaming Cell Value by Immediate Window

Here in the below list, we have weekdays name, see below:-

Image-03

If we want to make changes in the above list by changing any cell value or inserting a value in a blank cell, just put the VBA code in the Immediate Window for quick results.

We can change these cell values with Immediate Widow at no time without creating a macro for this.

See in the below image:-

Image-04

• First code, we change the existing cell value from “Wednesday” to Excelnsg.

• The second code put the value in a blank cell, Cell A10 with Month.

• Third code, we ask a question about the cell value as

? Range (“A7”).Value

And, got the answer as Saturday.

Similarly, we can remove or clear the cell value from the given list.

So, here we have applied multiple tasks to execute the VBA code in the immediate window.

Not only these tasks, but we can also run multiple VBA codes to test with the help of an Immediate window.

Thanks

Narendra

Related Post

 

How to Change the Background Color in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

What is Current Region Property in VBA

How to Change or Rename the Sheet Name in Excel VBA

How to Change Sheet Tab Color in Excel VBA

 

How to Create a New Workbook in VBA Macro

In this blog post, you will learn to create a new workbook in VBA.

In Excel, simply go to “File” and click “New” and then select the blank file from the list.

Here in VBA, we have two ways to create a new workbook or file by VBA macro.

Create Workbook without File Name

Whenever we create a new workbook in Excel, it opened with the name “Book.xlsx” by default. Then we can save the file with the desired name.

Below the macro will create a new workbook in Excel with the name “Book1.xlsx”.

If “Book1.xlsx” is already opened or existing there then macro will create a workbook name with “Book2.xlsx” by default.

For creating a macro for a new workbook, just follow the below procedure.

Go to the Developer tab in Excel and click on Visual Basic and then go to Insert and open a new module.

Just type the below code in the module and then press “F8” multiple times until End Sub for step by step execution.

Sub New_workbook() Workbooks.Add End Sub

After running the above code in VBA, see the result as the below image.

\

Image-01

We cannot locate this file until we save it with a file name in our folder or storage drive.

Create Workbook with File Name

Now we will learn how to create a new workbook with the desired name by VBA macro.

Just copy the below VBA macro in the new module, run the code by pressing “F8” again and again for step by step execution until End Sub, or just press “F5” from the keyboard to run in a single time.

Sub New_Workbook() Workbooks.Add.SaveAs Filename:="E:\Excelnsg.xlsx" End Sub

After executing the above code, see the below image for the result.

File “Excelnsg” is created now in “E” storage drive.

Image - 02

So here we learn about to create a new workbook by VBA macros.

I hope you find this blog post useful.

Please feel free to put your comment or suggestion in the comment box below.

Thanks

Narendra

Related Post

Easiest Way to Copy and Paste Data in Excel VBA

How to Move Sheets in Excel VBA

What is Current Region Property in VBA

How to Change or Rename the Sheet Name in Excel VBA

How to Change Font Color in Excel VBA

How to Change Sheet Tab Color in Excel VBA

How to Change the Background Color in Excel VBA

How to Protect or UnProtect Excel Sheet in Excel VBA

 

In this blog post, you will learn about sheet protection in Excel VBA.

What is protecting and unprotecting Excel worksheets?

Protecting a worksheet is putting a password in a worksheet so that nobody can edit or modify the data.

Only an authorized person can unprotect the worksheets by putting the password to edit or modify.

When we are trying to put or enter the data into a protected worksheet, then Excel shows the below error pop-up with a message.

See in the below image:-

Image-01

In Excel, we do sheet protection by right-clicking on the ”Sheet name” on the sheet tab bar at the bottom, which we want to protect.

And select the sheet protection option and put the password to protect the worksheet.

Here, you will learn about how to apply for worksheet protection in excel VBA.

We will apply the protect or unprotect sheet on sheet “Main” from the mentioned sheets on the bottom sheet tab bar.

Select sheet “Main” and go to the Developer tab and click on Visual Basic” tab and then “Insert” a “Module”.

Write the below code to protect and unprotect the worksheet

In this macro, we are using the password as "abc".

Sub Sheet_Protect()

Sheets("Main").Protect Password = "abc"

Sheets("Main").Unprotect Password = "abc"

End Sub

Now execute the code, step by step by pressing F8 until End Sub.

Image-02

When we execute our first line of code by pressing “F8”, the sheet is protected. 

Now try to enter anything in the sheet “Main”, you will get the error message. 

See image-01 for an error message.

Again by pressing “F8” from the keyboard, execute the second line of code

Image-03

When we execute the second line of code, our sheet protection has been removed and our sheet is not protected now.

Now we can enter the data easily in the worksheet without showing any error message.

I hope you find this tutorial useful.

Please feel free to put your comments and suggestion in the below comment box.

Thank you.

Narendra

Related Post

How to Change Sheet Tab Color in Excel VBA

How to Change Font Color in Excel VBA

How to Change or Rename the Sheet Name in Excel VBA

What is Current Region Property in VBA

How to Move Sheets in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Hide and Unhide Sheets in Excel VBA

 

In this blog post, you will learn about how to hide and unhide sheets in Excel VBA.

In Excel, we can hide and unhide sheets by right-clicking on sheet tabs and select the option for hiding and unhide sheets.

But in this post, you will learn about how to hide and unhide sheets in Excel VBA.

See in the below image, there are some sheets:-

Now with the help of VBA, we will hide and unhide one sheet from the above sheets.

So for that go to the Developer tab and click on the Visual basic tab.

Go to the insert tab and click on the module.

Write down the below VBA macro on the module and press “F8” again and again until “End Sub” to execute code step by step.

Sub Hide_Unhide_Sheets() Sheets("Main").Visible = False Sheets("Main").Visible = True End Sub

In this VBA macro:-

False = Hide Sheets

True= Unhide Sheets

Here we will use sheet “Main” for hiding and unhide with the help of VBA macro. 

Now we will run the VBA code and press “F8” to execute the code step by step. 

Image -01

See that when we execute our first line of VBA code, our sheet “Main” is hiding now.

Image - 02

Now press “F8” again to execute the second line of macro code and see the result in the below image.

Our hidden sheet is appearing again in the sheet bar at the bottom.

Image -03

I hope you find this tutorial useful.

Please feel free to put your comments and suggestion in the comment box below.

Regards

Narendra

Related Posts

How to Change Sheet Tab Color in Excel VBA

How to Change Font Color in Excel VBA

How to Change or Rename the Sheet Name in Excel VBA

How to Move Sheets in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Sheet Tab Color in Excel VBA

In this blog post, you will learn how to change the Sheet tab color in Excel VBA.

Suppose that we have some sheet tabs in our Excel sheet and need to change our sheet tab color.

For example, see the image below:-

There are 4 sheets in the above image.

Now with the help of VBA macros, we will change the color of these sheets.

We can change the color of multiple sheets here but here we will change the color sheet “Main”.

Here we can change the sheet tab color by two methods.

Below are both methods.

Method 1:- Changing Sheet Tab Color by VBA Standard Color Name

Method 2:- Changing Sheet Tab Color by VBA Color Index Number

Changing Sheet Tab Color by VBA Standard Color Name

In this method, we will change the sheet tab color by VBA Standard Color Name.

There are 8 standard colors in Excel VBA and these colors are as follows:-

vbBlack

vbWhite

vbBlue

vbRed

vbGreen

vbCyan

vbMagenta

vbYellow

Here we will use some of these color code names to change our “Sheet Tab Color”.

Write these codes in a new module and execute by pressing “F5” a single time or by pressing “F8” for step by step processing until the “End Sub”.

Sub Sheet_Tab_Color_By_Color_Name() Sheets("New Data").Tab.Color = vbRed Sheets("New Data").Tab.Color = vbBlue Sheets("New Data").Tab.Color = vbGreen Sheets("New Data").Tab.Color = vbYellow End Sub

See the result in the below image:-

Changing Sheet Tab Color by VBA Color Index Number

This is the second method to change the “Sheet Tab Color”.

Here, we will use the color index number to change the sheet tab color.

In this method, we can use the number from 1 to 56.

Below is the macro coding for changing the sheet tab color by using the color index number.

 

Sub Sheet_Tab_Color_By_Index_number() Sheets("New Data").Tab.ColorIndex = 1 Sheets("New Data").Tab.ColorIndex = 2 Sheets("New Data").Tab.ColorIndex = 5 Sheets("New Data").Tab.ColorIndex = 7 Sheets("New Data").Tab.ColorIndex = 9 End Sub

With the help of the above VBA coding, we can see the result in below image.

In this method, we can use the more color index number to change the sheet tab color.

I hope you find this tutorial useful.

Please feel free to put your comments and suggestion in the below comment box.

Regards

Narendra 

Related Post

How to Change or Rename the Sheet Name in Excel VBA

What is Current Region Property in VBA

How to Move Sheets in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Font Color in Excel VBA

How to Move Sheets in Excel VBA

In this blog post, you will learn about how to move sheets “before” and “after” any particular sheet in Excel by VBA.

Here in the below image, you can see that there are multiple sheets inserted. Here we will move any sheet before and after to other sheets.

Now we will use these sheets to move from one place to another.

Move Sheet After Another

First of all, we will move the sheet My Data to after sheet Main.

Use the code below:-

“Sub Move_Sheet_Name() Sheets("My Data").Move after:=Sheets("Main") End Sub”

Write the above VBA code in a new module and execute the code by pressing “F8” again and again until End Sub.

See the below image, where we have successfully moved the sheet “My Data” from its place to after sheet Main.

Move Sheet Before Another

Now with the help of VBA macro, we will move sheet “New Data” before sheet Main.

Use the below VBA code.

“Sub Move_Sheet_Before() Sheets("New Data").Move before:=Sheets("Main") End Sub”

See the result in the below image:-

So in this blog post, you learned about how to move a sheet before or after any particular sheet.

I hope you find this blog post useful.

Please feel free to put your comments and suggestion in the below comment box.

Thanks

Narendra

Related Post

How to Insert a New Sheet In Excel VBA

What is Current Region Property in VBA

How to use Orientation in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Font Color in Excel VBA

How to Change or Rename the Sheet Name in Excel VBA