Hello Everyone
Hope you all are fine. It's my pleasure to be part of this wonderful world. I am here for you guys with my 1st Lecture of course Visual Basic Application (VBA). I chose this course because I have a strong command of VBA . I have 3 years of experience in VBA. . I really thank the **size** team for giving me a chance to show my abilities. I'm very keen and highly committed to my profession I promise that I will do my Best.
Lecture#1
i. Introduction
ii. Macro Recording
iii. Variables and Data type
iv. Comments
(i) Introduction:
Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports.
VBA stands for Visual Basic for Applications. It works with the Microsoft Office applications like Excel, Word, Powerpoint etc.
Here we will learn VBA with MS Excel. We can automate our daily work, which we do in excel. We can write our code in Visual Basic Editor. To open a Visual Basic Editor we need to enable the Developer tab first.
How to enable a developer tab in Excel?
Go to the File>>Options>>Customize Ribbon>>Check the Developer option.
Enable Developer Tab in Excel
Visual Basic Editor is available on the left side.
Click on Visual Basic or Press Alt+F11 short cut key to open a Visual Basic Editor.
Visual Basic Editor Window looks like the below image.
(ii) Macro Recording:
We can record a macro in excel easily. To automate the repetitive task in excel, macro recorder can be used. You can record a macro applying the format you want, and then replay the macro whenever needed.
Let’s say we have to automate some formatting tasks that can be applied to selected cells. Below are the steps to do that-
• Open a new workbook or you can use an existing workbook.
• Select some range in Excel.
• Click on the Record Macro button.
Record macro button is available in 3 places in a workbook.
- In the View Tab>>Macros>>Record Macro
- In footer of the workbooks.
- In the Developer Tab>>Record Macro
- Record Macro Option in Developer Tab
You can use any of the options to record a macro. Click on any record macro option.
A below-given window will be displayed. Put the macro name by default it will be Macro 1. You can give the shortcut Key to access this macro. Here I am putting Ctrl+m short key. Select This workbook in Store macro in the box and put some description if you want. It is not mandatory. Click on the OK button. Macro Recording will be started.
Record Macro Window
Give some formatting to your selected cells like border
, font name, font size, text alignment etc.
Format the selected cells
Now stop the macro recording from the same button, from where you start recording.
Now our macro has been recorded successfully. Test this select some range in excel sheet and go to Developer Tab>>Click on Macros
The macro list will be displayed in the Macro window. Here we have only one macro.
In the below image we will run this macro in range I4:K9. Click on the Run button.
Run a Macro
Same formatting will applied on selected range ( I4:K9)
Macro Result
Since we have given a shortcut key Ctrl+m while recording the macro, so we can select some range and press Ctrl+m to apply this formatting.
To see the code in Visual Basic editor press Alt+11.
Code in Visual Basic Editor
Now the time to save this workbook. We have to save this workbook as Macro enabled or Excel Binary or Excel 97-2003 format so that macro can be available in the workbook.
Save as Macro Enable workbook
(iii) Variables And Data Type:
Variables are used to store the information. We can re-assign a value on a variable.
See the below example.
Sub Use_Variable()
Dim my_age As Integer
my_age = 30
MsgBox my_age
End Sub
The keyword is used to declare a variable. An integer is a data type.
Declaring these variables is not absolutely necessary, but it is recommended. We should get in the habit of declaring variables correctly. Try to keep your variable name properly.
Option Explicit
We can make our variable declaration mandatory by using Option Explicit at the starting of the module.
To add automatically Option Explicit while adding a new module-Go to Visual Basic Editor Window >>Tools>>Options>>check the Require Variable Declaration
Options Window in Visual Basic Editor
Tick on Require Variable Declaration check box.
Now if you insert a new module, Option Explicit will be there automatically.
Option Explicit in module
Data Type
As in the above example, we have taken my_age as an Integer. Basically, Integer is a data type, which is used for numbers.
Below is the list of data types in VBA.
| Data Type or Subtype | Required Memory | Range |
|---|---|---|
| Integer | 2 bytes | –32,768 to 32,767 |
| Long Integer | 4 bytes | –2,147,483,648 to 2,147,486,647 |
| Single | 4 bytes | –3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38 |
| Double | 8 bytes | –1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324 |
| Currency | 8 bytes | –922,337,203,477.5808 to 922,337,203,685,477.5807 |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| String | String's length | 1 to 65,400 characters |
| Object | 4 bytes | Any Access object, ActiveX component or Class object |
| Boolean | 2 bytes | –1 or 0 |
| Variant | 16 bytes | –Same as Double |
| Byte | 1 byte | 0 to 255 |
(iv) Comments:
Comments are used basically notes which are used to increase the readability of the code. Comments do not execute while running a code. We also can disable some parts of the code by using the comments.
IF we put a Single Quotation mark (‘) or “Rem” keyword before a code line then it will become a comment.
Sub test1()
'this is a comments
Rem this is a comments
Dim t As Long
t = 40000
End Sub
We can comment multiple lines together by using the Edit window in the Visual basic editor.
To open the edit window in Visual Basic Editor- Go to View>>Toolbars>>Edit
Show Edit Window option
Below given window will be displayed
Edit Window in Visual Basic Editor
Select the multiple lines of your code and click on the Comments Block button (highlighted below image)
Comments multiple lines
The selected line will be Comments and will not be executed during the code execution.
Selected line has been comments
To Uncomments select the lines and click on Uncomment Block button (highlighted below image)
Uncomments Multiple lines
The selected line will be Uncomments and will be executed during the code execution.
That's all for today. Stay safe and blessed we will meet at the next lecture I hope you guys have learned it. If anyone has a question about this course or query kindly comment first. Best of Luck.
Thank you!
Regards: @ghazi.vani
