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 am looking to pull information from another system using VBA in excel. So copying a Client number from excel to search a client system for a name which would copy that back into excel. I am looking to automate the current process that requires data entry of all the numbers and copying out the search number and putting it into excel.
Hi Marty,
Sounds like an interesting project. A lot will depend on that client system and whether or not it can be accessed with VBA. I’m assuming it is either a web based or desktop application, not Excel. Let me know if you have any questions. Thanks!
Hi jon,
Excelent video.
Your us make easy the difficult thing to explain
Thanks Juan! I really appreciate that. Have a great day! 🙂
Hi Jon,
Many thanks for this video,it is very educative even if I am totally new to VBA. Do I need to upgrade my Window version from Excel 2010 to 2013 like yours?
I would like to automate ‘actual expenditure vs budget’ on multi donor funded projects for management performance report.
Thanks for now
Hi Christopher,
Great question! No, you do not need to upgrade Excel. VBA has not changed much between versions. You typically want to be on the same version as the other users in your organization. That way you can test and debug on the version that users will be using.
Most code that you write in 2010 will work in older and newer versions. The only time that doesn’t work is if you are referencing objects that did not exist in a prior version.
Please let me know if you have any other questions. Thanks!
I want to get a better all round knowledge of Excel and this involves getting to know my way around VBA as there are problems that turn up that formulae alone will not efficiently solve.
Sounds great Ron! VBA can be a great alternative to formula limitations.
Very informative , easy to follow and understand I’ve always enjoyed excel and its many uses
Thanks George! I agree, there are endless possibilities with Excel.
I just tried your first code and got error.
Sub FirstMacro()
Worksheets(“Sheet2”).Select
Range(“A3”).Select
Range(“A1”).Value = “hello”
Range(“A2”).Value = 100
Application.Workbooks(“My First Macro.xls).Worksheets(“Sheet1”).Range(“A1”).Value = “Goodbye”
The application line has an error. Using excel 2003
Hi Jules,
I am happy to see you are practicing already. Awesome! It looks like you might be missing a quotation mark after My First Macro.xls. The line should look like the following.
Application.Workbooks(“My First Macro.xls”).Worksheets(“Sheet1”).Range(“A1”).Value = “Goodbye”
Try it out and let me know. Thanks!
Your first video was wonderful. I used to some programming but it has been over 25 years and I need a refresher and this is very helpful.
I have a project for work where I need to take data from a number of workbooks (the monthly data that is submitted by each site) and add it to the master workbook that has all the sites and all the months of the year. Each site may have one or more Providers which is a worksheet in the sites workbook. the Master workbook has a worksheet for each site with all the providers and the months of the year on it. Bottom line is that this data is then added to get site stats and company stats. I hope to automate this process so I do not have to copy and paste every month.
I look forward to learning more from your site.
Thanks Karen! That is definitely a great task for a macro. All that copy and paste can get boring, plus lend itself to errors. I know from experience… 🙂 In this video series you will learn how to loop through sheets, and the same basic principle applies with opening workbooks to get copy data from them. Please let me know if you have any questions as you watch the videos. Thanks again! 🙂
Hello there,
I am Finance Guy and I initially want to automate a file where if i input the data, the results would go through to produce a balance sheet.
Hello there,
Its a good demonstration video. Easy to Understand. Very useful.
Thank you Jon for your time and the clarity of your work. The video 1 is easy to follow and to understand; and overall allows to understand the code that is used to be integrated in Excel.
I usually work with statistical data that summarize the production of a medical center, for example, clients that came usually, the type of the given service, professional or technologist that assist them, the costs of the service and the client’s classification according to the number of received attentions, among other reports.
Thanks again,
Franco
Awesome! Thanks Franco!
Do you have a tutorial about how to create an autocomplete combobox?
I have taught myself VBA over the past 6 months and I have created some I feel pretty good projects. How ever I do struggle with pulling data from the company intra-net. That would be the “Cats meow” in my programming world. I am looking forward to the future videos.
A lot of files I deal with have headers before the actual data and I would like to automate the removal of these headers.
Great question Terry. The following line of code would delete the first 2 rows on Sheet1.
Worksheets("Sheet1").Rows("1:2").EntireRow.DeleteJust be careful because you cannot undo this action.
In the next video we will learn how to reference sheets and loop through them, so you could run this on multiple sheets in a workbook. Thanks!
Hello,
I want to registration for the free mini course Macros & VBA
Thanxs
Hi Roxana,
You are all signed up. Thanks!
I have a spreadsheet that develops a dynamic pivot table based on an input list created from an input form. Specifically Church Donations by collection and service time.
I am trying to summarize the Pivot table to then post the resultant values on another worksheet in the workbook.
Hi Nick,
Pivot tables are fully supported in the Excel object model, so you will be able to reference the pivot table range and then copy the values to another sheet. Let me know if you have questions as you go through the training. Thanks!
I have a spreadsheet of 100000 random generated passwords(15 char UC,LC and numbers). I would like to write a program that not only finds duplicated, but replaces them too.
Sounds like an interesting task Brent. That can definitely be done with VBA.
Great intro video to VBA. I have a weekly process where I copy ranges from a file into an existing table. I always keep the first two rows of the table and then copy special values approx 3000 rows into that table. It’d be great to select the range onto the clipboard and then push a macro to copy into the table. Then push a 2nd macro to copy special|values|transpose into another sheet and then copy special|values into a third sheet.
Hi,
How do register
Hi Igor,
You can register by clicking the green button on the page above that says “Get the Free Videos”. I went ahead and registered you, so you don’t need to do anything. Thanks again!
Frederick Chidester Sr
I am hoping that I can play these videos’ over and over again. Reason, unlike a number years ago, I now have only 10% vision in one eye.
What am looking to do? We have a Veterans’ Blind rowing team. I make calls to the guys to line them with transportation to weekly training sessions. I also bring blank workouts to have their workouts recorded.
I bring these sheets home and with a reader read the entries and then record them in a worksheet for each member.
What I would like to automate is the printing of a copy of the call sheet, the over-all attendance sheet, and a blank workout sheet for each member who is scheduled to attend.
If a team member is not coming, then no sheet needs to printed.
There is more, but for now that is a start.
Also a big thanks to Debra Dalgleish for telling us about this course, and to you for taking the time to put it together.
Am I signed up now?
Fred C.
Hi Fred,
Yes, you can play the videos as many times as you like. Just bookmark the link to this page and comeback whenever you want.
That sounds like a very interesting project that can definitely be automated with VBA. You are now signed up for the training and should be receiving an email from me.
Thank Debra for me too. It’s great to have an enthusiastic group here. Thanks again and have fun rowing! 🙂
THANK YOU JON!
I have been trying to self-teach these concepts and have not been very successful. I have a programming background, but have been out of that mode for quite awhile. Now I’m back to using spreadsheets and want to use VBA to make my life easier.
I currently run several processes over our database & extract to excel. Then I have to summarize the data to get counts of gains, losses, inactives, automatic-to-willcall, willcall-to-automatic. I’m doing this using pivot tables, but each process is done manually. Then I have to update the final spreadsheet with the counts for each service center & roll up to regions & company totals. It takes all day to run this currently. I would LOVE to automate using VBA.
Looking forward to learning more!
Hi Sharon,
That is a great process to automate with VBA. I’m happy to hear you are using pivot tables. The object model supports pivot tables and you can program just about every aspect of a pivot table with VBA. This is great for automating reports. I’m guessing you will be able to turn that all day task into a matter of minutes. 🙂
Thanks for this opportunity to finally get acquainted with VBA’s and Macros. I am currently maintaining a workbook containing several worksheets of stock trading data. The worksheets are updated using downloaded data from Yahoo Finance website (closing prices), and various data from my brokerage trading account. My daily trading activity is copy/pasted into appropriate worksheets (based on type of trade) by duplicating the prior row (several columns have formulas) and copy/pasting today’s unique trade information into the appropriate column. Would like to be able to step through my day’s transactions and auto update the appropriate worksheets and/or add rows to the appropriate worksheets. As well as overlay downloaded closing price info in the proper worksheets.
Thanks Jerry! That definitely sounds like a task that VBA can handle, and it will save you a lot of time. Let me know if you have questions as you go through the training.
I have retired now but have always been interested in VBA and Macro writing but never had the time to spare, now I have
As I am not always going to be using the facility once learnt i will need to leave a lot of notes about why I am doing certain tasks in the Macro.
My background is in the Building Industry with spreadsheets for Progress Claims and Bills of Quantities, other tasks may also present themselves
Hi Barry,
Great question! To leave notes in the macro you can start the line with an apostrophe. This is called a comment in VBA. It will turn the text green and that line will not be read my the VB Editor when the macro is run. The following is an example.
'This line of code is a comment. It stars with an apostrophe.
I talk more about this in the video #2, which will be available next week. Let me know if you have any questions. Thanks!
Please, I really need this lesson but with my very poor spot of getting a video via internet – do you send CD?
Thank you I just want to know.
Hi Tabuaka,
Sorry, I cannot send a CD. I added a link under the video that says Alternate Video Player. You might want to try that link as well. Thanks!
Hi,
Its tremendous knowledgeable video. I already have most of my task automated but want to know about user form.
Thanks Tashfeen! We discuss userforms in video #3. I will be sending it next week. Thanks again!
Hi Jon, great video, thank you.
I’d like to automate sending reports to sales reps. I’ve got a report in excel ofcourse, and want to send it via outlook. The problem is that when I try to do it – I got a message box in outlook, telling me that other program is trying to send a message via outlook. I Have to clisk “allow” to move on. IS there any method to avoid this message box?
Cheers
Malina
Hi Malina,
Great question. I believe that is a security setting in Outlook that you can turn off, if you don’t have any antivirus program running. It also depends on which version of Outlook you have. Checkout this article for more details. http://www.msoutlook.info/question/883
Thanks!
Jon – thanks a lot 🙂
Hi,
Thanks for the video’s…
Wondering if there is specific help available (books,links) that would help transition from vba 2007 to vba 2013/16.
specifically…
user forms that are open when opening new books then close when closing books.
user forms that lose focus behind newly opened books.
etc…
thanks in advance
peter
Hi Peter,
Great question! VBA and the VB Editor have not changed much at all between 2007 and 2013/2106. However, the application did change from a Multiple Document Interface (MDI) in 2007/2010 to a Single Document Interface (SDI) in 2013/2016. This can cause issues with userforms as you mentioned. Here is an article to an MSDN help page on programming for the SDI. Jan Karel Pieterse also has a great series of articles on how to keep userforms on top of the SDI. That should help get you started. Let me know if you have any questions.
Thanks!
Hi Jon, Awesome learning video with a very logical flow and great pace! You have a soothing voice that is easy to listen to and an enjoyable teaching style which is a refreshing change from many of the other online video tutors I’ve encountered on the web.
I would like to automate a data analysis process that sums the total time spent on a variety of tasks each day (some repetitive/others unique.) Data is entered as follows: One task per row with column headers for Task Type, Date and Time Spent. Criteria for summarizing is to sum the time spent on all tasks ‘by date performed’. The macro would need to go down row by row using the current ‘date’ cell to compare to the date cell on the next row BELOW to see if it matches.
If the two dates match, the time for the task is rolled up to be included in the total sum for that date.
When it finds a non-matching date below the current cell, it should start a new total related to the new ‘non-matching’ date below the current cell. It would reset the sum total to zero and start calculating a new ‘total time’ sum by repeating the row by row tallying based on date.
On a different note….I was wondering if you offer a text transcript or overview summary of your lessons? The reason I ask is that I don’t find video learning to be the best medium when trying to learn and retain new concepts/skills.
I find teaching videos are not helpful as a quick reference guide down the road, when I’m looking for one tiny, forgotten knowledge nugget.
It seems like I am forever either backing up or fast forwarding, second by second, minute by minute, to try and find the key forgotten ‘how to’ that I’m looking for. I find it VERY tedious and prefer a text format for future reference.
I’m really looking forward to your next lesson and hope I was successfully registered.
Hi Karen,
Thanks for the great feedback. That sounds like a task that you could accomplish with formulas or a pivot table. You could automate it with VBA as well. There are a lot of different ways to solve problems with Excel. I don’t have any reference guides for the videos yet, but that is a great suggestion. Thanks again!
Thanks for your FREE videos,
I am running an Institute. I daily conduct “Test-Excel” to the students. Everybody will login to their respective systems and opens the Excel File. They will start attempting the questions provided in that file. In the sheet2 Results will be shown automatically because i have written a formula but this sheet is hidden.
Coming to the point! Now there 20 students and everyone took the test. 20 excel files are there. I have to open each file and unhide the second sheet then take the score, enter the score number in a newsheet to analyse. I want to anutomate this activity. with 1 button i have to get everyone’s score in a news sheet?
Hi Jon,
Thank you for your free video. It is very informative and easy to understand. Hope to see more and be able to write macro in the future.
Thanks Jane! 🙂
Hello Jon
How are you doing.
Honestly I tried myself to learn Excel VBA, I found it interesting in first place, but after a while I realized there’s something missing, an expert help, to learn Excel VBA “step by step”.
Then Ms. Debra, sent this mail and mentioned about your free Excel VBA course, which I haven’t started as yet.
Is there any way to learn within a month, so that I integrate and automate my excel day to day tasks with ease.
Regards,
Hi Khurrum,
Thank you for the great feedback. I do have a full online course that goes into learning VBA in depth. The course will be open for enrollment in a few weeks. Honestly, learning VBA in a month is pretty fast. You should be able to learn the core concepts in that amount of time, and the more you practice the faster you will learn. Please thank Debra for me, and let me know if you have any questions. Thanks again!
Great, easy and clarifies s a lot for a beginner
Hi, I need help in writing a code that can translate content of a range of cells from Arabic to English
Appreciate your help and thank you in advance.
I enjoyed it very much. at the moment I have no special need. but it is very interesting, ant in the future it will be useful.
Hi John
I am the EXACT example of Andy.. his passion for Excel is also my passion for Excel. I LOVE working on things like what he has to. I taught myself how to do a simple macro at a job I recently lost. I really like how you are explaining the Product & method information. I can’t wait for the next video. I do want to learn how to do high level macro work. this is my goal is to learn VBA and be able to create macros for things.
Hi Jon,
Great video. I am already looking forward to the next video. A couple of guys at work are quite good with this stuff already so i guess i am just catching up. Though they create most of their code via recording so it is nice to learn how to write the thing manually. Thanks for the easy introduction, i have managed to write my own notes and will practice over the weekend before receiving the 2nd video. Be nice to know what all the other functions happily do – but i shall wait for the other videos to find out!
Hi Ben,
I’m happy to hear you are learning VBA. The macro recorder is a fantastic tool, however it is great to understand the core concepts of programming VBA. The recorder can only go so far with automating a task. For example, it does not create Loops which you will learn in the next video. Feel free to forward this along to your co-workers as well. Please let me know if you have any questions. Thanks again!
Hi Jon,
I want to write a macro to email an attachment, either a sheet or workbook. I can bring in the changing email address to the sheet, as the manager names change with each data set. This sheet will be a sales summary, etc.
Thanks,
Dave
Jon,
I am a retired part-time consultant who is in the process of completing a book on the basics of compensation management. It will also include a set of workbooks that my partner and I have developed. We have had someone convert most of them to VBA and i want to learn enough about VBA to be able to handle any problems that might arise from the user.
It’s a great chance to learn
Hi, I’m 72, retired, but still partially active in business. I enjoyed your 1st lesson very much and have subscribed to the next lessons.
I make a fairly intensive use of Excel (2011 for MAC) and right now I am preparing some cost calculation files in Excel for some business friends of mine .
To make their work easy, I wish to prepare UserForms that can be filled with the calculations data by means of text-, combo. and listboxes to enable the selection of the parameters that come into play for the cost/price calculation.
I want to click a button and transfer the entered data from UserForm to the actual calculation worksheet.
I have formulas in this worksheet that calculate the costs and give a price as result.
The key parameters of the calculation and the resulting price are summarized in a row of 10 cells, that represents an Offer Line.
I want the Offer line copied in the UserForm to let the user read the parameters and resulting price and amend the calculation data if needed.
I want to click a button so that the Offer Line is transferred to a worksheet ” Offer Form”, in the first free row.
I want to repeat this process for all the “n” Offer Lines that are required by the client.
Once all the requested Lines are listed in the Offer Form, I want to add the Sales Conditions that come from another dedicated worksheet.
When the Offer is completed (heading, Offer Lines and sales Conditions), I wish to click a button to have it saved as PDF in a dedicated folder in my MAC.
Ideally I would like to have the PDF sent to the client by means of the e-mail program (Outlook? Mail?).
The UserForm and relevant calculation and offer worksheet should be emptied after saving of Offer in PDF format, ready for the next offer calculation.
I hope to learn how to do most of the above from your lessons, the rest to be picked up in the VBA forum in the net and from books.
Many thanks in advance for your help and my best regards from Italy.
Giorgio
Compare two worksheets
Your use of terminology was great. I’ve tried to write VBA many times without success unless using the record macro function which has limited my capabilities. Summed up I never leant what objects, properties and methods were / are: I look forward to Parts 2&3.
Thanks Tony! I’m really happy to hear you learned from the video. You are right that the macro recorder is great, but has limited capabilities when automating a process.
Hi Joh,
I recently became responsible for the accounts of a club with many reporting requirements. Currently information is repetitively rekeyed into various excel & work documents. ie: Same number keyed 4 or 5 times. I want to write a macro that identifies the report, position of the info via lookups (date / period / time) and extract all the information into a separate worksheet.
One step forward
The first video is very good and an easy way to learn macros. The way of teaching is also very good and easily unerstandable. Thanks for the efforts and help.
God may bless you for this effort pls
Thanks Abdul! 🙂
Would like to know how to use “Index or Lookup” functions when the tables are in an external hidden (not visible) workbook from the active workbook. Having a form pull data from external workbook.
Enjoying your class.
Awesome video…soothing voice. Video explains the process in great detail. I have a better understanding of application, methods, and properties. Loved the banana example. I love Excel, and all that it offers. My life could be so much easier if I knew how to write macros that will not only work in the current workbook but in others as well. As I continue learning VBA, I am sure how to write this will become obvious. Thanks again for the free lessons.
Great video,I can’t wait to start writing VBA. Just love it. Thanks so much.
Thank you Justice! I’m happy to hear you are excited about VBA. It is a great feeling when you write and run your first macro. I got addicted. 😉
Hi Jon
Greetings from São Paulo Brazil, congratulations for the excellent video. So well done, you did it looking from the point of view of someone who knows excel but always wanted to learn vba.
Currently I am trying to develop a macro to automate a excel dashboard based on several pivot tables.
Now I have to refresh them every 15 min. But I would like to be able to create a vba to automate this task.
Jon I will watch the other videos of yours expecting to learn how to do it.
Thank you
Best regards
João.
Thanks Joao! Glad you could join us from Brazil. It is definitely possible to refresh the pivot tables with VBA. You can use the Application.Wait method along with a loop to have the macro wait a specific amount of time before refreshing again.
Nice video!
As I am programming for some years n VBA, also for me some things are described very clearly, and for me to use as a reference.
Thanks Jon
Thanks Jon Acampora for your Video. I really appreciate it. I want to learn more from your other video. Please provide me.
Rgds
Ahsan
hi Jon I enjoyed your video. There are tons of things that we do step by step at the office and i want to mechanise them. From presence sheets to forms that include name (copy paste 3 times) and likewise info. I want to have a macro that asks for your employee number and from that number all info pops up in an excel sheet, then the type of “action” and fill up dates and times. I used to program in FORTAN II and IV so the sequences are not difficult to follow but mostly the locations in the excel sheet and end up with an F8 sending the sheet to the printer so that it can be signed. look forward to keep learning the procedures. Thks for sharing your knowledge