VBA

If you want to super hide the worksheets in excel, excel has an very hidden property.

When the sheets are very hidden, if you right click and try to unhide such sheets, the unhide view gets disabled. As there will be no worksheets to view.

Use the below steps to test the very hidden property of the excel sheets.

Open new worksheet

Press Alt + F11 to go to VBA editor window

Insert new module from the project explorer

Copy and paste the below code to try out:

Sub SuperHide()

‘This property will super hide the sheets
Sheet1.Visible = xlVeryHidden

End Sub

We will not be able to unhide this sheet1 from excel window. For that we would need to write a vba code for unhide.

Sub UnHide()

‘This property will unhide the sheets. True over here reprents the xlSheetVisible property
Sheet1.Visible = True

End Sub

This will unhide the sheet and you will be able to see the same in excel window.

You can add multiple sheets in a similar way and use both the hide and unhide codes for different uses. If you don’t want to super hide the sheet, you can also use the below code to just hide the sheet.

Sub Hide()

‘This property will simply hide the sheets. False over here represents the xlSheetHidden property
Sheet1.Visible = False

End Sub

This will hide the sheets and in this case, you will be able to unhide the sheet from the Excel Window.

You can also use the below keyboard shortcuts to hide or unhide sheets for faster use.
Hide – Alt, O, H, H

Unhide – Alt, O, H, U

Just in case if you don’t want any one to go in VBA code and unhide the super hidden sheets. You can protect the VBA module with password of your choice.

Check the post How to protect vba module in excel ? for the same.

If you are building a macro and don’t want any one to modify it. You have an option in Excel VBA editor, using which you can protect your VBA module code.

Open a new excel file

Go to Visual Basic Editor Window.

Insert new module and add a sample code to it.

Sub SampleCode()

Msgbox “This is a sample code we need to hide !”, vbInformation

End Sub

Follow the below steps to protect the VBA module:

Click on Tools

Click on VBA Project Properties

The VBA object properties window will pop up. On this window, select the Protection Tab.

Tick the Lock Project for viewing check box.

Enter the password of your choice in two fields, Password and Confirm Password.

Click OK and we have just protected the VBA module. Save and close the workbook. Open it again to check the protection.

Go to VBA module, all modules will be hidden and if you double click on it will ask you the password to unprotect.

Thank you for reading. Happy Learning !

VBA stands for Visual Basic for Applications. It is a programming language provided by Microsoft, to create applications or develop macros. VBA is a free tool, which comes along with the Microsoft office package, which we can use to develop programs in Excel, Word or PowerPoint platform.

VBA is widely used with Excel to create macros to automate the repetitive tasks. Tasks which consumes lot of manual efforts, which can be automated using the VBA macro. We can write a set of instructions in VBA code to execute the repetitive tasks, fast and more accurately without any manual intervention. Few people also call these macros as Robots. which do their tasks without resting and they can rest for the time the robot is doing their job :-).

Please note that VBA and VB are different from each other. By using VB (Visual Basic), you can create applications which are standalone and executable. While VBA needs support of Microsoft office applications to develop and run macros.

Excel from Microsoft office is widely used across globe, from day to day tasks to complex data processing. To execute these tasks using the macro (vba code), we can execute many manual tasks such as:

Formatting the data in specific formats for all sheets in workbook
-formatting your monthly reports for specific format and arrangement of the data for presentation purpose. Creating charts and formatting colors as per the report requirements. This can be done by writing set of instructions and executing them with click of a button.

Automating the repetitive task
-If you need to collate data from 20 different excel files on daily basis and put it in a template file. Then format this data as per required output. This task can be automated by the macro. For the first time you can record the macro while collating the data, The next time you would just need to execute the macro with one click and all the manual work will be completed by the automated instructions.

Inserting frequently used text
-Say while preparing reports, you need to insert few texts in a standard format for all reports you create, Here we can write macro and assign a shortcut button for it, by pressing which the macro will insert the required text for you. e.g. The product description headers. This will reduce your typing or copy paste time and eliminate the error of manual mistake.

Creating complete vba application
-To create a complete vba application would definitely take some time to understand the coding and input requirements. e.g. you can develop a complete conference room manager application using Excel VBA code, which can be used by the users to book an conference room.

Creating custom functions in VBA
– Apart from the standard functions (sum, count and more) available in Excel, we can create custom functions as per our requirements, very easily using the VBA code. e.g. say we are creating a home loan emi calculator, we can create a functions which will give us the output, based on few inputs for calculations.

There is a large scope of developing macros, automating the complex processes, generating reports and much more. It all depends on how creative you think to solve the problems. That’s all for now, about VBA.

Thank you for reading. Happy Learning !

The Visual Basic Editor is the starting point for creating macros in Excel VBA, so it is important to get this configuration right. To launch the Visual Basic Editor in Excel 2010 or Excel 2007, click on Visual Basic (or press Alt+F11).

Below Visual Basic Editor window will open

The left window with the sheet names in it is called the Project Explorer. If you can’t see the Project Explorer, click on View and then Project Explorer. Most probably the Project Explorer will already appear as a column on the left side of the screen. If not, follow the below steps to achieve this:

1. Right click on the Project Explorer
2. Check Dockable (If required)
3. Click on Project – VBAProject and drag the Project Explorer to the left side of the screen

The Code window can be added by clicking on one of the sheet names. To cover the whole screen, you can maximize the Code Window. We feel that this is the best configuration of the Visual Basic Editor.

