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 created a very complex workbook that works as a grade book and transcript tracking for students. The file is duplicated and renamed for each individual student. Each teacher would have about 30 of these files, one for each assigned student.
I would love to learn how to create a workbook that would do data updates to each individually named workbook when there is a bug or updated data.
Hi Jason,
That’s a great task to automate. In my VBA Pro Course I have an entire module of lessons dedicated to learning how to create an application that automates processes on a set of files.
You can create files from a “template” file or run any procedure/macro on the set of files. I think it would work really well for your project.
I call this tool the File Manager, and have used it numerous times throughout my career to automate processes with multiple files.
In module 11 of the course I start from scratch and walk through how to create the File Manager step-by-step. I will be sending more info on the course this week.
Very Nice video. I hope all the others will be good!
Thank you so much simple explanations are the best; it makes learning new processes less intimidating. Looking forward to seeing the other videos.
enjoyed the video, I have a lot to learn and this is helpful. One of the first things (and certainly one of the best things) I learned about Excel is that it is best to write out what you want to do in text/sample tables before starting to work in Excel. In this vein, it would have helped me if you wrote out or typed out the objective/activity in detail and showed that prior to jumping into writing code for VBA. Hope this helps, Larry
Thanks Larry! That is a great suggestion and I will consider it for future videos.
I have used VBA before, but as you rightly pointed out, I have adapted existing code with things I think are right, but without the true knowledge of what and why. I look forward to the rest of the series to see just what I don’t know, and how I can better utilize this part of the product.
i would really like to learn how to take a list from excel, 500 rows plus, and merge with a word template, to send direct to a printer, using leterhead on page 1 and blank pages for pages 2 and sometimes 3.
Thanks Peter! VBA is like a whole new world within Excel and Office (Word, PowerPoint). It can really save you a lot of time.
I believe the process you are describing can all be done with VBA. Word has a VBA object model of its own and you can reference it in your Excel VBA code as well.
Hi
I want to automate having a drop down list that I can add “members” to and that I can autofill — So I want to select “johnson, Bob”… I start typing “Jo”… I move in the list to the member that start with “Jo”, such as “Jones”. No “Johnson, Bob”. Is there a place to add Johnson bob easily?
I also want to be able to generate from a list of teachers, class, students, a spreadsheet for each teacher class combination with specific fields. Best would be if it would generate a separate workbook for each… Even better would be if I could move that info to Word, which is more familiar to my teachers.
The 1st video is very clear.. I was disappointed that I will need to wait for the next 2.
Hi Carol,
The search feature you are referring to is a great idea. I have actually created something like that, that I use in my add-ins. You can see it in my PivotPal and Tab Hound add-ins.
I also teach exactly how to create that in my VBA Pro Course. I will be sending more info on the course this week.
Sorry I made you wait for the 2nd video. 🙂
Thanks John for this info. i didn’t have long to wait for the 2nd video. Enjoyed the series very much and will be using what I learned in building my application.
I have been wanting to get into VBA for a long time. Books I have tried just seem to put me off (and to sleep in some cases). I find this much better and easy to follow. Thanks Jon.
Thanks Keith! I’m glad my videos didn’t put you to sleep. j/k 🙂
Great lesson plan Jon.
I am looking forward to learning more.
Jon,
Thank you so much for this video. I’m the “Andy” of my office; looking forward to more videos in this series.
I do have a program that I’d like to develop. It involves a user providing a list of items that they want to compare which among them is the most desired. This comparison would take a unique pair of items on the list and ask, in a dialogue box with buttons, which of the two is more desired. Excel would store the data, and at the conclusion of asking all permutations of pair-wise comparisons create a list of most desired to least desired.
I recognize the limitations of trying to describe; hope to learn from your course how I might begin constructing the framework. (I’ve programmed in Java in a prior life – learning the syntax of VBA is my opportunity!)
Thanks,
Mark
Thanks Mark! Andy is kind of the story of my own personal journey as well. I think we all face these challenges when learning Excel & VBA.
You process sounds like it is definitely possible with VBA. I look forward to seeing what you create.
Thanks again!
Hi Jon,
Awesome video! It was very clear and easy to understand, so I’m really excited for the next two videos.
Lately, I’ve begun being tasked with creating various excel spreadsheets with instructions to “just make it do this”. Most of these requests however are impossible without VBA and while I’ve been able to google code for some processes online, I usually end up with some extra unnecessary lines of code which oftentimes comes back to bite me.
With these courses I hope to not only be able to understand this code better, but even go so far as to write my own code to accomplish these tasks.
The most current task is to have five of the same spreadsheet for five different companies and then have one “master” sheet which would automatically update with all the data entered into all of the individual sheets. Usually it would mean entering a row of data, but sometimes I’d need to delete a row as it is deleted from the company spreadsheet. (I imagine this is doable somehow with VBA. Correct?)
The second issue has to do with he company sheet I just mentioned. Once a date on any of the rows of data “expire” (more than 90 days pass since from that admission date) I need that line to delete itself from that sheet and consequently from the master sheet as well. (Perhaps I’d have it be entered in a third sheet for record keepings sake of all historical data.)
Can this be done?
Looking forward to the next videos!
David
Thanks Dave! I’m glad you enjoyed the video.
To answer you questions… Yes! It can all be done with VBA. In my VBA Pro Course I have an entire module on a VBA application I developed called the File Manager. This tool allows you to open multiple workbooks and perform any macro (automation) on a set of files.
This tool would help with these types of processes, and can easily be customized. In the course I teach how to create the tool from scratch, so you can use it for your own projects.
Either way I think you can save a lot of time with your project using VBA. I will send you more info on the course this week.
Thanks again!
Impressed by my first (quick) look at the video and looking forward to a more thorough look when I get time.
As for what do I want to automate: well I do a LOT of work with Excel (2007, unfortunately, as my employers have yet to upgrade), but will really need to get a grip of what is achievable before I decide where to start on automation.
A couple of thoughts for you, Jon, if you would care to consider these:
I, and I suspect others among your readers, am hearing-impaired and audio quality on YouTube videos can be patchy. Subtitles would help us immensely (not to mention our partners who have to put up with listening to high-volume audio) if you have access to the necessary technology.
Secondly I am not of the video generation and actually learn better by means of the dreaded PowerPoint! I appreciate it would be an extra task for you but it is something you might want to consider.
Thanks in advance
Hi Jim,
Thanks for the suggestions. I will look into how I can make the subtitles better. All of the animations and visuals in the video were actually created in PowerPoint. I try to mix between that and screencast video of me working in Excel.
Thanks again!
thank you for the video. I hope to gain a lot in exel
Hi Jon
Loved the first video, at last someone who is explaining the VBA editor in a language that I understand. I have tried to make sense of the editor for some time, as this is the way forward in Excel.
Looking forward to the next video, and learning more.
Thanks David! I really appreciate that. 🙂
Great video!
Easy to follow and great introduction to Macros.
Thank you
Hi Jon,
Great lesson .. I am interested in creating Macro for the V-lookup and the Pivot Table.
Thanks, nicely explained.
first time to learn about macro and the presentation makes it so simple for me and get more interested to be a guru in it within the shortest possible time. pls give me link to video 2 and 3 . Many thanks for the opportunity and good presentation.
The process I want to automate is the production of charts for a monthly report. The charts are in pairs, a count of occurrences for the past 12 months with a trend line, and a corresponding one of rates with an upper control limit. On the second chart the actual rates appear only when the value exceeds the limit. The person doing it spends a lot of time on these every month, as the pairs have to be produced for two different categories and each might have 5 to 20 or more chart pairs. I’d like to produce them automatically and copy them to a blank sheet, arranged as they’ll appear on the report and ready for the analyst’s review.
Hi Bill,
That sounds like a great (and time consuming) process to automate. VBA is really helpful when it comes to automating charts. As an example, my free Chart Alignment Add-in was coded entirely in VBA. There is a lot you can do with VBA to automate the creation and customization of charts.
Good job. I like to know the process of performing iterative calculation, reading a text file and provide statistical analysis and produce a desire chart. Further, I like to learn the process to find the desire corresponding value from large data sheet where inbuilt function is lugubrious.
I want to automate my monthly reporting process. I download from a reporting services type apps and need to render in excel which requires reformatting the row and column height. I then color code specific rows and also rename the worksheet tabs. There are about 6 or 7 reports with approx. 25 worksheets. I have tried to automate with macro in the past but was unsure how to recall my personal macros.
The course is very well explained and I look forward to programming tasks to enhance my productivity.
Thanks again?
Thanks LF! You can definitely do all that formatting with VBA. The macro recorder is great for learning code and getting little snippets. Hopefully this course will help you write your own macros.
Thanks again!
Hi, I’m already an advanced Excel user with VBA also.
I found your way of explaining very good, clear and easy to understand for rookies. Also, in general, your teaching style, I’ve found it really simple. Great video
Thanks Manuel! I appreciate the positive feedback. 🙂
I am about to learn VBA for Excel with the main objective to automate most of my weekly, monthly reports, dashboards or even simple Excel tasks I need to do all the time. I work for an important pharmaceutical industry (ELI LILLY BRAZIL) and my time is really important.
The boring Task i automated through excel vba is to filter a model suffix from a very big list in a pivot table.
I have filtered a model suffix off the pivot table and then linked it or filtered it using excel vba.
This is a very good beginning video. I have several projects at a local community college that I am working on which involve user forms, and processing macros. I have recorded several subs, worked from one to another and am trying to get to the point where I can automate some plots based on data gathered. Obviously, my methods and attempts are crude and cumbersome. I am interested in learning more as I go along. Thanks.
Thanks Dan! VBA is very useful for automating charts. I have a free Chart Alignment add-in that I created in VBA.
The macro recorder is great for learning about the objects, properties, and methods of charts. Learning more about VBA will probably help you code more efficiently and create more robust solutions.
Thanks again Dan!
I kind of know a little about macros.So please send me the links of videos 2 and 3.Thank you
Your teaching skills are excellent. I know very little about macros but I can readily see the value. I will have to play this several times to absorb it. I enjoyed the lesson. Thank you.
Although not a complete novice with VBA, I am impressed with your clear and thorough explanation in this video. Thank you.
Thank you Sian! 🙂
Excellent explanation of VBA code in excel
Jon,
Enjoyed the video, your pace, and voice – clear, calm, friendly.
I have two items that I thought about in the video. First, I have a few that I send out that are user forms of sorts. They replaced a cash transmittal for our Treasury office. User inputs info prints out & emails (don’t have shared folders to all remote locations yet) and sends with cash/check/credit cards for deposit. Macro takes the info and puts it into a file format for upload to GL. How do I hide the step through process that the clerk sees on their screen as the macro gets the info in the right sequence or replace it with something like “Preparing File for Upload”?
Other item is the macro is triggered by a button which seems to get disabled sometimes in emailing and security settings. Is there a way to stop that from happening?
Hi Tony,
Great questions. There are a few ways to hide the process that the macro is running. Probably the most common is to turn the screen updating off. You can add the following line of code to the beginning of the macro.
Application.ScreenUpdating = False
Then turn it back on at the end of the macro.
Application.ScreenUpdating = True
This will temporarily turn off the screen updating which means the actions that the macro is taking won’t be seen by the user. You might also want to display a hidden sheet that has a please wait… message on it before turning off the screen updating. This would give the user something to look at, and indicate that the macro is running.
The macro security settings happen at the user level. You would either need to change the end users macro security settings to enable all macros (not recommended), or store the file on a server or shared drive and have the user always allow macros for that specific location. If you are constantly emailing the files then you will need to tell the user to enable the macros when they open the file. You can also build routines or macros into the file that show a hidden sheet telling the user to enable macros before they can work with the file. This is more of an advanced solution, but does work well.
I hope that helps. Let me know if you have any other questions. Thanks!
Great presenting skills! Simple to the point, plain language, its great!!!
Video #1 was very clear. Looking forward to seeing more advanced videos. I’ve written relatively simple macros but have never used APIs.
Hi Jon,
I liked the video. Well explained and presented. I had used macros in the past by using the start recording macros option.
This video gave an insight into the back ground of a macro:the vba language. I am presently using a 2010 ms office version.I look forward to the future videos.
I want to use this feature to enhance my productivity and efficiency. Thanks
Regards,
John
Thanks very much for giving me opportunity to know about Macros programming
Yogarajah
Hi,
I sincerely appreciate the efforts put up by team in educating us in MS-Excel.
I have a query:
Can we do something to shorten the time taken to verify large no of data in excel from a pdf document?
(Say for example, I have to check whether X no of tags in a list in excel are available in a vendor document which is the source of the excel list which is needed to ensure that as the pdf document is updated, the excel list is updated as well).
Many thanks.
Saurav.
Lesson was very clear and usefull. Hope I can get the balance next week.
Thanks very much
This is an excellent video!! Very easy to follow and understand. I had used VBA for several years in the past, however, I have been away from it for almost a year and just need a refresher course. I currently create and use pivot tables to analyze data and would love to learn how to create dashboards (and include charts). Please send me the links to videos 2 and 3.
Thanks!
Crystal
Hey Jon,
I can’t wait to learn VBA and Macros from you buddy! I have left this on the back burner for WAYYYY too long.
I am looking forward to seeing the other two videos.
Cheers,
John
Thanks John! I hope these videos help get you started writing your own macros. 🙂
Great start. Looking forward to the other videos.
One task I need over and over is to select a range that may vary in length in order to perform some kind of process ie. sum the range or conditional format the range or copy a formula from one cell to a column of cells etc. Outside of VBA I can use the shift End up/down/right/left keys but from within the macro it is not as easy. I have seen the code but I am not sure I understand it well and I certainly cannot write it myself.
Hi Michael,
Great question. I actually have another article and set of videos on how to find the last used row or column on a sheet. There are several ways to do it, and a lot depends on the setup of your sheet. Please let me know if you have any questions. Thanks and have a great day! 🙂
extraction of gl number, profit center, and ytd amount from 12 months of text file p&l files (12). Then summed by gl and profit center with a detailed view.
Jon
I really appreciate the deliberate way you went through the instructions. I use VBA quite a bit, but it is always a struggle as I am constantly having to search the web or try to find an answer in a reference book. I hope, eventually, to actually understand what it is I am doing and not just know that the code I have cobbled together seems to give me the desired result.
Jacob
Thank you Jacob! I appreciate that. My journey with VBA started the same way yours did, and I spent a lot time creating macros without fully understanding it. Once I got a better grasp on the programming language, I was able to create more robust solutions and apps in a lot less time. With a lot frustration too. 🙂
I teach the object model hierarchy, and properties and methods, because I think that was a turning point for me where I really started to understand how the code works. I hope these videos help do the same for you. I also have an entire VBA course, and I will be sharing more on that in the coming weeks. Thanks again!
Loved your Video #1 … so easy to understand … can’t wait for the next one.
Thanks Margie! 🙂
HI Jon:
You are a great presenter and I look forward to seeing these courses.
Thanks Sam! I appreciate that. 🙂
I started VBA diploma some time ago and stopped due to family issues. I use Excel a lot in my work, writing formulas, linking sheets and workbooks. I’m always looking for a easier way or a better way to make my work more automated and user friendly for my clients. From payroll to job takeoffs, even drawings.
Everything I use Excel. I will keep looking at your videos. Thanks
Awesome! Thanks Gabriel! I’m happy to hear you are using Excel for everything, and automating it with VBA. I like to do that too. 😉
hi
Hello Jon Acampora,
I have enjoyed your first VBA Macro Video. It was very informative.
I would like to ask below mentioned questions for my point of view:
(1) How much time I need to invest to learn some basic steps of Macro (for newcomer?
(2) To learn entire Macro process/steps and to create expert macros; how much time it will take?
(3) Would you cover entire steps about “VBA macro creation” in next two videos?
Please provide your appropriate answer at your earliest convenience. Thank you once again for your help to beginner.
Regards,
Kaushal
Hi Kaushal,
Those are great questions. A lot will depend on how much time you spend practicing. Learning to code is not easy at first, but I recommend following these videos and trying to write your own macros. Once you experience the power of VBA and how much time it will save you, you will want to spend a lot more time learning it.
I also have a full online course called The VBA Pro Course. This course is designed to take you from beginner to advanced user by teaching in an easy step-by-step manner. I expect students can get through it in three months or less, but learning VBA is a lifetime process for me. I have been coding VBA for 10 years and am still learning new things everyday. Just like Excel, you probably won’t ever need every single thing that VBA does, so it’s best to focus on the basics at first and then learn as your projects/problems demand it.
I hope that helps. Thanks again and have a great day!
Wonderful and easy to understand video tutorial… I look forward to the next two. Thank you very much Jon.
Thank you Fay!
It is my first time even thinking about doing macros, and I didn’t even know what VBA was. So I don’t have any inkling of what I want to automate yet. I look forward to seeing the other videos. Thank you for making this free.
Thanks for a simple but great introduction to complex topic. I track 12 pairs of leveraged ETFs (24 sheets in one spreadsheet) on a daily basis and would like to copy the previous days range of info one row lower before updating the info for the new day. Thanks
Thanks Ross! That process can definitely be done with a fairly simple macro. The next few videos will discuss concepts like loops and referencing cells and ranges. Those techniques should help you with this process. Please let me know if you have any questions.
This is just what I was looking for!!!! The most common thing that I need to learn how to do is userforms and pivot charts.
Thanks Charles! We will be discussing userforms in the third video. 🙂
Thank you very much john, great video, I am just like new to VBA, I only use to work with excel and all i learnt from youtube videos. i hope that i will learn macros and vba too from you. good job keep it up. looking forward for the course you going to start.
Thanks once again,
Awesome! Thanks Jamal! 🙂
What I would really like to see is a structured and comprehensive online training course that’s soup to nuts. I understand Excel is quite extensive with a many moving parts, but I would be great if the all the video lectures could be structured that way.
Thanks Charles! My VBA Pro Course is a comprehensive course for learning VBA for Excel. I will be sharing more info on it in the coming weeks.