Jason Jay - EXCEL VBA Step-by-Step Guide To Learning Excel Programming Language For Beginners

Step-By-Step Guide To Learning Excel Programming Language For Beginners Jason Jay Copyright 2017 by Jason Jay - All rights reserved. If you would like to share this book with another person, please purchase an additional copy for each recipient. Thank you for respecting the hard work of this author. Otherwise, the transmission, duplication or reproduction of any of the following work including specific information will be considered an illegal act irrespective of if it is done electronically or in print. This extends to creating a secondary or tertiary copy of the work or a recorded copy and is only allowed with express written consent from the Publisher. All additional right reserved.

TABLE OF CONTENT

If you already know how to use Microsoft Excel but therere a few things you cant do, it is time to learn the strongest functionality it has, Visual Basic for Applications (VBA). Visual Basic for Applications is a programming language incorporated in Microsoft Excel, Access, PowerPoint and even Word, which let you do all things you already know about them and much more. For example, you want that every time you open a specific Microsoft Word file it writes automatically the current date two lines below where you left last time. Or maybe you want a whole spreadsheet of Excel without formulas on it and still applying them as if they were there. How would you do that? All these things and much more are done with Visual Basic for Applications for Microsoft Office. Look at the example below: It looks like a program made for analysis, and it does.

Guess what program it is? Probably you are thinking it is not any Microsoft Office program, but let me tell you that it was made with Microsoft Excel, how would you do something like that without programming? Theres no way! Youll learn much more than that and will be able to create your own programs using Visual Basic for Applications (VBA). If you need a very specific program for your business analysis, something for personal use, or even just for having fun, you need Visual Basic for Applications now!

Every programming language has similarities between their fundamentals. The functions IF, Then, Loop, Close, Open, are just some of them. Well learn the basic ones first; it will be necessary to understand how VBA works. Well use Microsoft Excel 365 for this instructions and examples, however, from Microsoft Excel 2007 onwards it will work the same. Microsoft Excel doesnt show the Developer TAB by default.

It only has File, Home, Insert, etc. But theres no one called Developer. To access to this TAB there are different options, but well show the easiest one.

1. Right click to the Ribbon (any part inside the red box, except the buttons).

1. Select Customize the Ribbon

1. Enable the Developer checkbox and click the OK button.

1. You should see the Developer TAB available now.

1. How you Access the Developer TAB?

a) It is available by default in Excel. b) Right click on the Ribbon, Customize the Ribbon, enable the Checkbox for Developer and Accept. Youll see a few options available in the Developer TAB, by now well start to use the Record Macro button. Youll see a few options available in the Developer TAB, by now well start to use the Record Macro button.

A Macro is an automated sequence which will apply every time you play it. Lets see a practical example of it: Imagine that in your job you do the same process every morning. It takes some valuable time and even youre getting bored of that. The process is the following: a) You receive a Microsoft Excel file from your boss with some data and you need to write the date using Year, Month and Day in different columns. You do this because it is the format your job needs and youve been adding the same values every day for a few years. In this case an semi-automated process would be helpful.

Excel gives that option to all of us with Macros. A Macro is a semi-automated process which let you run a specific task using a shortcut. To create a Macro, follow the sequence below: a) Click the Record a Macro Button. b) Write a name for your Macro. (Needed) c) A shortkey which every time you press will Run the Macro. Be careful, dont add Ctrl + C or Ctrl + v, otherwise it wont copy or paste anymore, but run the Macro.

In case you want a more specific shortcut, hold the shift key as you press a letter. For example, ctrl + shift + c. To make it work, dont press ctrl as you add a short cut. (Optional) d) Store Macro in: Personal workbook: Will be available for all the files you open with Excel on that computer; New Workbook will be available for a new file only. This workbook, will apply only to the current open file. (Optional) f) Click Ok. g) Start doing everything you always do, which would be adding the current date in this case. h) Once you finish, go back to the Record Macro Button, which now is called Stop Recording. h) Once you finish, go back to the Record Macro Button, which now is called Stop Recording.

Press it and now should be saved. This would be a very simple Macro, it only adds the current date, but what would you think if you also need to import data from a web page which is updated every hour, and need to classify it using a few charts, and you do the same process several times a day. No doubt, a good Macro would be useful. The process to get it any Macro, is the same weve followed. Theres only an important thing to consider when creating one, it is to choose between using Relative References or not.

The Relative References button is just below the Record a Macro Button.

Once you click on it, it remains active until you click on it again. It is used to record macros in which the process should be applied to different ranges instead of one already set. Its functionality is very useful. A macro recorded without relative references will always repeat the process on the same cells used when recorded. But if you use relative references, the macro will run from the active cell. Using the example above, what If you need the dates written on cells F4:H4 instead of B2:D2? The only thing you should do is to select F4 and run the Macro.

Or select any cell you need and run it. But you need to record the Macro using Relative References, and then select the cell and run it, otherwise it wouldnt work.