Macros & VBA Training Series Part 1:
Writing Your First Macro & The Excel Object Model
Double-click video to view in Full Screen HD.
Download the file used in this video:
Macros & VBA Training Series
Enroll for FREE
The video above is the first in a 3-part training series. Can I send you the next two videos?
Please click the link below to enroll in the free training. I will send you video #2 in just a few days.
Please click the big green button above to register for the free training series.
About Me
Welcome to Excel Campus! I am excited you are here!
My name is Jon Acampora and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. When I'm not looking at spreadsheets, I get outdoors and surf. 🙂
What Process Do You Want To Automate in Excel?
Please leave a comment below with your answer, and any questions. Thanks!
Thanks for the concise and effective way in explaining how to write a macro in VBA.
I have a report with 15 columns or more of data and around 4000-20000 rows that I need to copy and paste to a master template located in a different workbook. I would like to automate this task instead of doing copy and paste values. The template has the same header of the original report.
I have another report where the headers do not coincide with the headers of the master template, sometimes I link the rows to the master template. I had tried to use Vlookup, but my problem is that some records have the same file number with different values on the same worksheet. Therefore, I avoid using vlookup for this task.
I would appreciate your input.
Thanks for the help.
Lilian
P.S. I would like to take a course on VBA, are you apt to teach me? 🙂
Hi Lilian,
Thanks for your comment! Those are definitely problems that VBA can solve. Power Query is another tool that will help solve your first problem, where the headers are the same.
I do have a VBA course that covers beginner to advanced topics. It’s called The VBA Pro Course, and it will be open for enrollment in less than two weeks. I will send you more details on the course when it opens for registration. Thanks again and have a great day!
HI John
Thanks for the intro, simple and effective.
I’m keen to automate worksheet analysis. Looking forward to the next video.
Regards, Steve.
Thanks Steve!
Hii Jon,
I just want to tell you thanks for this video.
but I want something other then it i.e. the macro used by some government department like tax forms issued to clients & auto generate in .xml files. can you tell me about that type of macros.
Hi Jon
This first video was awesome, I’m in construction business and I do all my take off in Excel, this video and the next one will help me to have a clear vision on prepare small take off estimate program to speed up my estimating procedure.
Thanks I know I need to learn a lot, but I will try the best I can.
Lachi
Thanks Lachi! I’m happy to hear that you enjoyed the videos. You are right that there is a lot to learn with VBA. I think it’s best to take it slow and learn by practicing. I know you will be happy that you decided to learn VBA, as it will save you a lot of time. Best of luck with your program! 🙂
This video is really awesome. I hope we will go in more depth in coming videos. This is the first time when I am watching such a simple video dedicated to VBA. Thanks a tonn!!!
Thank you Suresh! I really appreciate the positive feedback. We definitely go in more depth in the next 2 videos, and I hope you will learn a ton! 🙂
Hi Jon,
Thank you so much for this great awesome video. It’s super helpful and very detailed. I would love to become a VBA expert as you are! Thank you so much
Thanks Candy! I would love to help you become a VBA expert! 🙂 And my VBA Pro Course is designed to do just that. I’ll send you more info on it in the coming weeks, as it will be opening for enrollment again soon.
Thanks again and have a great day Candy!
Fascinating! Having trouble downloading the file to my Mac though. I work for a non-profit and would like to automate creating a pivot table for each Case Manager of donations from their accounts, after the monthly update of the Excel table containing all donations.
Thanks Lisa! I will email you the file. Your pivot table project sounds like a good case for some VBA automation. 🙂
Hi Jon,
Great video and I find that you explain all the points very clearly.
I need to develop excel know how for two uses:
1. Automating weekly and monthly sales reports using pivot tables regrouping all sales channels and charts for visual reference.
2. Trickier, and probably not really what excel was intended for: automated translation of large volumes of repetitive content by identifying repeated sequences and establishing a glossary. I’m getting the hang of the text functions used to explode a cells textual content into individual coherent sequences, and if you believe that VBA can help out further, then I’m all ears
Thanks again for the video and I’m looking forward to number 2.
Cheers,
Col
Hi Jon,
you are a great teacher! thanks a lot for great introduction.
How difficult is it to create macros based on a flexible number of workbooks in the folder?
E.g. to have an overview of costs in one Excel workbook and reference it to workbooks saved in the particular folder. If I add a workbook with completed folder, it comes in the overview workbook. If I remove a workbook with a project from the folder, it takes it out from the overview?
Thanks a lot again for great presentation! Kitchen example is brilliant!
Thanks Gintare! That is definitely possible with VBA. I actually teach this topic in one of the sections of my VBA Pro Course. We build an application I call the File Manager, that allows you to automate processes on a set of files. You can also use Power Query to quickly get a list of all the workbooks in a folder, then use VBA to manipulate those files. Automating this type of process saves a ton of time. Thanks again Gintare!
Great video, Jon, and certainly looking forward to more of this series.
I do R&D so I deal with a lot of repetitive data reduction. I’ve templated spreadsheets as best I can but it’s still a lot of copying and pasting and simple commands like vlookup on their own are only so useful. I’d like to be able to automate the incorporation of data from various sheets to a comparison page, then populate summary charts or scatter plots of different parameters which are either output or calculated from the data.
Thanks,
Chris
Thanks Chris! VBA can definitely help with your data tasks. I think you will find the next video useful for that. Please let me know if you have any questions. Thanks again and have a good one!
Hi Jon
This is a great starting point for me. The task I want to automate initially is copying and pasting a range of data from one worksheet to a summary worksheet in the same workbook, then printing the worksheet. (I track all invoices to be paid by our business, then send the invoices and summary sheet off to our accounting team located on the other side of town on a weekly basis). As I am constantly adding to the data, the range to be copied is variable.
Looking forward to the next videos.
kind regards
Karen
Awesome! Thanks Karen! That sounds like a perfect task to automate with VBA. I also have an article and videos on how to find the last cell or row in a sheet with VBA. These techniques work well when the range is variable. The techniques in that article are a little more advanced, so don’t worry if you don’t fully understand them yet. Thanks again Karen!
Excellent presentation. Thank you for sharing your expertise with us. Much appreciated.
Thanks Babu!
I have used the record function in the past but It is good to see the basics which will help me understanding them a lot better. My initial goal is to format a lot of pie charts with the same color and other formats options rather than do each one by manually. Looking forward to the next video…
Thanks Barry! The macro recorder is a great tool because it helps you learn the properties and methods of specific objects. It also helps to have a good understanding of what all that code means, and how you can modify it to fit your needs. You will definitely be able to update and modify your charts with VBA. Thanks again!
Hi, this was a very professional tutorial, that shows alot of well explained information in short time, even if I am using vba in excel from long time, from this tutorial I have learned something, that I wasn’t use before, like short keys F12 and ALT+F11. I am exciting to watch next tutorials. Thank you.
Thanks Zoltan! I’m happy to hear you learned some keyboard shortcuts too. I love keyboard shortcuts! Thanks again and have a nice weekend.
Hi ,dear Jon
Thanks to your pretty perfect learning . I kindly require you to continue this .
Thank Mano! All these wonderful comments inspire me to keep creating videos and teach Excel.
Thanks for the easy to understand demonstration! It sure beats the excel vba book I’ve been trying to use to teach myself.
Awesome! Thanks Brad!
Excelent video. Help me understand how VBA code is structured. By using VBA, I want to consolidate the daily reports from all manufacturing areas (production, quality, maintenance and raw materials warehouse) into one single report to the General Manager. This tasks take me more than 3 hours a day as it includes tables, graphics, summaries, etc.
Thanks Horacio! That is definitely a task that VBA can save you a lot of time with. I recommend starting with the piece that takes you the most amount of time, then continue to automate from there. It’s easier than trying to conquer it all at once. Thanks again and have a great day!
I would like to automate many of my grant management and accounting tasks that I do on a daily and monthly basis. I have already started to use macros to do some of this stuff, and it saves me a ton of time. Currently, I download transactional data every month to create financial reports for my faculty. I created my own excel spreadsheet to analyze this data and create financial reports. However, it would be nice to add in different conditional macros to do various things. Also, a way to automate the whole process would be nice too! I have many, many ideas but I need to learn VBA and macros more to implement them. 🙂 Anyone that wants to coordinate with me, free free! My name is Aleesa Toman. My email is: [email protected] and I work at Wayne State University in Detroit, Michigan.
Hi.
I have recently found that all my excel files on my PC are corrupted beyond repair.
Is there a way for me to extract the data, using vba code, from the sheets without having to open them since they cannot be opened?
Hi Vernita, That sounds like a very frustrating problem. Sorry to hear that. I’m not sure that VBA could do that without opening the files. There might be some other tools out there that can read the XML data from within the file. I don’t know of any specifically. Best of luck to you.
Great material! Looking forward to #2
Thanks Jasmine!
Hello, enjoyed the video. I used to program in Visual Basic but VBA confuses me some.
We use Quickbooks and I work for a construction company. I need to use Excel to summarize cost data on a job to one page, summarizing by cost code in my own format. Quickbooks does not make this real easy. I have built some rather simple code and macros based on examples I see on the Internet but do not fully understand all of the code.
I also would like to use Excel to make selections on a contract, press a button and build a customer contract in Word based on those selections to make it easy for my supervisor to build job contracts.
Thanks Reita! I used to work with QB and know what you mean. That’s actually how I got started with VBA. Developing processes in Excel that QB didn’t do. Hopefully this series will help you start to understand the code better. Thanks again and have a great day!
Hi – below are the things that I’m interested in automating currently. FYI, my team and I work on Macs and have Office 2011 for Mac:
– Daily data pulls from multiple source spreadsheets
– These sheets are Google sheets that are generated by users across multiple departments.
– We need to create a consolidated sheet of data from these individual sheets, based on a range of dates relative to “today”.
– Is it possible for an Excel macro to operate on a Google sheet directly or will we need a script external to both applications that first saves the Google sheet as an Excel document and then executes the macro?
– Cleanup of data received from clients to prepare it for input to existing automated workflows.
– Currently done with formulas, but many have become too complex and difficult to maintain.
– Within the macro, may want to offer the user a way to make a decision that would drive the next step of the cleanup since there are multiple ways the data may be entered by the client and different rules that have to be applied during cleanup based on options within the data.
– Apply formatting based on data input – are there options via macro that are better than using conditional formatting alone?
– For existing Excel templates maintained by my team, determine if we can replace some of the complex formulas with macros that handle the calculations or auto-population done by the formulas. Most of these scenarios handle auto-populating cells based on user input in other cells.
– For the same templates, I’d like to include some instructional dialog popups for helping the user to know the next step as they do their input or make changes. The users of these documents are not typically heavy Excel users, so some interactive instruction would be helpful for them.
– Overall, I also need to understand how any macros we create would function across multiple platforms or versions of Excel. Most of our users are on Macs using Office 2011 for Mac, but several of our users who work at other sites are on PCs, typically with Excel 2010 or later. Does platform matter for macro execution (as long as no one is on 2008 for Mac)?
Thanks for the opportunity to participate in this training!
Beth
Hi Beth,
Lots of great questions. I see if I can help with most of them. In general, I think that you should only use VBA when absolutely necessary. If formulas or conditional formatting will get you the desired results, then stick with that. However, there are lots of instances where formulas get complex and slow down the calculation speed of the workbook, especially with the Mac version. In this case you will want to use VBA.
Checkout this page for info on getting data from Google sheets with VBA. I have not tested this, but looks like it’s possible.
I have worked on a few projects where Mac and PC compatibility were required. If you are developing your macros on the Mac version, then most things should work on the PC version. However, developing on the PC version presents a lot of issues because the Mac version is not fully compatible. Especially with ActiveX controls that you place on the spreadsheet. I would avoid those all together for the Mac version. I would highly recommend doing a lot of testing on the PC version if you are developing in the Mac version. The 2016 Mac version does not contain a VB Editor yet. This is just good to know in case you are thinking of upgrading.
I hope that helps. Thanks again and have a great weekend Beth!
Thanks for the great intro. to Excel macros and VBA!
Thanks Ken!
Hi Jon,
I am from Guadalajara Mexico and it is quite difficult to find good books about Excel & VBA. Can you recommend some authors or titles?
Great videos.
Thanks Carlos! I enjoy the Power Programming books by John Walkenbach. Here is a link to the book on Amazon. Those are giant, in-depth books. His Excel VBA for Dummies book is also a great read that is shorter and easier to consume. I hope that helps.
Jon, thank you for your reply!
And thank you for the book recommendation. I will look forward to get it!!!
Great videos!!!
regards.
I am interested in automating the update of about 150 files that read 3 files of data and then updating a summary file of the individual files.
I also would like to understand how to make a correction of a formula in all 150 files.
Thanks…. the initial video was very helpful.
Thanks Janice! That’s a task I do a lot with VBA! In my VBA Pro Course I actually have an entire module dedicated to creating an application that will do just that. I call it the File Manager and it allows you to run any macro/process on any number of files. It saves me a ton of time every month! I teach how to create the application in the course, and you can also download the file to start using it immediately. I’ll be sending you more info about the course when it opens for registration. Thanks again and have a great weekend!
This is a great training, it is very clearly explained. It’s easy to follow through and understand. I would like to learn VBA to see how I can use it in my job and improve productivity. Thanks
Thanks Laura! I’m sure you will find a ton of uses for VBA. It’s gets addicting… 🙂
Great video. Would like to know. How to do pivot tables
Thanks Dave! I have another video series on Pivot Tables. Andy makes an appearance there as well. 🙂
I have a series of KPI’s which are collated from various data sources. I am hoping that VBA will help automate the process.
Thanks Hannah! I’m sure VBA can help with that.
Hi Jon,
congratulations, I first approached the VBA & Macro world last winter but I had never understood them as clearly as now.
As I work in sales, the functions that I want to automate are definitely sales forecasts, trends analysis (like you showed in the video), outcome vs forecasts and budget planning.
Looking forward to the Parts 2 & 3.
Best,
Awesome! Thanks Denis! I’m happy to hear that these were helpful. I use VBA for a lot of sales forecast and trend analysis as well. It’s a life saver. 🙂
Hi Jon, thanks a lot for your reply!
And is there any article on you can suggest me on sales forecasts, analysis, budget, etc?
Also, are you going to address this topic too on part 2 & 3 of the training?
Best,
GOOD
Hi Jon,
Awesome and thanks for the VBA training with “My First Macro”, the book I used in KG for learing ABC with ” My Fist book of ABC”. 🙂 . Keep me update for the continuous updating in excel and VBA.
I have a worksheet that I inherited which I have to update daily. The updated information is in the system we use at work. There is a .csv file that I can pull from the system that contains all the information however, I have not been successful at pulling the info into this worksheet. This worksheet is our master schedule so I really don’t want to mess it up but I spend a lot of time updating manually what should be pulled into the spreadsheet. I’m hoping these videos will help.
Thank you for this free video series, I’m really enjoying this first video. I’m using Excel on Windows 10 at one location, and on a Surface Pro at another. Looks like the Surface Pro doesn’t allow VBA – Can anyone confirm this – maybe I’m missing something.
You should be able to use VBA on the Surface Pro. The Alt+F11 keyboard shortcut might not work on the surface keyboard. Try Alt+Fn+F11. You can also open the VB Editor from the Developer tab on the Excel ribbon. Please let me know if that helps. Thanks!
Very informative and helpful. I have only recorded basic macros such as one to refresh all the pivots in a workbook. Very simple to record. This does help me understand the parts of the macro. I have a workbook that has multiple reports and charts within it. I will have to think a bit to see what would be good to have automated. Probably a lot.
Thanks Laurie! Glad you found it useful. VBA can automate just about anything, so I’m sure you’ll find a lot of awesome uses for your file. Thanks again!
how to VBA intsaling in dowload ?
Hi Puan,
VBA should already be installed with Excel. Are you not seeing it when you press Alt+F11? What version of Excel are you using?
JON ACAMPORA : ooh okay, be able VBA with Excel yet there is thank 🙂 … first, i should question, but in netbook open Excel > File > My Macro cannot whay ??.. helpfullness are you question 🙂
Very nice start! What is the Mac equivalent to the F8 debugger command on Windows?
Thanks.
Thanks Jon! That’s a great question! I learned something new finding the answer. 🙂
The F8 Equivalent on the Mac 2011 version of Excel is Cmd+Shift+I.
It’s the Step Into command on the Debug menu. I added a screenshot below that shows the Debug menu in the Mac 2011 version of Excel. The keyboard shortcuts for each command are listed on the right side of the menu.
Thanks for the video. Can i have more about the Macro Coding?
I need to learn this. But i am 0% knowledge about the macro.
Thanks,
Chee Hong
Thanks Chee! I will be sending you the other videos in the series with contain more information on coding macros. Thanks again!
Very useful, thanks alot
thanks for the basics. I’ve worked in VBA for a couple of years, but it helps to understand some of the structure & concepts behind the language
Thanks Health Junkie! 🙂
Thanks for the video – this is my first ever foray into macros and VBA. I’m still trying to imagine what I can do with this, so am keen to see the next two videos.
I have a task that involves different people emailing their workbook in weekly, then data is copied and pasted from the individual books into a summary workbook. The sections that are copied change each week (ie that week’s summary is copied from one sheet in each book, but the next week it would be on the line below). I would like to know if it is possible to have this process automated by macros and if not, how to change the system so that it can be!
Cheers, Paige.
Thanks Paige! Yes, that process is definitely possible with VBA. There are many ways to go about it. I think the next two videos will show some techniques that will help with this. Please let me know if you have questions after you watch those. I’ll be sending them out in a few days. Thanks again!
Great, thank you! Looking forward to them 🙂
Do all three videos have to be watched within the next five days (I only ask as I will be going on vacation this week with no time to consume the content beforehand)?
Hi Peter,
No you do not have to watch the videos immediately. You will be able to access the video pages in the future. Just click the button above to register for the videos, and keep the emails I will be sending you. Those emails will contain links to the video pages, including this page. You can come back any time and watch or re-watch the videos. Please let me know if you have any other questions. Have a great vacation! 🙂
Greetings, Jon!
I wound up here through Mynda’s promotion on My Online Training Hub. Excited to learn about Macros and VBA and truly appreciate your teaching style. My particular problem at present is a pivot chart on a dashboard worksheet that is not updating from it’s source. The data and source pivot chart are housed in a separate workbook from the dashboard. The pivot chart in the dashboard does not update when I update the source workbook. Aside from being excited about learning Macros and VBA, I was hoping that this would give me some solutions.
Regards,
Nikolas Larum
Thanks Nikolas,
That’s a great question. Typically when you copy/paste a pivot chart to a new workbook, the chart is no longer linked to the original pivot table. You can see this by looking at the source data of the chart. The chart data series will be hardcoded numbers. It’s best to have the pivot table in the workbook with the chart/dashboard. You can have the pivot table on a hidden sheet if you don’t want the users to see it.
You could also use VBA to copy the chart or update the source data manually when the source data is updated. Another alternative would be to use a regular chart, instead of a pivot chart, that would keep the links back to the source data workbook. As you can see, there are a lot of ways to solve this issue. 🙂
Jon
Great explanation. I was really impressed.
I have used VBA on and off but have never been totally comfortable with it.
Some things I am working on:
Each month I have about 10 worksheets and I need to email each one to a different person.
2nd – downloading data into a worksheet from an external source.
Thanks Patricia! Hopefully this video series will give you a little more comfort with using VBA. The third video should help with exporting your worksheets, and there are some great resources online that you can use to automatically email the files.
VBA is great for downloading data, and you might also want to look at Power Query. This is another great tool that can make that process really easy.
Will VBA run on an iMac?
Thanks
Hi Peter,
VBA runs in Excel 2011 and 2016 for Mac. At this time, the 2016 does not contain a full VB Editor. Therefore, you will want to use Excel 2011 for Mac to write macros and do development. Thanks!
Congratulations! As a president of a Toastmasters club I noticed immediately your excellent use of ‘voice’. Your use of clean and articulate English combined with the cadence and quickness of your words demonstrates your intelligence while the use of common English words specifically ‘kitchen’ examples promotes a strong understanding of the VBA terms. For as often as I have attempted to ‘program’ in VBA this is the first time I ‘understand’ the meaning of ‘property’ and ‘method’. Thank you!
Thank you Allan! I really appreciate your kind words. VBA is not the easiest topic to explain, so I try to relate it to everyday objects that everyone is familiar with. I am happy to hear that it helped you.
Great video!! I need to run a task based on a cell value in column A until either there are no more values in column A or there is a change in the value. ie., Column A contains either the values 880 or 875. These values are grouped together. I need to run my task for 880 until it changes to 875 or I’ve come to the end of my worksheet.
Thanks Reetz! In the next video you will learn about Loops. Loops are an extremely powerful feature of VBA that will help you accomplish this task. You can basically loop through all the cells in the range/column and test each with an IF statement.
You could also load the range of cell values to an array, to make this loop process lightning fast. Let me know if you have questions after you see the next video. I’ll be sending it to you next week. Thanks!
Hello Jon,
Thanks for this excellent video. Are Macros&VBA the same for access as for Excel? I have built a DB in access but my boss wants the results back in Excel as he feels more comfortable. I have the macro that exports file in a specific folder but the result is not nice. And I have to reformat it. Additionally I would like to keep this workbook to receive monthly data, create an analysis sheet and an end year summary. I think that part of your videos go in the same direction. Looking forward to reading from you soon. Best regards
Hi Vicenta,
Yes, you can also use VBA in Access. There are some differences in the Object Model, but the coding language is the same. You might also want to look at Power Query to bring the data into Excel from Access. This is a great tool that can make it a lot easier, with little or no coding required. Here is an article I wrote on an overview of Power Query.
Thank you, Looking forward more videos !
Thanks Jon for providing this in-depth video on “writing Your First Macro & the Excel Object Model” It’s been several years since I wrote my own Macro – and I just realized what a valuable time saver this can be in my daily work schedule… I’m interested in automating my Excel worksheets with my name, date, time, page number of page number[s] so that each time I create a new workbook/worksheets, I can eliminate the time it takes to each of these steps… Currently, I’m using an Excel template… which is better and quicker? Macro vs. template!!!
Hi Sarah,
If your template already contains all the info you need, then that should be a pretty fast solution. If you still need to modify/customize the file created from the template based on certain conditions, then you might want to create macros to automate those processes. Macros can definitely save you a lot of time. 🙂
Great video lesson. I have been using macros for several years now to automate processes, but most always have used the macro recorder because I did not know the VBA coding. This opens doors for me that will help me be even more efficient in use of Excel. Thank you. We have a need to create a summary worksheet of certain cells in several workbooks in one common folder. We have quotations as individual files in a folder that we need to need to track information from for reporting and analysis into one worksheet.
Thank you Phil! I’m really happy to hear that. The next video in the series will help with the basics of creating a summary report. You will learn how to use Loops, which are a feature the macro recorder does not create code for. This can really make your code fast and efficient, and allow you to accomplish things that the macro recorder can’t do. Don’t get me wrong, the macro recorder is great for getting snippets of code and learn objects, properties, and methods. But it also has it’s limitations. I will be sending the next video next week. Please let me know if you have questions about your summary report after that. Thanks again!
This class was awesome! After all of these years you explained macros and vba in a way that I could finally understand. I am looking forward to the rest of the classes.
Thanks Detra! I’m so happy to hear that.