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 so glad, finally I got proper guide for excel. I love to learn about excel. Thank you so much sir.
Thanks Vaishali! I love to learn about Excel too. It’s my favorite 🙂
Hi Jon
Thanks for a clear and concise introduction to VBA. I currently use the Macro recorder to automate tasks , and have used the web to find lines of coding to automate tasks, such as opening the work book at the Contents page (I have a hefty workbook for entering and analysing Health and Safety data), and running a macro to produce a list of pivot tables. I have used VBA to create the data input form and would like to know how to connect the form to the database – a colleague has done this for me in the past – and, as the fields of data have changed since the database was created, I need to redo it.
Looking forward to watching the next video.
Thanks Linda! In video #3 we talk about userforms. Is the database in Excel, or another application?
It’s in Excel.
Hi Jon, Terrific teaching style. Very clear and easy to understand approach. I am looking forward to the rest of the course. Thank you for offering it.
Thanks Lucas! I really appreciate your support. 🙂
Hi Jon,
I’m just wanting to learn more about VBA and how to incorporate it in updating some reports and also just figuring out how to add to my dashboards or make data available to add in my dashboards.
Thanks,
Hi Jon
I heard about this training series to late. can I still enrol for free for this training series.
Hi Sakkie,
Yes, it looks like you are registered and everything is good. Thanks again!
Hi Jon
I watched the first video and it was a eye opener for me. I cannot wait for the next video.
In the meanwhile I will practice my skills as you demonstrate in your video.
Thanks for a very nice product.
Thank you Sakkie! I’m happy to hear you are practicing. That is the best way to learn VBA and improve your skills. 🙂
I had no idea how to write VBA code. With your clear step by step instructions, I am looking forward to it!
Awesome! I’m so happy to hear that Shari. Warning, writing VBA does get addicting… 😉
hi Jon,
I am so happy I found this site. Thank you so much for putting in a very informative video about macro and vba. I really want to learn more about excel. More power to you.
Thanks Lovelymae! I hope you learn a lot here. 🙂
Hi Jon,
Great job & great resources to learn Excel VBA
I wanted learn Excel VBA to automate my reports.
Hi Jon,
I found it very useful as I had no idea how it works properly. slowly getting the grasps of it specially that you explained it in a very simple way.
Thank you very much.
Lidi
Thanks Lidi,
It’s great to take your time with learning VBA and practice as much as you can. It will start to make more sense as you go, and also save you a lot of time in the future. 🙂
Hi Jon, I’m relatively new to excel and vba, but fascinated by what can be achieved with vba. for years now I’ve been trying to construct code that can automate the calculation of random nrs to the point where they match other, existing nrs, but then to also move on to a next set of nrs using Loop functions. do you think you could help me?
Hi Marius,
Sounds like an interesting project. I’m not sure what “nrs” is though.
Hi Jon, Thanks for replying to my message. “Nrs” is simply short for numbers. would it be possible to attach an excel file as an example?
Hi Jon
This is my very first time dabbling into Macros it seems very interesting and I can see that it will be very useful to incoporate it into my dashboards + monthly reports with specific information extracted from a dashboard.
Excel is fun and exciting venture!
Thanks Anita! Macros can definitely help save a lot of time with dashboard preparation. I agree that the endless possibilities with Excel make it super fun and exciting. 🙂
Great video, will send the link around to others who could benefit!
Thanks for sharing Louise! I appreciate your support. 🙂
Thank you for the Video! Looking forward to watching the 2nd and 3rd installment. Best vid I had every watch regarding Macro!
P.S. I also watched your videos regarding Pivot Table. The best!
Thank you Kaye! I really appreciate the nice feedback. I’m happy to hear you are learning so much about Excel. Awesome! 🙂
Great video Jon! Easy to follow, the pace is perfect and the explanations are clear. I have only used the macro recorder to create macros but I have always wanted to understand them better so I can tweak them if needed. I think I’m on the right track to learn more about that from your videos. Thank you for sharing your knowledge 🙂
I would like to be able to set up a macro that will look into securities and exchange web site go to the fund symbol
and then retrieve data so I can insert it in an Excel ss. The only problem is that for each symbol the data does not
appear on the same line. I now download data from another web site and insert the data in a Excel ss. I have data back
to 2014. The macro I am now using will retrieve the data from several ss and insert the data so I can then screen the data so that the funds that pass all the screens will show up. All I do is start the macro, sit back and watch the macro
do its thing. By the way in column A there are around 600 symbols the macro go thru to retrieve the data. I have the time set in the macro to two seconds as one of the data takes a longer time to retrieve.
I also use a macro to retrieve the money flow index for each fund.
Also I use a pay for program named XLQ for retrieving data. I invest in closed end funds so I use all this data to help
in selecting the funds I want to invest in. I have to look at each financial statement manually which is a pain so if I
could find a way to use a macro to find the data that would be a great help.
Mac
Great content and pace; it makes learning Excel easy…thanks Jon! I want to be able to select a single range that contains 100’s datasets, then run a sub routine to produce individual charts for each dataset (with complex formatting).
Thanks James! That is definitely possible with VBA. Almost all the components of a chart can be modified with VBA. It’s amazing how big the object model is… 🙂
Dear Jon,
You are an excellent teacher. There is a lot I’ve learned by watching your videos on pivot table and this introductory video.
I am hoping to apply the vba knowledge gained from your tutorials to another domain.SolidWorks CAD design software uses VBA macros to automate repetitive designs. I would love to see a tutorial on that.
Sincerely,
Chirag P Gardharia
Thanks Chirag! Unfortunately I don’t have any experience with CAD design software data. You might want to checkout the work by my friend Charlie over at EngineerExcel.com. His site is all about Excel for Engineers.
every minute copy column c of sheet1 and paste value to sheet 2 column D, 2nd time column e, 3rd time Column F….. and so on
I just registered. First video was really helpful. Right now want a macro to consolidate multiple client files into one sheet and carry out filtering on it to display results in a different sheet
Thanks Suyash! That can definitely be done with VBA, and is a very common task. I have a lesson that covers that in more detail in my VBA Pro Course. Thanks again!
Thanks Jon, Great Video!! I look forward to learning more about Macros and VBA. I would also like to find out some of the processes in recording Macros as I know some of the Excel commands to not record.
Thanks Ellen! The macro recorder is a great tool for getting snippets of code. As you’ll see in this video series, we can go beyond the macro recorder code to make our macros more efficient. The macro recorder does not create coding techniques like loops and if statements.
Really interesting!! I look forward to see the next video! I hope I can manage to apply this at my job 🙂 Thanks a lot!!!
Awesome! Thanks Christelle! 🙂
Hi Jon – I registered and enjoyed part one – but how do I get to part 2 and 3? Seems a silly question but I am having a difficult time getting there.
Hi Nanette,
The videos will be emailed to you. I believe you have already received the emails. Sorry for not getting back sooner. Please let me know if you have any other questions. Thanks! 🙂
Hi, it’s great everything I learn here and in other video. Hope you continue.
It is wonderful.
But I have one that I don’t think it can be done.
I work for a company that makes chips. In the Quality Control lab the measure the products. for moisture, salt, oil, ffa defects etc and the measure by 1.3 or .63 etc.
I did a link and tranfer because the company needs to see a monthly report, and weekly reports with min, max and averages. per week and month. It include date, time and the rest. My master file includes date, weekending date and month ending date. I have it in pivot filter. Everythings well but I have a lot of products. And I have to manipulate for weekly result.
Hi,
Can we automate alignment of data labels on the charts ?
Hi Chintu,
Great question! Yes, it is definitely possible. Checkout my Chart Alignment add-in. This add-in was written entirely with VBA, and helps you align the elements in the chart.
Hi Jon. It is a fantastic tutorial. I have been trying for ages to understand it. This process looks so simple with your explanation.
I need to learn how to write a simple macro – Add a column in a spreadsheet, add a title of the column (NAME), add a formula (=A2&” “&B2), fill the column with this formula till the last row (number of the rows is different every time) and finally remove the formula by overwriting it with the value.
Thanks Grazyna! I’m happy to hear you are learning VBA. That process can all be done with a macro. I have another video series on how to copy & paste with VBA that you might enjoy as well. Thanks again!
Hi Jon,
You have explained in very simple language its very easy to understand step by step.
please give some more examples in the videos it will be helpful
Thanks
Thanks Manjunatha!
Video was really good. Looking forward to viewing and learning more. I want to automate a process I have to do every time I reun a particular report. I have to highlight and cut the first 8 to 10 characters in a line of text and then paste it on the end of the text line. There are quite a few lines to do this process on. Can this be done? Thanks.
Hi Mitchell,
Yes, it can definitely be done with VBA. You can use the String functions like Left, Right, Mid in VBA to manipulate the string. Something like the following should work.
sText = Range("A1").Value sOutput = Mid(sText, 9, Len(sText)) & Left(sText,8)I hope that helps get you started. Thanks!
Thank You Jon…. It was very interesting and eager to learn more…
Thanks Ajay! 🙂
I’m in process of creating a “macro generator” for order entry within Dynamics GP. GP uses a non-existent syntax language (dexterity), so the work around would be to build out tables in excel or access and combine a series of concatenate, if, and, vlookup functions to auto-populate the entries needed for GP macro to properly populate the data points. Any suggestions would be greatly appreciated!!
Hi S.,
It sounds like you might be creating a database with lookup tables. In that case you might want to use Access, Power Query, and/or Power Pivot. These tools will help you create relationships between data sources for easier reporting. If that sounds like too much, then you can definitely create the same with lookup formulas. I hope that helps. Thanks!
Thank you john it is great what you are doing. i learnt a lot from you!
Awesome! Thanks for letting me know Jack!
Thanks, Jon! I use Excel a lot, and I know there is so much more to it! I found the answer to a long frustrating issue online today and during that search is where I found your tutorials. I like the visual effect and the ability to work along with the downloaded sample file.
My next long frustrating issue is this: I have to manipulate several .csv files to get into an Excel format that is compatible to read into a MicroSoft Access DB. For most of these files, there are 2-3 alphanumeric columns that I have to format to “Text”. Some of the cells may have been in exponential form, and when I convert to Text, I then have to go in and ‘touch’ each item to get the actual text form of the number instead of the exponential form. Is there an easier way to do this?
Hi Mary,
Depending on how you import the data, you can use the Text-to-Columns feature for this. That will allow you to convert the entire column to text at one time. You can also use Power Query to manipulate the data on import. I hope that helps get you started. Thanks!
Hi Jon,I just want to compare two excel sheet and get the difference in a another sheet,My data type are text not numbers.
hi Jon,
just started with your video. I have many things I would like to add to my spreadsheets. I watched you vBA copy paste video. I would like to copy A1 to G1 in a sheet that is downloaded and not saved and paste to a master sheet. I need to copy all the rows in the downloaded sheet. I can with VBA copy the first row, how to have the copy go include all the data to the last row?
Kevin
Hi Kevin,
Great question! I have another video series on how to find the last used cell in a sheet. There are a few ways to do it, and this article/video series explains when to use each. I hope that helps. Thanks!
I am extremely fortunate to visit your web page.
Hi Sir Jon! Thank you for the tips especially on copy + paste. I am currently developing a program about NBA analytics and I am hoping I can use , if not all, some of your tips. God bless you! 🙂
Thanks Henry! I’m happy to hear you are learning so much about VBA.
Dear Jon! Thanks for being awesome, your support is really helpful for many.
Thanks Ali! I appreciate that. 🙂
Thanks Jon, your presentation style is fantastic. I am wanting to learn how to generate emails from a date value – for example generate an email to remind me to arrange a present for my wife before our anniversary.
Thanks for the very informative video. I appreciate the effort you made to illustrate Excel objects in a comprehensive way.
Thanks Mahenina!
I’d like to automate the calculation number of nights spent away on business travel in order to award a fixed sum per night for the correct number of nights. I need to subtract the return date and time from the departure date and time and come up with a number of days. Travel is international and the duration is 12 hours. If traveller leaves after 06:00 a night away is indicated. for return travel there is always a night away. any help would be appreciated.
Many thanks.
Hi Mrs. H., You might want to checkout my article on how the date system works in Excel. I also have an article on how to group times, that explains more about time calculations. I hope that helps.
Thank you very much Mr. Jon for this awesome tutorial
I could only watch first video and when clicked to register my email, I didn’t receive any more …
Can you send me the other two videos?
Regards
Hi Yasser,
It looks like you might have entered a different email address to register. I went ahead and signed you up at your gmail address. You should receive emails for the other videos in a few days. Thanks!
Thanks a lot for reply.
I received the second video and in fact it is awesome. You are talented.
Waiting for the third video and wish you would continue this wonderful series
Thank you very much. Best Regards
Hi Jon,
This was a very good introduction to VBA. I am in the beginning stages but am very excited about the possibilities that VBA can help with my workload. Currently I am trying to figure out how to create a macro in a workbook that has multiple worksheets (10 I think) and 2 of them are templates that calculate a distributors performance. I have all of the necessary formulas already built into the template I just need an automated way of bringing in an account number from a list (A1:A150) on one of the spreadsheets (Sheet1) and populating that value in a cell (A1) of the template (Sheet2). Then I would like to export 4 spreadsheets ( sheet 2, Sheet3, sheet4, Sheet5) to a new workbook and save the new workbook in a folder on our network (path= N:\Distributor Results\2016\). the new workbook name needs to be (2016 +(text from cell A2 of sheet2) + Medalist Results). then I need the process to repeat to the next account number until the end of the list. There are about 150 accounts so as you can see this is very time consuming if we do this manually. Any advice is much appreciated.
Thanks again and I am looking forward to the next video in the series.
Regards,
Tom
Hi Tom,
I’m happy to hear you are learning VBA. That sounds like it can be a very time consuming process. Everything you listed out can be automated with VBA. It’s just a matter of putting all the pieces together and writing the code. I hope this video series helps get you started, and I know you will be able to save a ton of time by automating your task.
Thanks again!
Hi JON ! Would you please like to tell the procedure of calculating uncertainty using excel.
Hello Jon,
I am new in this website and I started VBA in Excel. Is there any help to know about coding in VBA or programming in Excel? Is there any Short keys for VBA or material in PDF or video?
I appreciate if you help me.
Thanks in advance
Vahid
Hi Vahid,
I do have a few videos in my VBA Pro Course that go over all my favorite keyboard shortcuts in the VB Editor. There are a lot of them. I hope that helps. Thanks again!
Hi Jon! I’m currently working on automating my workbook to copy my data entry tab and paste and clear data entry tab to a master tab. The information is always adding new information to master tab
Hi Idie, That’s a very common task with VBA. I have a 3 part video series on how to find the last used row or cell in a worksheet, that will help with that task.
Thanks!
I’m just starting to learn about Macros and VBA and with your explanation it looks not that difficult. Thank you very much!
Awesome! Thanks for letting me know Hudson. I’m happy to hear you are learning VBA. 🙂
I am so glad, finally I got proper guide for excel. and really i like your way of teaching
thanks & really appreciate all your efforts
Thanks Yehya! 🙂
I’m want to enter new data daily on sheet 1 and at the end of the day run a save and clear macro by copying data to another sheet where all the data are save everyday without copying over existing data.
Hi I am needingsome guidance on how I could create a macro in excelthat will copy data out of a as/400 system?
Hi Jon,
All of your videos are awesome. Best source I’ve had in a very long time. I’ve got one problem though. I’m not receiving an e-mail with a link to the second (and third?) video. Any chance I’m doing something wrong here?
Thanks again very much!
Stefan
Hi Stefan,
I send the emails a few days apart, to give you time to watch and practice. It looks like you did receive the emails for videos 2 & 3. Please let me know if you did not receive them. Thanks again!