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!
Hi: Your VBA intro was really helpful, and I am less intimidated by the process.
I am interested in: using source data from downloaded file saved in excel format, saving that data into a workbook that already exists (import data?), adding a category column to identify transactions by category using multiple methods to capture the types of categories….data within the source data table where 1) period of performance inputs are not blank 2) using account number from table to categorize some data, 3) using a table or name range of category options to assign a category to transactions based upon “purchase order” number, etc., by vendor etc. 4) then summarizing categories, and adding future values for transactions with POP values for further analysis and reporting. – something like that. I haven’t worked through all of the details, but being able to automate a monthly review process which will provide YTD data for month, quarter, & year end reporting purposes is what I am after.
Thank you Cynthia! I’m happy to hear that VBA is a little less intimidating for you now. I know it was intimidating for me when I first started learning it too.
That is a very common reporting process that VBA can definitely help with. You might also want to look at Power Query as a way to help with the data import and manipulation. The combination of VBA and Power Query can make for some really powerful and efficient automation systems.
Jon,
I was very impressed with this first video as I now feel I have the beginning skills on how to write macros and work with VBA. I know these skills will come in handy as I keep learning more from your other videos.
Many thanks for making these videos free. It’s very appreciated.
All the best.
Arlene 🙂
Thank you Arlene! I really appreciate the positive feedback. 🙂
It’s a great video training session. It’s very easy to understand for beginners to start learning VBA macro.
Thanks Brian!
Good introduction to VBA, Jon!
There are 2 things that I use VBA for more than any others and would love to improve my coding skills:
1. Read text files (either delimited or fixed length fields) in, manipulate the data, and create spreadsheets;
2. Open 2 different workbooks (with common key fields) and for every row of a sheet in workbook 1, use VLOOKUP to pull in values from the second workbook, creating a 3rd workbook.
I have working solutions for both scenarios, but am sure that my code could be improved upon.
Thanks, Jon. I have been hunting for some decent training on Macro writing that doesn’t just use the Record Macro command. I tried VBA for Dummies, but got bogged down. This looks perfect. I look forward to the next lesson.
Awesome! Thanks Ken! 🙂
Hi Jon, thank you for the first video… leaves you wanting to know more! I am interested to see how you link the coding for the MsgBox to the Hello Message on your Cover tab.
Hi Dillon,
Thank you for the comment! Great question. I’m planning on sharing a bonus video that covers different ways to run your macros, including buttons in the spreadsheet. I will send it to you in the next week or two. Sorry if I’ve left you wanting more again… 🙂
Looking forward to the other videos.
I am a support technician, and I need to be ready to support staff, who may have received a worksheet with macros or need one done. I have done some specialize macros by hacking at it, and using the internet. Now I wan to build a good understanding.
Thanks Evan! I think you are in the right place to learn and master VBA. 😉 It definitely takes practice, but I’m here to help support you on your journey to learn VBA.
I can’t wait to see Video #2. I have a workbook with transactions for each month and would like to summarize it without all of the headaches.
That’s great Anita! Video #2 will allow you to accomplish that task in a few seconds. 🙂
Hi Jon,
Great introduction!!! .. i am interested in interogating a SQL dbase from Excel and extracting a summary of large amounts of data into excel report.
Dave
Hi Dave,
VBA can definitely help with the SQL integration. You might also want to look at Power Query for the importing and data manipulation process.
I work with QuickBooks (QB) a accounting package and unfortunately when you export from QB it gives a lot of extraneous lines. For example a total line after each transaction. I would like to learn how to id these extraneous lines and delete them for starters.
Thanks David! VBA can help with that clean up work. Sometimes you can also use the Filters built into Excel to help identify and remove the lines. Power Query is another option as well. As you can see, there are a lot of options.
I think this would be a great topic for a video or blog post. Would you be willing to send me an example of the file? You can randomize or change the data if needed. I just need the basic structure. You can send it to [email protected]. Thanks!
Thanks for a great intro video –
I plan to automate the creation of a workbook index (sounds like the 2nd video) and
also to augment / improve the automation done for our Problem Resolution incident analysis.
Thanks Harry! The 2nd video should definitely help with that.
El acceso a temas de esta indole, no son muy fáciles, dados los costos del dolar en nuestro país impiden tener esta posibilidad de forma mas sencilla. el conocimiento se hace necesario aunque el idioma no es la barrera.
Mil Gracias.
I want to automatically total numbers by name:
Joe 100 to Joe 600
Joe 200 Sam 350
Joe 300 Jeff 100
Sam 50
Sam 300
Jeff 100
Hi Todd,
I’m not sure I fully understand your question. You can send your file to [email protected] if you’d like. I’d be happy to take a look. Sounds like you might be able to use a SUMIF or SUMIFS formula, depending on how the data is structured.
This is exciting, I do repetitive copy and paste document prep every month. I would LOVE to learn how to automate parts of the process!
Looking forward to parts 2 & 3.
Hi Katie,
That’s probably one of the most common and time consuming tasks to automate with VBA. I have a blog post with a series of videos that shows different ways to copy and paste with VBA. Check it out and let me know if you have any questions.
Thanks!
Thanks Jon,
Short and sweet introduction – The way it should be but still enough detail to understand the process.
I’m in Project Planning and can see so many places to introduce VBA to make life easier – especially at reporting time when one needs to bring all data required for updating into one central place. Would love to read data from external sources like an Oracle data base to get related planning data to add to the “Update Data Workbook”
Hi Abraham,
Thanks for the comment! VBA can definitely help with that. Power Query is another tool that makes the data import process easy. The combination of VBA and Power Query can create some very efficient automated systems.
Been using macros awhile but never trained formally. It’s been a frustrating and inefficient exercise even though I have had successes here and there. This short video has helped to explain rudimentary concepts I’ve been missing.
Awesome! Thanks for letting me know Trish!
Hi Jon,
Thanks for the great introduction, the video was very clearly presented and was really helpful – much better to watch it than read a book! I’ve used the macro recorder a few years ago, but that’s it. It would be great to be able to code it directly.
You asked what I’d like to use VBA for – well I tend to use formulae a lot, so I expect I’ll watch your other videos and get lots of inspiration for ways that I could use VBA that I hadn’t even considered…
I’m looking forward to your next videos, keep up the good work!
Thanks Sarah! I’m so happy to hear that the video helped you. We dive into some real world examples in the next videos that should hopefully help you automate some of your own tasks.
Outstanding introduction. Looking forward to the next videos.
I am looking for VBA for when my order file is opened it will date, increase the the quote number and save file as xlsx file. My problem at present is when I reopen an existing file it re-numbers and changes the date.
Hi John,
You might want to use an IF statement to check if the file has already been stamped. There are few ways to go about this. Let’s say cell A3 contains the date. We will also call your original order file a “template”. This is the file you use to create new orders. You might want to add a cell to the template that flags when the date stamp macro has run. Let’s say this is cell A4. You could hide this row so the user doesn’t see it.
When the date stamp macros runs, add a line of code that adds the value of TRUE to cell A4.
Range("A4").Value = TRUEYou will also want to add an If statement to your date stamp macro that checks if the value in cell A4 is TRUE. If it is true then do NOT run the code to date stamp the file. Something like the following.
The first line of code above test if the value in cell A4 is NOT (<>) True.
I hope that helps. Please let me know if you have questions. Thanks!
Thanks Jon for sharing your knowledge for free.
I am a big fan of Macros but I do all my ones by recording them and did not know exactly what all the codes meant.
Look forward to the next lessons.
Awesome! Thanks Chris!
Great intro. I used VBA quite a bit 5 or so years ago and haven’t touched it since. I am really looking forward to using this course as a refresher. Thanks.
This is the first time I’ve ever seen or read anything that explains VBA in such a way that is easy to understand – either Excel or Access. Finally!
I would like to know how to automate extracting data from an external DB (eg. SQL Server) with or without Power Query.
Jon,
Your explanations are clear and I really liked the analogies you used. I found the first video very helpful.
Thank you
Cheryle
Hello Jon,
Are these tutorial available other than Youtube?
Hi Mudassir,
Not right now, but I will post them through another medium. I realize not everyone has access to YouTube. Thanks!
Thank you for this easy way, hope all will be in such way
Hello Jon
great simple to understand videos.look forward to more videos
regards
rajan
I’d like to automatically duplicate a worksheet based on the # of parts that I need to quote. Currently my work book has an information / description page followed by 4 specification sheets with formulas and links back to cells on the information page. If there are more than 4 items to quote I have to add a sheet / copy the cells and update the formulas & links in the new sheet. Is there a way to automatically add a sheet with the links pointing to the correct cells using a macro?
Hi Ed,
Yes that can definitely be done with a macro. Everything from duplicating the sheet to updating the formulas can be done with a macro, and you will be able to add the new sheet(s) with a click of a button.
Good primer to VBA with basic knowledge – especially good on the references and what happens if they are not declared.
I would like to make a macro pause in order to manually compare data side by side and insert rows in that data where needed (which will vary each time), and then I would like the macro to resume its final subroutine to finish.
Hi Trish,
There are a few ways to go about this. One approach is to have a userform appear after the first macro runs. Then have the user press a button on the userform to run the second macro. I explain how to create userforms in the 3rd video of the series, which I will be sending next week. Thanks!
Really nice way to get into VBA which I have been wanting to do for sometime.
A common process we try to automate is where we create a report which is either in a pivot table or has dropdown options. We may then want to create an individual report for each branch or customer which can currently only be done by selecting the dropdown or filtering the pivot. This could then be sent out as a pdf to a designated mail list for each branch report.
I would really like to be able to do this and understand the process behind building it in steps.
Thanks Darren! That is definitely a common process, and I explain more about it in the 3rd video of the series where you will learn how to export sheets to individual files. You can use this same basic process to export PDFs as well. You might also be interested in the “Show Report Filter Pages” feature of pivot tables. It is a built-in feature that allows you to create a sheet for each item in the field in the Filters area of your pivot table.
I work with a lot of data but I may have several worksheets and the categories may not all agree. I’m looking for an easy way to summarize data from various worksheets into one summary page so my managers do not have to work with several different reports or several different pivot tables.
I’m looking for ways to simplify financial summary reports where the content comes from several sources.
Hi Lyn,
I think the 2nd video will help with that. I will be sending it next week. Thanks!
Very good intro video. I’ve recorded very simple macros and now have a better understanding of the code. I would like to automate some repetitive monthly tasks where data is added to a file and then reports have to be manually updated with the new data. Would also like to be able to write macros to format pivot tables. Thanks
Thank you! Jon. I’m interested in table driven approaches for names and UI. How to set up a macro that reads from the table? Also intrested in a simple approach to store worksheet data in a database.
Thanks for helping me any further.
Thanks Jan! VBA and Excel Tables work really well together. I have an entire module of lessons dedicated to this topic in my VBA Pro Course. There are two ways to refer to Tables in VBA. They are referred to as ListObjects in VBA and a member of the Worksheet. You can also reference the Table using structured referencing. Range(“Table1[Column1]”).Select would select that column in the Table. That is just a very brief explanation, but I’m a big fan of using Tables.
Thank you, Jon.
It was a well-delivered presentation.
I will have to play it a few times to get all of it and I look forward to your next video.
Thanks again.
Chris
Thanks Chris!
I would like to rearrange data, and apply consistent formatting to that data, which is imported from a database every month; the number of records changes each month.
This is the first time I was interestingly learn the subject,
before VBA It was to me like solving like a Maths Problem.
Your way of teaching is excellent…!
Looking for a next video Its not playing can you assist me.
Thanks Jalal! I appreciate the positive feedback. 🙂
Hi Jon,
Thank you for this great video. I would like to automatically (through VBA) pull data information from MS-Project to Excel.
Regards.
This first course was really what I needed, looking at all the amazing things VBA can do was overwhelming, with this first course the basics started making the slightly more complicated scripts easier to breakdown, I look forward to part II & III.
As for my pet project I’m trying to take a flat file that reserves a dozen laptops that amazingly has people overwriting others entries and basically causing mayhem. The plan use one sheet for data entry and a second for “behind the scenes” reserving of machine and storing data. So sheet1 the user enters data then presses a button & it searches on 2 criteria to find an available machine and return the machine name to the submitter. Then when the machine is returned I need to update a secondary workbook with the info from the reservation workbook and then make the asset available again. If possible make a counter for how many time the pool of laptops is exhausted based on those two search criteria.
Seems simple as text but do I ever get confused trying to do it.
Was so excited to receive this training video as we recently just started using the “Userform” option in Excel. Thank you!
Want to be able to subtotal and total sheets and combine information in summaries
Great intro…thank you for this free video
Hi Jon
Thanks for a great introduction to VBA. I last used basic macros a few years ago and have since been using various Excel functions to sort my info out. I have also recently looked at possibly using more advanced Pivot Table options. These each appear to be longer way round options compared to what should be a more straightforward approach with VBA.
I’m looking specifically at sorting a list of attendees to a membership event by various criteria (male/female, location, attendance days, age ranges etc.) and then putting together reports for each location group. But, the general use of Excel macros/VBA (are they just different approaches to the same answer?) for basic repetitive formatting would also be helpful knowledge.
Thanks for such a straightforward style of presentation that was very easy to understand and follow.
Hi Kevin,
I think there is almost always more than one way to solve a problem in Excel. It sounds like you could definitely use VBA for that project. Pivot tables might be a good solution as well. A lot depends on the original format of the data, and what reports you want to create.
Thanks for sharing the Macro!
Great video!
The cartoons in-between is a nice touch. Easy to follow and great introduction to Macros.
Good program want to set up macros for data conversions in excel and many other functions.
Great training approach!
Hi Jon,
Thank you for the first macro video. I want an excel macro for my Bank Reconciliation and GL analysis import I do to excel that becomes sometimes very laborious.
Excellent introduction, thank you!
Explaining how the lines of code are structured, read and executed by Excel provides a really sound base on which to build VBA script.
I’d like to code just about any repetitive task (e.g. parsing out extensions from values like filenames and trading symbols/tickers, etc. based on separation values).
Thanks again, I look forward to the next installment in the series.
I write VBA every day, and I thought the first video was good. in fact, I would recommend it to some of my colleagues, but I couldn’t get to the 2nd and 3rd video. There was part of a screen that showed 50% done at the top, but there were no scroll bars to get to the bottom to fill out the form. Please send me URLs to the them, as I’d be interested in how you present programming objects.