On the left side of the window under project explorer, you will find 3 folders:
1. Microsoft Excel Objects: All Excel Sheets and workbooks will be available under this folder
2. Forms: Any user forms created in the excl VBA will be available under this folder
3. Module: All created macros will be available under this modules folder

You can add the object or reference libraries with the help of adding VBA references.
1. Go to Tools
2. Click on References
3. Tick the library or references you need to add to execute your macros

4. Click OK, done

Thank you for reading. Happy Learning !

There may be a requirement to view data on excel VBA form, in case you are building an Excel VBA application.

This data might be used to view a list of points to cross audit or adding a check if your data is updated correctly and many more audit options.

In order to create a sample form, we need prepare a data input. You can copy paste the below table to your excel sheet and follow the steps, below.

1. Open excel workbook and copy the below table to sheet1

User Name	Communication	Leave Management	Training Score	Productivity	Quality
ABC	10	3	94	56	98
DEF	10	4	42	49	91
GHI	8	4	100	60	91
JKL	3	9	40	60	90
MNO	1	3	52	62	95
PQR	8	5	74	45	85
STU	10	6	74	65	96
VWX	10	3	51	94	95
YZA	9	8	72	61	93

2. As per above table our data is in Sheet1, within range from A1 to F10.

3. Press Alt + F11 and open the VBA editor window

4. Right click on Microsoft Excel Objects and Click on Insert –> Module. For more details check out my post on How to use Visual Basic Editor in Excel VBA ?

5. Right click on Microsoft Excel Objects and Click on Insert –> UserForm

6. A new UserForm will be inserted, adjust the length of the UserForm as required to view the 6 columns.

7. Now add a list box from the VBA Editor Tool Box window. (If you are not able to find the tool box on screen, then Go to View menu on the top–>Click on Toolbox). Adjust the size of the list box to fit the 6 columns of the data table.

8. Add a Command Button on the form we have created. The code to view the data in list box, will go into this command button.

9. Double click on CommandButton1 and this will take us to the vba editor screen of the form. We will be able to see the below code:

Private Sub CommandButton1_Click()

End Sub

10. Copy paste the below code in the CommandButton1 click event.

————————————————————————————————————————–

Private Sub CommandButton1_Click()

With ListBox1

.ColumnCount = 6

.ColumnWidths = “50;70;90;70;70;50”

.RowSource = Sheet1.Range(“A1:F20”).Address

End With

End Sub

————————————————————————————————————————–

11. How this works ?

————————————————————————————————————————–

Private Sub CommandButton1_Click()

‘ below code will work on the Listbox1, which we have added on the form

With ListBox1

‘ We can change the number of the columns we need to view in the list box. In this case we need to view 6 columns.

.ColumnCount = 6

‘Below code defines what will be the column width. We can adjust this column width based on the data requirement.

.ColumnWidths = “50;70;90;70;70;50”

‘In below line we are providing our table data range, which needs to be populated in the Listbox1

.RowSource = Sheet1.Range(“A1:F20”).Address

End With

End Sub

————————————————————————————————————————–

12. Once the code is added, Click on Run button on top menu or press F5 on keyboard.

13. We will be able to see the below form. Here click on command button.

14. Once we click on command button, we will be able to see the below result.

15. The list box here, is used to populate the table data in tabular format. We provided the # of columns, column width and the data range source to the list box. The list box provide an horizontal and vertical scrollbars, based on the data range.

Please feel free to customize this code as per the requirements.

Thank you for reading. Happy Learning !

To start with creating the Excel VBA macro, we will create a simple display message macro.

Create Macro:
Using Excel VBA we can automate tasks in Excel by writing macros. A set of VBA code instructions to automate the repeated tasks or complex, time consuming tasks.

1. Press Alt + F11 to view the Visual Basic Editor
2. Select VBAProject(“your excel file name”)
3. Right click on on the name
4. Click Insert — Module

Module – the codes written will be saved in the modules. you can create multiple modules, under same file to manage your codes.

On the blank editor window write below code:

Sub DisplayMessage()

MsgBox “My First VBA Macro!”

End Sub

In above code the DisplayMessage is our macro name. After executing this macro, it will give us a pop-up message with “My First VBA Macro!”.

How to run this macro from Excel :

1. You can assign the code to the command button or any excel object.
2. Insert text box in excel, rename it as Display Message.
3. Right click on text box and click Assign Macro
4. Select this workbook and you will be able to see the macro name created by us
5. Select the macro name

Now if you hover the mouse over the text box, the mouse arrow will change to hand icon which means the text box is now clickable.

Now you can click on the text box and the macro will be executed. You will receive a pop-up message saying, “My First VBA Macro!”

Message box in excel VBA:

The MsgBox dialog box in Excel VBA can be used in many ways to provide the required information to the users. Wherever they need to know and take actions based on that.

1. Write a simple message in Excel VBA, MsgBox “First message box code”. When executed, it will display the below messagebox:

2. Combining the message box to display the value in the specific cell. If you want to display the updated or changed value in cell B4, we can use the code, MsgBox “The value in cell B4 is ” & Range(“B4”).Value

Here in this code, we have used the & operator to concatenate the text and the value is cell B4.

3. Writing the message on the multiple lines we just need to concatenate the text with vbNewLine. for e.g.,
MsgBox “This is my first Line” & vbNewLine & “This is my second Line”

You can use the Msgbox function to provide different messages during the execution of Macro. E.g. Alert Messages, Critical Warning Messages, Decision Making by allowing user to Clik Yes, No or whatever values you want to assign.

I will cover this in another blog on how to use message box for user input during the macro execution. Till then Happy Learning !