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!
I have an existing code. The code populates values in a template which has pivot tables. The data is coming from a cube. Essentially, the pivotcaches are getting refereshed.
I want to create pivot charts in the existing code without creating the Pivot table. As the data is more than a million, hence a cube was created (which is what i was told). I would like to understand teh concept of cube also. The data is coming to the cube from sql server database tables with pre written queries written.
Hi Rajbir,
You can use the CUBE functions instead of pivot tables to bring the data into the spreadsheet. I hope that helps.
I have a invoice filled out for customer, and I would like to transfer the important parts of this invoice to another worksheet and I would like to transfer this information into a line after line and add a reminder date to the saved document. eg
Invoice No. Name. Address. product installed. Invoice total date installed date to be renewed. done/renew
536 Rod somewhere twin filters $250 4 June 2016 (+360 days) renew
I hope you can help. Regards Rod.
First of all, these are really useful videos. Thank you for you knowledge and expertise.
I am often presented with rather lengthy, multiple sheets / ranges of data where our end users request to find the differences amongst each range and identify those that match vs those that do not.
For example, workbook / worksheet #1 may have 25000 lines of data, with between 4 and 6 columns.
Workbook (or worksheet) #1 may have 25000 lines of data, with anywhere between 5 and 10 columns.
The 2nd workbook / worksheet may have the same amount of data, with matching columns that could be compared to WS1.
Users ask to summarize the differences in either a new worksheet, or add a new column in the current data identifying if there is a corresponding match or non-matching line of data.
Hi Jon, How can I convert the below formula into a module?
=IF(AND(B3<10,C3<=7),IF(D3400,D3600,D3800, “Need Consultation”)))), ” “)
Thanks in advance.
Ben,
I noticed you didn’t get a reply, so thought I’d try to help.
Your formula doesn’t appear to be a valid formula, so the VBA equivalent would also be invalid. Assuming you replace the invalid formula with a valid one:
1) Type in your formula
2) Turn on the Macro Recorder
3) Click into your formula and hit F2
4) Hit enter to exit your formula cell.
5) Turn off the Macro Record.
Result: a new macro with your formula translated into VBA format.
If you wanted to apply that formula to a range of cells, you could do it like:
Range(“R3:R10”).FormulaR1C1 = “=IF(AND(R[-1]C[-16]<10,R[-1]C[-15]<=7),IF(R[3396]C[-14]=R[3596]C[-14],R[3796]C[-14], ""Need Consultation""), "" "")"
Thanks Jomili! The macro recorder is definitely a great way to get the formula code in the R1C1 notation.
Wow! I never thought I could be able to understand how to write code in Excel but thank God and you for these videos that are so clear in explaining and showing the concepts. It feels to me like putting the pieces of a puzzle together and knowing which piece goes where. I was blind but now I can see. I am a beginner in my VBA code writing, and even though I started to take Excel classes, sponsored by the company I work for, I have to say that I feel more comfortable with these videos than in the actual company classroom. I really understood and learned more with these videos. My goal is to sharpen my skills by escalating in my Excel knowledge and be able to get a better paying job in the near future and in the process help other people in the work place. I am looking forward to continue learning Excel.
Thanks again.
Hi MiniMe! 🙂
Thank you so much for your kind words. I am so excited to hear that you are learning VBA, and that the video helped you. Learning VBA will definitely help you improve your Excel skills and get that higher paying job. I can’t wait to hear about your success. Thanks again!
Excellent explaination to create macro
I would love to automate the layout of my pivottables, like lines, borders and fills.
Your training videos are awesome. They were easy to follow and understand. I would love to contribute to your site or purchase your products.
I want to automate to create a market profile chart with excel because it’s quite tedious and prone to human interventions.
I hope that you can help me with that.
Thanks in advance.
Adrian
Loved the first class and am just waiting for this links to the corresponding classes.
would like to create a box for a manager to enter in the info needed for my data base. Right now everything is manual and I’d like to make it run smoothly. So when the manager opens the workbook, I want for them to be able to select the date they are wanting to enter info and have all the fields for that date show up for them to enter. How do I do that and then have that info populate into data that can be converted to pivot tables and dashboards?
Thank you!
Good basic information, I am importing data from an xml source. the data has multiply line items for each major items imported, it could have 1 line per item or 20 lines per item would like to be able to have the data copied to a new sheet with a blank row between the major items to separate each major item with its sub items. The process would only copy a few of the columns from the import data to the new sheet,. I expect would need to run a loop to copy the rows and when the line numbers of the group starts over with 1 a blank line would be inserted.
Hi Jon!
I have two inputs :
1) An excel file with exchange rate
2) An excel file with accounts numbers with balance in different currencies
These two inputs need to be paste in a template with formulas that will give the current balance for that account. (This file is already created, no need to create a new one)
Output:
1) Excel file per account with ending balance
2) One summary of all of the account with the ending balance for analyze easily and faster
The challenge is to copy and paste in different excel workbooks these inputs an obtain a summary of each account and the ending balance in one single sheet just to make the analyze without going one by one each excel file.
Thank you in advance!
Hi Omar,
Great question! I have a 3 part video series on how to copy & paste with VBA that should help get you started. Here is the link. Check it out and let me know if you have any questions.
https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/
Thanks!
Awesome!!!!
My immediate need is to keep track of uncoordinated folder creation and file naming out of the independent cubicles of creative co-workers.
I am hoping to very soon be able to
(1) monitor files and folder structure
(2) change file and folder names
(2) use pivot charts for relational queries
Thank you, wow
that was fast and informative
Thank you!
I have a workbook with multiple sheets with cells that are either protected and those thdat the user can input data on. so selected cells can be edited while some are locked. I’ve added a macro that disables copy, cut and paste so the formatting and cells with formulas are intact and can’t be changed. now, i want to be able to copy a column of data from another workbook to a selected column in my protectected workbook without changing the formatting or any part of the workbook?
Hi Reena,
I’m not exactly sure I follow, but it sounds like you will need to unprotect the sheet before pasting. You can definitely have a macro unprotect the sheet, perform the paste, then protect it again. I hope that helps. Thanks!
Hi Jon,
I hope to get a form created where I can enter clients details and a new client number can be generated for each of them.
Thanks, this is great!!!
Hi Alberto,
Yes, that can definitely be done with a VBA userform. We discuss more about userforms in video #3. I’ll be sending that next week. Thanks again!
Awesome!!
I would like to automate the formatting process of the monthly IntraStat report.
Main Problem: In Excel some columns will be summed up (summing up in the Data Tab), the result is a line with “Ergebnis: [Value]” (Ergebnis: Result).
How can i delete the leftovers, keep the new line with the result and afterwards just split the column up, so i have the result line with the summed up value without “Ergebnis”?
Hi Marvin,
You might want to use the Filters to filter the data, then delete the visible cells only. The filters are referenced as AutoFilters in VBA. I hope that helps get you started. Thanks!
I want to make a fully automated share stock report in ms excel.
Thanks.
Jon,
Thanks for the very clear video. I have been using macros for a long time but I want to understand them and write them from scratch. This is a great start.
My challenge is to select rows of data based on a value in a certain column, say “J”, then copy that row into another worksheet or workbook. The challenges for me are that there could be 50-60k rows of data and I want to select the rows from a non-repeating drop-down list of the contents in the “J” column.
Thank you for your video series and help.
Thanks John! That sounds like a task for Filters. The Filters can be programmed with VBA, and they are called AutoFilters. You can use the built-in filter drop down menus to make the selection. Or you could create a drop down in a cell and have the filter be applied when the user makes a selection from the list. The possibilities are pretty much endless with VBA… 🙂
Although I’ve been doing VBA since at least ’94, it’s always nice to see someone explain the basics in such an understandable format. Too bad this wasn’t around when I started. Really would have been useful. It took a lot of painful work to reach the level that I have and I’m still learning every day. Your videos would have made a world of difference. Sharing what you know is such a great service.
Thank you Sue! I really appreciate the nice feedback. I learned the hard way too, and it took along time for me to figure this stuff out. I’m happy to hear the videos are making it easier to get started. I love Excel and VBA because there is always something new to learn. 🙂
Hello Jon,
I am the training Monitor for my whole division and I would like to set up a macro that will read the date in the “Date Due ” column them send an e-mail to the person and his/her supervisor, CCing me, telling them they are due the training. I have seem this done and have the spreadsheet all set up to do this but cannot find any guidance on how to do the macro.
Thanks in advance for your help
Bill
Hi Bill,
Great question! Ron de Bruin has some great resources on how to send emails from Excel through Outlook with VBA. I always go there when I need to add this feature to a project.
Hi Jon – I LOVE Excel but I’ve never done VBA before. I do a lot of replication in my job though – most of it is updating daily spread sheets etc and I’d love to have a little Macro that takes me directly to a particular Pivot Table and updates all pivots in the workbook. I’m sure that is VERY easy but I’ve no idea how to do it. I’m going to try now having seen your first video but not sure it tells me enough!
Hi Katie,
That’s a great use for a macro. The code to update all pivot tables in the workbook is actually very simple.
RefreshAll is a method that will refresh all of the pivot tables and data connections in the workbook.
I hope that helps get you started. Let me know if you have questions. Thanks!
Is it possible to create a pivot table through a macro? I do a report quarterly showing our client renewals and have been re-creating the pivot table for each report.
Yes, absolutely. You can create a pivot table with VBA. Just about every action you take in Excel with Pivot Tables can be automated with VBA. Everything from adding fields to the pivot table areas to formatting and filtering. The object library for pivot tables is amazingly vast, and this can really help us automate boring repetitive reporting tasks.
How do I display the hierarchy shown in your video in excel 2007?
Very helpful!
Hi Bart,
Great question! Unfortunately there is not a good place to view it in the VB Editor. You can use the Object Browser (press F2 on the keyboard in the VB Editor), but I’m not a big fan of it. It does list all the objects, properties, and methods. It is just hard to navigate.
You can also use the Intellisense drop down menus like I show in the video.
Another good alternative is the MSDN help site for VBA. On the left side of that page you will see a long list of objects. You can click on those to see their members, properties, and methods.
I hope that helps get you started. Thanks!
Wow. I was always thought macros would be too hard to learn. This first video made it easy to understand. Now I’ll be looking at what I do for some ways to automate. I can’t wait to try this.
Awesome! Thanks for the nice feedback. I’m so happy to hear that you are learning macros Diane! 🙂
Great first video! Very easy to understand. I have written a lot of code for Word but none for Excel. We use a lot of spreadsheets to track Purchase Orders with a Summary sheet to pull all the data to one spot. Every time we add a PO sheet, we have to manually add it to and link it to the Summary. Looking forward to the 2nd video which may address how to automate these functions. Thanks!
Thanks Kathie! I think video #2 will be perfect for you. Please let me know if you have any questions after watching it. I will be sending it next week. Thanks again! 🙂
The keyboard shortcuts you referenced in Video 1 are not functioning for me. It could be due to I have a wireless keyboard with recordable shortcuts. Once my shortcut keys were programmed, even my F2 did not work like it used to in Excel. I use Excel 2007. I need to do a couple of things:
1. Some downloads from our vendors record family information down in the same column. (ie. Employee, Spouse, child 1, child 2). The information is not consistent, meaning, some of the information is for Employee only or Employee, spouse, etc. We need to move this information to a horizontal format. Meaning all the information from one family in one row. Haven’t found the best way to do this – would Macros work?
2. We run multiple reports in order to gather all the information we need, then we need to summarize it into one worksheet.
Hope this helps.
Hi Tammy,
The function keys on your keyboard (F1 to F12) might be shared with media keys or programmed functions. On a lot of laptop and wireless keyboards, you have to hold down the Fn (function key) while pressing F1 to F12. Some keyboards have a function lock feature that allows you to turn the function keys on permanently. Here is an article I wrote on the best keyboards for Excel shortcuts, with some of my personal preferences.
In regards to formatting your data, you can definitely do that task with VBA. You might also be able to use Power Query to unpivot the data. The use of VBA and Power Query together can make for some really nice streamlined data transforming and reporting.
I hope that helps. Thanks again!
We are keeping weekly attendance records of our members and I’d like to automate the process & produce a monthly summary ofall recorded info.
Hi Doug,
That is definitely a job that you can automate with VBA. You might also be able to use Pivot Tables to quickly summarize the data. Here is a link to another video series I have on Pivot Tables and Dashboards. Thanks Doug! 🙂
Hi Jon,
I want to import data from both MS Access & SQL Server 2012 into my spreadsheet (2013 or 2010), then format the imported data into table format (excel feature), then make one single pivot table using these 2 tables and also a dashboard with charts and graphs along with a summarized tables (using table feature) in another sheet (sheet 2) with complex functions and formulas.
This is my requirement. In the above requirement, everything seems to be a challenge for me right from importing data from MS Access and SQL Server into and then formatting these 2 tables in excel tables to pivot tables, dashboard with visualizations, summarized data tables.
Please let me know whether I can accomplish all these with this course.
Best Regards,
Sandeep
Hi Jon, Sorry this is not a technical question, but it would be really helpful if I could download your video, I¡m getting on a bit (73) but love playing/working with this fabulous program, but I need to keep going back to the video to check, (I am very new to macros/vba), I don’t think I have a use for Pivot Tables so I have stayed away from them…
Hi Graham,
You can come back and watch this video at any time. I don’t have a downloadable format right now, but you can keep the link and come back and watch it. Registration for the training ends in a few days, but you will still be able to come back and watch the videos. I hope that helps. Thanks again!
I gather data from three different systems and copy/paste them into one spreadsheet for sales tax return importing. Each of the three spreadsheets need to be formatted the same before I copy them into the combined sheet. This process is manual and takes me way too much time. I would like to automate the formatting process.
Hi Sheila,
That process is definitely possible with VBA. Copy & paste is one of the most common tasks we do in Excel, and VBA can save us a lot of time with this. I have a free video series on copy & paste with VBA that you might be interested in. Thanks!
You might also try Power Query. Power Query records keystrokes and saves them for future use. There is an excellent tutorial Getting Started with Power Query by Devin Knight at https://www.youtube.com/watch?v=-0irzkfF4s4#t=3.82.
hello,thank you for this video
i want to download this video, please guide me;
thanks
Hi Mohammad,
I don’t have a downloadable format for the video right now, but you can come back and watch it any time. Registration for the training ends in a few days, but you will still be able to come back and watch the videos. I will send emails with links to the videos when you register for the free training (big green button above). I hope that helps. Thanks!
Your method of explaining how to create macros/VBA is great. I am trying to teach myself Excel 2010 through books and videos on my own. I find that it is a challenge, but I can’t afford taking classes at a college. I am a senior citizen and still working. I would like to understand how to create macros/VBA.
Hi Anna,
Thanks for the great feedback. Well, you’re in the right place to learn VBA 😉 My best advice is to practice writing macros as much as you can. You will learn a lot just from opening up the VB Editor and practicing what you learn from the training materials. Thanks again!
Great video. Could you please advise what tools you used to create the video? I liked animated Andy and the color-coded text boxes/arrows.
Thanks Joan! I actually use PowerPoint for all the animations. I record the screen with Camtasia, and also use it to edit the video and add some of the annotations (boxes/arrows). I hope that helps.
Jon. Thanks for the info. I hope to give Camtasia a try soon.
Hi – Thank you very much for doing this. Your presentation was very clear, but a little too fast to take notes from. I have been trying to get started in macros, but am a slow learner and need practice doing each step (as opposed to just downloading the practice worksheet for example). Can I rerun the first video a few times until I “get” it without messing things up? And/or do you have a cheat sheet that recaps the basic macro startup procedure?
One application I am thinking macros may help is a workbook where the first sheet is a master with the basic salary info about employees, then several other sheets with the same basic info, but modified to reflect specific sources of pay (distribution percentage, period and the like). During the year there are changes (like a new employee) – can a macro help me to enter info just once in the master, running the macro to insert the same change into each of the other sheets without altering modifications that are unique to an individual sheet?
Hi John,
Yes, you can re-watch and also pause the video as much as you’d like. You can also come back to this page in the future.
And yes, a macro can definitely help automate data entry. Almost anything you do manually in Excel can be automated with a macro. Thanks again! 🙂
Jon,
This video training is excellent. It is my first time writing code in a very long while. It is great that I can pause it where I get stuck, and listen again or go hunting on a search engine to find how to get the feature you mention. For example, I could not get the shortcuts you describe to work with my setup of Excel 2010, so I searched “how to find…” and was able to get the functionalities you showed in the video – Had to start with getting the Developer tab in the main menu ribbon, in order to find the VB editor!
Thank you for offering your knowledge as a free service. It helps us newbies get comfortable enough to even want to continue! Looking forward to the next module.
Hi KD,
Thanks for the nice feedback! I’m happy to hear you found the developer tab. I need to add instructions on that. I do have a video on this page that explain how to enable the Developer tab as well. Thanks again! 🙂
Nice video. I’d like a macro to pull data in from another spreadsheet, copy down equations based on the length of data brought in, filter for specifics and update pivot tables. Thanks
Thanks Pat! That can definitely all be done with macros. Filters are references as AutoFilters in VBA, and they are great function to automate if you are repeatedly applying the same filters to your data.
Seems fast and easy way to learn!
Hi Jon,
I’ve used Excel a lot but I am technically challenged… 🙂 I have many things I would love to automate and I just have to figure out how. One of my jobs is at a restaurant, and we have to take the excel timesheets and go over them to set the start time to the actual shift start instead of the login time. It would be great to be able to automate this, but it would require referencing a second sheet (not currently in excel) to find out when the particular employee’s shift officially started.
Any advice?
Hi Teresa,
Great question! That might be something you can solve with lookup formulas like vlookup. Here is an article I wrote on an explanation of VLOOKUP. You can write vlookups to bring in data from other workbooks/files. I hope that helps. Thanks!
I have programmed before, but not VBA. I have an iMac, El Capitan and Excel 2016. I have been able to do all in your first lesson, however I do not get any drop down menus at all (when you press the dot after application (for instance).) I also do not have any menu bar at the top of the screen, only a few icons to run the program or step through, etc.
You are an excdllent teacher. My limitation of my system may make this harder than it should be. But I will go as far as I can.
Thanks.
Hi George,
Yes, unfortunately the VB Editor in Excel 2016 for MAC is lacking in tools. There is no way to really debug your macros or develop in that environment. Your best bet for Mac at this time is the 2011 version of Excel. Hopefully that will change in the future. I wish I had better news…
Unfortunately, I do not seem to be able the second and third video of this series. I am just trying to learn Macros and VBA and do not have a task that I would like to automated, so I would take any task that you have on the second and third video. Thank you.
Regards,
Ninous
Hi Ninous,
I will be sending the next two videos next week. Be on the lookout for an email from me then. Thanks!
A very well organized and clear video. While adept at Excel, my macro extensive macro writing was limited to Lotus 1-2-3 for DOS – many of things I wrote macros to do were built-in functions for Excel for Windows. But not all, so . . . thanks again.
Thanks Allen! 🙂
Hello Jon, great training.
I know I am going to have lots of questions as I delve deeper in the program. I am using Excel to track monthly commissions by company and comparing these to the same from the prior year. The commissions will also be entered in an income and expense workbook (separate workbook) by month and company. I’m not sure if macros or formulas is the best way to go.
Hi Lori,
That’s always a debate. A lot will depend on the process. I try to do as much as possible with formulas, and then automate the repetitive tasks with VBA. If you find yourself doing the same task over and over again, that a formula can’t solve, then it might be time for a macro. 🙂
As an inexperienced user that was one of the best explanations of how to get started that I have seen. It made some the stuff I had been reading very clear and answered a lot of questions. I look forward to the other videos. I have s spreadsheet of contracts that I use to assign the contracts to service individual. The contract has expiration dates, dollar values, and project codes that have to be updated monthly. It normally takes about 2 weeks to go through and verify the data and enter the updates. I would like to be able to turn that at around a lot quicker.
Thanks Tom! I have a feeling you will be able to turn that around A LOT quicker with VBA. 🙂
Hi Jon
Wow! I enrolled on one of Mynda’s courses and she put me on to you. Must say, your first video makes understanding the structure so simple. Great job. I’m looking to help a colleague put together an automated process that distributes an Order List to assorted buyers of vegetables, from an Excel list of veggies available. She does not want the recipients (buyers) to be able to fiddle with the list of veggies, but merely to be able to insert their name (on separate sheets?), and their required quantities. All the prices and totals are given. The challenge is then to get these lists back, and to consolidate them into one Master Order List, to submit to the veggie supplier, summarising the orders, names, etc. The process also needs to allow for any unfulfilled orders (due to stock unavailability), and to determine the revised prices for the buyers. I’d like to believe that a combination of macros, VBA, etc could do this. True?
Can’t wait for the rest of the videos.
Regards
Ernest
Thanks Ernest! It’s great to see you learning so much about Excel. It’s one awesome tool, and my favorite. 🙂
Yes, it sounds like that entire process can be automated with VBA. Pretty much anything you are doing manually can be done with VBA. That’s what makes it amazing.
Please let me know if you have any questions after the next videos. Thanks again!
You used the shortcut ALT + F11 to open the VBA editor. How do you open it from the tabs or ribbon instead of the short cut?
Great question Sheila! You can do this from the Developer tab in the ribbon. There is a Visual Basic button on the left side of the ribbon that will open the VB Editor.
If you don’t have the developer tab in the ribbon, the first video on the following page explains how to enable it.
https://www.excelcampus.com/vba/personal-macro-workbook-video-series/
I hope that helps. Let me know if you have any questions. Thanks!
Hi Jon, I am a Senior Citizen who would like to learn marcos, I already am subscriber to MY Online Training Hub and the courses are very helpful. I am proceding slowly due to time management and do not have a specfic question yet just wish to Thank You for all the videos you are providing us with.
Thanks Dennis! It’s great to hear you are staying active with learning Excel. There is always something new to learn, and that is what makes it fun for me. And VBA is a whole other world with endless possibilities for creativity. Thanks again and have a good one. 🙂
Loved your video! So easy to follow.
I would like to use VBA to refresh a pivot table upon a change to its source table. The worksheet has multiple tables that feed multiple pivot tables. Some tables have 2 or 3 pivot tables built off of them as well.
Also, I have a table that is being used as a lookup source. My problem is that a change to the table most likely takes it out of the proper sort order needed for the lookup formula in another worksheet to work. How would I use VBA to automatically sort the table after a change to that specific table?
Thanks!
Hi Jennifer,
Great question. VBA can handle all of this.
For the table sorting you can use a worksheet or workbook event to automatically run a macro when the user makes a change to the worksheet. The macro would sort the table in the proper order.
The event you use will depend on what actions the user is taking. Changing the sort order will not trigger a worksheet event, but you can use the Worksheet_Change or Worksheet_SelectionChange event to fire the macro when the user makes a change or selects a cell. I hope that gets you started. Let me know if you have questions. Thanks!