Visual Basic in Excel – Day 1
Heartiest apologies for another break in post yesterday. I think you kinda have to deal with it. Honestly, i am learning and growing in this with you, so yesterday gone in designing the syllabus for both of us. I will give the syllabus for coming days till today’s evening.
I welcome you all in the first day of visual basic learning. From this day onward, we will directly write macro in excel’s visual basic application. Hopefully we will master it soon. Your support and involvement is very much needed, to keep the learning bar on top.
The good news is that you don’t need any programming background for this despite it is one of the basic platform where you can learn to write and apply code. Once we get the confidence to write and apply codes on this basic analytics platform we will jump on the more advance Statistical and Analytical Programming tool R. I am certified R programmer and practicing it since 1 year so yeah i will guide you in the fairly easy way.
For writing the first and basic macro in visual basic please follow these steps –
- Open your excel 2016.
- Click on Developer option and go to Visual Basic in code ribbon.
- Firstly save your excel sheet by going to ‘save as’ option *Twist* change the ‘Save as type’ to Excel Macro-enabled workbook from Excel workbook and give name to your excel sheet.
- After saving now please come back to excel VBA and click on ‘properties window’ from the View’s drop down menu and you will see a properties window appears in the left side.
- Click module from the insert option.
- Give any name to your macro in the properties window, eg. write_a_macro.
You will see write_a_macro window appear in the centre. GREAT!! finally we reached to coding window.
- Please repeat the code as given below :
MsgBox “Learning VBA”
- Click f5 and whooa your Learning VBA msgbox appear on the excel sheet.
Congratulation on your first visual basic coding in excel. We will learn many more interesting and complex codes in the coming sessions, so stay tuned. Thank you for joining me today.
See you soon. 🙂
Firstly, i apologise that i could not join you yesterday but i promise no breaks from now, till we master the Excel Analysis.
So let’s continue …
Today i will tell you about macros, VB and VBA in excel and how we are going to take there make to learn analysis in excel.
Now open the excel 2016 application in you laptop/computer and please follow these simple steps –
- Click the file option from the ribbon.
- Click on options from the drop down menu list.
- Select the customize ribbon option from the square box appeared on the screen.
- Then click on Developer tab from the main tabs and click OK.
You will see the Developer tab appear on your screen. Now please click the developers tab and you will see the options like Visual Basic, Macros, Record Macros etc.
Macros, in simple language repeat the recorded action. Let’s perform a simple macro before we enter into the world of Visual Basic. Please follow these simple to record and perform this simple recorded macro –
- Click on Developer’s tab.
- Select the Use Relative References tab from the ribbon.
- After that select the Record Macro tab. You will see a square box of record macro will appear on the screen.
- Now, as you can see the ‘Record Macro’ square box has 4 options as – Macro name, shortcut key, store macro in, Description.
- Firstly, give a name to macro name option – eg. highlight.
- Second, choose a shortcut key as shift+ any letter – eg. shift+W
- Third, for storing the macro you have 3 options. They are – This workbook, New Workbook, Personal Macro Workbook. I am choosing ‘This workbook’ option.
- Finally in the Description option you can description about the macro. Eg. It will color the cell. Then click OK button.
After clicking okay button you will observe the Ready with a grey square box appear at the bottom left. Now click on any cell in the excel sheet and and fill it with any colour from the fill colour option in the font ribbon. Now click on another non highlighted cell in the same row and the pause grey button next to Ready in bottom left corner. Now again on the Developer’s tab and click on Macros option, you will see the name of recorded macro. Then select your highlight macro and click on Run. You will see your recorded action is repeated. Woww !! isn’t it.
Again click on Macro and now click on the edit option. You will see the Microsoft Visual Basic for Applications box will appear with the box in the middle. It’s the code of the macro we just recorded. Great! Right. Don’t get panic as we won’t write such codes now as we lack practice but for sure soon we will learn to art to write much better codes.
Anyways today’s purpose was to make you familiar with the working of macro and to show you excel’s visual basic application. The fun part of visual basic has arrived, so don’t miss any blog in the coming days. I am sure we will master it soon.
See you tomorrow. Take care and keep learning. 🙂
This is your friend and data analytics learning partner, Shubhi.
So without wasting any more time on intro let’s start learning analytics as there is a long way to go.
The first tool that i am using in the analytics is Microsoft Excel. We will be using the tool with the help of its advanced technique VBA Macros. I have very little exposure to programming throughout my educational journey and i am not hesitant to say that up until last year, 2018 i was little scared of computer programming because of its syntax precision. During my 6-months internship tenure in MBA i got the opportunity to learn and apply Programming R at basic level. Taking that journey forward i am again starting learning programming but this time i am starting with Excel.
There are two main reason behind this – first, someone who is naive in coding, learning analytics through macro in excel is best way to begin with. Second, in excel 2016 version there are 16,384 columns and 1,048,576 rows which is an excellent data capacity for beginner to learn for. Even if you don’t have such large data don’t worry, we will take some small data and will learn and apply codes on it. The same logic will apply to large data set only the value of assigning variable will change once we will be analysing the large data set.
So stay tuned with me in the coming days, i will be giving the introduction on Excel VBA. The day is not far when we will master the Excel Analytics.
See you soon.
Allow me to introduce first…
My name is Shubhi Mishra. I am a healthcare enthusiast. I have pursued B-tech (Biotechnology) & MBA from Banasthali Vidyapith, the university not only consistently ranks among the top 8% of Indian Universities as per MHRD (Government of India, Ministry of Human Resource and Development) but is also acknowledged as the best women’s university of India.
Like many i too was in dilemma of what to do after finishing studies. I decided not to leave my domain of healthcare and to do something relevant in this field. Then the Question arises what relevant i could do on my own? I am just the team of two – me and my laptop. Well let me tell you this team of two can do wonders. The only thing we need is to remain focussed in our vision and mission and the cherry on top would be if could get your time to time guidance.
My vision is of artificial intelligence enabled healthcare technology and my mission is to master the analytics techniques. I dream of doing something worthwhile in healthcare rather than being lost in corporates. As the people says the world on its technological revolution with Data Analytics and Data Science. So i would like to join the team of people who are taking Healthcare forward with the help of Analytics and Artificial Intelligence. Taking advantage of online world-class analytics and data science learning platforms and combining them with my educational knowledge in Biotechnology and Business i would like to master the skills needed in the revolutionize the healthcare sector.
I will keep my blogs update with my analytics learning. Though the field is vast and i am just the beginner but i am determined to be the part of healthcare revolution. I will also keep you update with the artificial intelligence led products in healthcare. The field and challenges are huge but i am determined.
So stay tuned. It will be fun. 🙂