Since not every year and month are the same (for example different number of days, different number of weekdays), the template for your data needs to be adjusted every single month in order to correctly show these differences. To define the date, we will create another worksheet called Date where the user can define the month they want the template to be created for. Rename the first worksheet to Cities and add the six cities by typing their names in the first column with a city on every row, as shown here: Figure 01: Cities Worksheet Open a new Excel file, save it as Excel Macro-Enabled Workbook (extension. Either we can include the cities in an array in the code, or we can define them in a separate worksheet in our template.įor this tutorial, we'll be doing the latter, which makes it easier for other people to add cities later on without any knowledge of VBA. In order to define the cities, we can do one of two things. Then we'll just run the macro and go do something else. If we were to do this manually, increasing the number of cities would consequently mean more work.īut in our case, since the macro will do the work for us, we can add as many cities as we like. Let's Define Our Parametersįirst we will define the different cities that we will be using for this tutorial. You can also find them on my GitHub together with the final result.
I have downloaded several Excel files of data which are free to use. But you don't need any experience with VBA (although it might help you understand the different concepts I'll introduce). In order to follow along with this tutorial, I expect you to have a basic understanding of Excel.
The template will be automatically created by a macro we build together based on the cities and date that we use as parameters.įinally, we will also learn how to import the data from another file in our template. In this tutorial we will be writing a macro that prepares a template to note the temperatures in several European cities – Amsterdam, Barcelona, Berlin, Brussels, London, and Rome. The language is object oriented, it's written in C++, and it includes all the features you would expect in a coding language these days. Intro to VBA and Overview of the ProjectĪlthough VBA was declared legacy in 2008, this implementation of Visual Basic can help you automate the repetitive tasks in your daily life. So I will take you through the basics of VBA, such as how to use variables, loops, if-else statements, arrays, and dictionaries. This saves me quite a lot of time in my day-to-day life.īased on the fact that you're reading this tutorial, I assume that you would like to be able to do the same. Below you can find an overview.I use VBA, or Visual Basic for Applications, to automate my repetitive tasks.
There are many free templates available, waiting to be used.ħ Data Validation: Use data validation to make sure that users enter certain values into a cell.Ĩ Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.ĩ Print: This chapter teaches you how to print a worksheet and how to change print settings.ġ0 Share: Learn how to share Excel data with Word documents and other files.ġ1 Protect: Encrypt an Excel file with a password so that it requires a password to open it.īecome an Excel pro! You can find related examples and features on the right side of each chapter at the bottom of each chapter. Each Excel workbook can contain multiple worksheets.Ĥ Format Cells: When we format cells in Excel, we change the appearance of a number without changing the number itself.ĥ Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.Ħ Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. When you start Excel, click Blank workbook to create an Excel workbook from scratch.ģ Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Learn how to use the ribbon.Ģ Workbook: A workbook is another word for your Excel file. This section explains the basics of Excel.ġ Ribbon: Excel selects the ribbon's Home tab when you open it.