VBA Training Series – How to Write Your First Macro (Part 1 of 3)

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:

My-First-Macro-Reference-Common-Objects.zip

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.

Get the Free Videos

Please click the big green button above to register for the free training series.

Jon Acampora Circle MVP Profile 2019

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!

1,683 comments

1 … 5 6 7 8 9 … 23

Your email address will not be published. Required fields are marked *

  • In my work I create statements & forms for end-users. I want to automate how they format & finalize their work prior to sending out to customers; then automatically create an e-mail with the file attachment. I have an associate in my area who is very good with VBA and is collaborating with me but I need to learn for myself. He has directed me to this site to get me started in that learning process. I am very excited about the prospects of me becoming my department’s Excel guru and problem solver. Can’t wait!!!

    Reply

  • I really enjoyed this first video and am looking forward to seeing more. I’ve been looking for a way to increase my understanding of VBA and I think I’ve found it. Thanks.

    Reply

    • Awesome! Thanks Thomas! I really appreciate the great feedback and am excited you are here. Have a great day! 🙂

      Reply

  • Hi there. Thanks, the 1st video was great. I’d like to learn code to automate harvesting email addresses from my inbox or a folder I’ve put them in. (I’m using Outlook 2013.)First prize would be automate and copy over the person’s name and email address and any other contact details to an Excel workbook.

    Reply

    • Thanks Robbie! That is definitely possible with VBA. VBA can also be used to automate Outlook, and you can write code that allows you to run automations in Outlook from Excel, or vice versa.

      Reply

  • Ok some minor problems or clarifications’ maybe needed corrected or explained.
    A error message appeared found the cause:
    1 Note the plural in the words – Workbooks and Worksheets that
    Kept me busy for a half hour with an error message.

    Had a problem understanding this:
    2 This line of reference code as follows:
    Application. Workbooks(“MyFirstMacro.xlsm”). Worksheets(“Sheet1”). Range(“A1”). Value = “Goodbye”
    Would this not go first, as it a reference location? But then you are ending it
    with a procedure in that you want to go back to sheet 1, “A1” and enter “Goodbye” ?
    In that the macro is within the Workbook that you have place the macro, why add it?
    I however, note it is an assumption that Excel will know this is the correct Workbook,sometimes.
    As far as the sign-off of “Goodbye”, just add the following using your example:

    Worksheets(“Sheet1”). Select
    Range(“A1”). Select
    Range(“A1”). Value = “Goodbye”

    Unless the is all wrong.
    Application.Workbooks(“MyFirstMacro.xlsm”).Worksheets(“Sheet1”).Range(“A1”).Vakue = “Goodbye”
    Looking forward to learning more.

    Reply

    • Hi Fred,
      I would be happy to help. The plural in the words is there because we are referencing the collection of Workbooks and Worksheets. The Worksheets property is the collection of worksheets, or all the worksheets, in the workbook you are referencing. If you don’t reference a workbook name then VBA uses the active workbook.

      I’m not sure I fully understand your second question. The fully qualified line of code would not necessarily effect the lines of code below it. If you do not qualify or select the workbook or worksheet first, then VBA will assume you are referring to the active workbook/worksheet.

      You can use a With statement to shortcut the fully qualified line.

      With Worksheets("Sheet2")
      .Range("A1").Value = 1
      .Range("A2").Value = 3
      .Range("A5").Value = 10
      End With

      In the code above all the range references would be for Sheet2 because the lines start with a “.” period and are inside the With statement. This makes it so you don’t have to start each line with the worksheet reference. It allows you to shortcut the code a bit. I hope that helps. Thanks!

      Reply

  • Im new to VBA and i think your video is very well explained.I need it to perform some very repetitive
    tasts that i do in excel. I look forward to the next video

    Reply

  • Very easy to understand concepts of VBA. Looking forward to more videos and experimenting more with the use of VBA..
    Thanks

    Reply

  • I realised I needed a macro to take and store data from a field that gets overwritten periodically. I searched YouTube for a copy and paste routine and found your video on this topic. I became aware in the opening seconds that my knowledge of VBA was too limited to benefit from this so I was mightily relieved to be redirected to your training series where Video 1 proved a perfect starting place. Many thanks I look forward to learning more.

    Reply

  • Thank you for your crystal clear steady paced presentation. i love excel and subscribe to many free guru providers.
    I am looking to create a dashboard for a small group of golfers, someone has established the course specifics and player handicaps. I think that VBA may assist with the tedious tasks of score recording /handicap adjustments/nearest the pin achievers and so on.

    Looking forward to your next two videos Jon and thanks again.

    Reply

  • Thanks Jon! I’ve learned a little VBA in the past and am rusty now but I have to say that this introductory video is outstanding and I’m looking forward to continuing.
    I would like to use excel to retrieve stock quotes online and then create a portfolio summary which includes updated graphs.
    Sandy

    Reply

  • I have seen so many videos on VBA/Macro creation before and have been wanting to learn more but always get confused on the big picture. But you did a very good job at breaking it down and explaining parts clearly to me (even down to the object hierarchy and sections of VBA editor). I look forward very much to watching the next video! Thank you =)

    Reply

  • Great intro and super informative. I haven’t coded VBA for 10 years or more. Looking forward to a refresher and seeing all the new features. Thanks very much in advance.

    Reply

  • This is very good training.
    Thanks!
    🙂

    Do you have a tutorial about … how to use questionnaires where excel will download data from the Word?
    Regards

    Reply

  • Thanks Jon the introduction helps immensely! I’ve been dabbling a bit in VBA but still a complete novice. In particular I have two requirements currently.
    1. How to populate cells with the address of a selected property in a dropdown list. I created used VBA code to create the dropdown list. On selecting the property I want the cells in the rows below to auto fill with the perimeters of the address i.e. street name & number, suburb and city. I’ve used “Select Case Range” to do the auto fill according to the selection from the drop list. The code works but only once after I physically run from the VBA window. What do I need to do to enable this functionality to update automatically on selection of the property from the dropdown list?

    2. How to find the last cell in a statement – after watching your video clips on “range find” etc. the code seems applicable but I just can’t quite pin it down to my needs. I have a running total column which with figures being either zero, negative, positive or even empty and the code I use would need to pick up the value of the last cell in the column albeit any one of the first three numeric conditions. The column to the left of the column with the figures mostly contain a description to the adjacent figures but may be blank along with its adjacent cell as a break between two separate instances recorded. There are formulas in the cells containing figures used to tally the running total. In the event that the last figure is zero subsequent cells don’t display as the column has been custom formatted using 0;-0;;@.

    Hope I’ve managed to capture the two requirements and look forward to your kind assistance.

    Thanks, Noel.

    Reply

    • Hi Noel,

      For the combobox you will need to add your code to the Combobox_Change event. This will run the code every time the user makes a selection in the combobox. I am assuming you are using an ActiveX combobox on your worksheet for the drop down menu. You should be able to right-click the combobox and choose “View Code”. That will automatically create a change event macro for the combobox. Just add your code to that macro.

      I’m not sure I fully understand. If your last column contains formulas then you can use the Range.Find method and set the LookIn parameter to formulas (LookIn:=xlFormulas). This will search for formulas and stop even if the formula is returning a blank.

      I hope that helps. Thanks again!

      Reply

  • it looks so easy! I can’t wait to see more. Our sales force database runs great queries but the reports are unformatted. Every time we run a report we have to format – no two reports ever look the same. I’m looking for a macro to format our reports and save time. Thank you. Love your clear and concise presentation.

    Reply

  • Hi Jon,

    Hope u doing good. I have been working as reporting analyst and I do have to deal with formulas, logics, pivot tables etc. everyday. I found this video very useful as I am still a beginner in writing codes using VBA. I will be greatful if u can teach me to write codes for simple vlookups, functions. So that I can automate my sales report.

    Reply

  • I have a Data base in excel that has over 2000 rows of information and 20 columns of information . I wrote a program using VBA that adds information at the bottom open row. The problem that I have now is that I need to find information in any given row, the program does not read anything.

    An example is in row. Account Number, Name of person, Address, City State, Zip Code,Status, Classification, ETC.
    How do I get the information to be seen in VBA from and row, so that I can make a printout of same.

    Reply

  • Dear Jon,

    I’ve watched your first Video of this 3-part VBA course.
    I was in Business about 30 years; since 10 years I’m working at school as a teacher for mathematics and computer science. And EXCEL has been “faithful companion” always.
    A couple of years ago I started with VBA; it’s fascinating, and sometimes there are funny moments, when I feel “I have to program something”. So a lot of useful (especially for mathematics) but also “quite nice” stuff (e.g. programming games or simulations in VBA) has been produced and my skills and experience was growing.
    However, watching tips, tricks, and Videos about VBA are always welcome; sometimes there are only tiny tips which I didn’t know but which will help me improving my work.
    Your first Video is very good, and gives an idea of VBA for those, not being familiar with it; I’m anxious to watch the next one.
    Sorry, that I’m watching your Videos more from “didactics view”, but with that I can only say: very good Job!
    At School I’m starting the VBA “course” more playful, usually with a small game: two Players are “rolling a dice” by pressing a button; VBA creates a random naumber “on demand”, adds numbers of the players and gives a “Winner” note together with a Sound. O.K. – perhaps not the approriate way for People who are in Business …

    Thank you again for your good work and
    Best regards
    Your German EXCEL fan Bernd

    Reply

  • When I’m inputting in Excel, sometimes I want to move to the right when I press enter, but sometimes I want to move down. Can I add a custom button to the ribbon that will accomplish this? Thank you for videos.

    Reply

    • Hi Robert,
      Yes, the ActiveCell property has parameters that allow you to specify the number of rows or columns you want to offset. So the following code would select the cell to the right.

      ActiveCell(1,2).Select

      You could create a macro with that code and assign it to a button in the Quick Access Toolbar.

      You could also use the Tab key to move to the right.

      Let me know if you have questions.

      Reply

    • Hi Kim,
      Not at this time but I am looking into that for the future. Please let me know if you have any other questions.

      Thanks!
      Jon

      Reply

  • As an Accountant for many many years I started on Excel when it first was introduced.

    So I have an excellent knowledge of the programe . With a number of helpful add-ins I have increased my abiity no end.

    However VBA has always been lacking in my learning but watching your Video has made me think about adding this to my list as you make it seem so easy to get in to and produce some great time saving procedures.

    Reply

  • I’m building a cost management administration in Excel. I probably will have to migrate it to a database later on. Therefor I’ve more or less setup the worksheets like I would have set up normalized tables in the database. I’ve build some of the calculations as functions in VBA. Next step will be to identify the input and chance processes the user will follow to maintain the data. Once identified I want to build intelligent input sheets, user forms, for data input and data changes.
    So I’m interested to learn how to build the user forms to maintain the data in the worksheets. I will have to bring data from one form to multiple worksheets, I will need to handle dependencies in the data and I will have to protect data from changes once it’s in the worksheets.
    Another issue I have is that I’m missing trace functions like I know them from for example Basic and Rexx, displaying the code with it’s variables during execution of it.

    Reply

    • Sounds like a good project for VBA Rens. We discuss userforms in the 3rd video in the series. That should help get you started.

      Reply

  • I work in manufacturing and distribution as an International Customer Care Representative. I have worked with Excel for about 15 years and I am excited to delve into the VBA aspect. I have experience in creating macros using the recorder, but I would like to learn more so that I can update my macros without using the recorder. No sense in going through the creation again if I am only needing to reference a larger range of cells.
    A lot of my work requires the use and manipulation of spreadsheets, anything from forecasting to inventory, packaging or production schedules.
    I would have to say that I am guilty of using any means to make me look like a Data Guru or anything that will make my tasks (and others tasks) easier to perform.
    Thank you for offering the videos and sharing your talent!

    Reply

    • Hi Liz,
      Sorry, I just saw your comment. I’m happy to hear you are learning more about writing your own macros. It will really help you automate tasks beyond what the macro recorder can do. I just published a blog post and video series on the Personal Macro Workbook. It includes examples of how to add macro buttons to the Ribbon, and a Yes/No pop-up box before the macro runs. Thanks again and have a great day Liz! 🙂

      Reply

  • I am just starting to get into macros and this video was very easy and helpful. I have read about 7 chapters of Excel VBA for dummies but came to a stop as I had no use for VBA at the time. As I use excel for dashboards (basic) and data analysis in my work I know exactly what I need to do and in which order but because I don’t know the vba language I cant write the macro. I am going to watch all your videos and learn vba tyo push my excle skills to the next level and I am very excited about this.

    Reply

    • Thanks Abdul! I’m happy to hear you are learning VBA, and am also very excited to have you here. VBA is a really great skill to add to your list of Excel skills, and one that companies are always looking for. 🙂

      Reply

  • Jon — well done! I like the smooth presentations and all the examples. I am a retired IT guy, who is now trying to move away from heavy server/database management to more personal focused learning, and your video is excellent! I am looking forward to more. How easy is it to replay the first video? I hope it was not a one-time-only lesson. 🙂
    Al

    Reply

    • Thank you Al! It’s always refreshing to hear about an IT guy learning Excel. 🙂 I really appreciate the great feedback. You can come back and watch the video any time. Just bookmark this page or keep the emails I send. Please let me know if you have any other questions. Thanks again!

      Reply

  • Jon, good job!
    I have written thousands of lines of code in Excel 4.0 macro that no longer run correctly with the newer releases of Excel. I have been needing to modify the macro code to be compatible with Excel 2003, which will soon be the only version of Excel allowed in my company. The problem for this compatability lies in making and modifying charts; the macro 4.0 code seems to still work other than on charts. I have spent a very frustrating few days recording, altering, and playing back VBA macros. If I had started with your site, I would have saved many hours.
    Right now I’m trying to enter a desired title for a chart. ‘hood’ and ‘sheetname ‘ are strings defined in the 4.0 macro code.
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    Dim Title As String, hood As String, sheetname As String
    Title = “H3PO4 ” & hood & ” ” & sheetname
    Selection.Caption = Title
    This code only ends up with the title “H3PO4 “

    Reply

    • Thanks for the great feedback Carl! I’m not sure I fully understand your question. Did you set the sheetname variable in your macro?

      sheetname = ActiveSheet.Name

      Reply

  • Hi Jon,

    Great video Jon. I’m totally new to writing VBA code. Most of the time I just use the Macro recorder.

    I need help writing a code for one of my workbooks. I need to filter for value that does not equal zero or blank in column J in Sheet1 and paste to Sheet2. Going back to Sheet1, I need to filter for value that does not equal zero or blank in column K and paste to sheet2, in the next empty cell after where copied data of column J was pasted… I hope that makes sense.

    Thanks
    Soo

    Reply

  • What I am trying to learn to do is create a usable user form to simplify input of information into a spreadsheet which can be used in conjunction with pivot tables to create reports on a daily and weekly basis. I have got a bit of the concept of how to create the form(s) just not how to appropriately apply that information into the spreadsheet.

    Reply

    • Hi Philip,
      Great question. We cover usersforms in the third video, but transferring data back to the worksheet is a great question. Here is a very simple example of how to set the value in cell A1 to the value in a textbox in a userform.

      ActiveSheet.Range("A1").Value = UserForm1.TextBox1.Value

      Now, you will need to trigger that code with an event. Typically this event will be a button click. So you could add a “Submit” button to the form, and place that code in the Click event code for the button.

      I hope that helps. Let me know if you have any questions. Thanks!

      Reply

  • Thanks for trying to help people who are willing to learn and I am one of them. Hopefully we will learn a lot more from you.

    God Bless…

    Reply

  • Jon,
    Thank you so much for the video that people who are just starting outcan understand!! Your a wonderful person for teaching and coaching people and the examples that were used explained it perfect. I1m very excited about learning VBS and MACROS!!
    Thank You,
    Stephanie

    Reply

    • Hi Stephanie,
      Thank you for the kind words. I really appreciate that and am happy I can help. It’s what I’m here to do. 🙂

      I’m excited that you are learning macros. Awesome! Have a great day!

      Reply

  • Thanks for the video. It is very well done. I will wait to ask questions until I see the next video in the series.

    Reply

  • Hi there! Thank you for having this video. I always thought that writing VBA is very difficult. The video made writing seems to be very easy and not intimidating. The video is very explicit and clear. I look forward to next videos.

    I am a medical writer for GE Healthcare. I put together a list of my tasks to track how long it takes me to complete one task. This helps me in figuring out if the timeline my project manager provides me for writing protocols, consent forms, and clinical study final reports for one clinical study are feasible; and I can predict how long it will take me to write one document. Learning how to write the VBA will help me automate my list and tying it with my timelines from MS Project.

    I am still learning how to create Gantt Charts in Excel to help me see my progress on all my projects. Again, thank you for the video!!!

    Kind regards,
    Catherine

    Reply

    • Thank you Catherine! Sounds like some great uses for VBA to help save time. Please let me know if you have any questions as you watch the other videos. Have a nice weekend! 🙂

      Reply

  • Hi Jon,

    Fantastic presentation. i cant see the 2nd and 3rd video though.

    Thank you so much for your help.

    Reply

    • Thank you Joan! I will be sending you an email with the link to video #2 in just a few days. You should also have received an email from me after you signed up. Thanks again and have a great weekend! 🙂

      Reply

    • Hi Victor,
      Dim stands for Dimension and is the statement we use to declare variables. I explain more about that in the second video. Here is the link to the help page for the Dim Statement

      I hope that helps. Let me know if you have any questions. Thanks!

      Reply

  • Great video. My needs require I retrieve files from a network shared drive, import them, filter them, combine them and run comparison analysis on the results (pivots, error %, etc.). I’ve been looking into Power Query/Power Pivot for this but am still in the early stages of learning that one and thought macros might be an easier fix. Thanks again for the insights of this video. I look forward to the others.

    Reply

    • Hi Wayne,
      I use Power Query/Power Pivot in combination with VBA to make some great automations and reporting solutions. All of these tools can really help us build applications in Excel. The Power tools are great and I highly recommend learning them along with VBA.

      Thanks again and have a great day!

      Reply

  • This is great – I am loving your videos! I am a Business Ed Teacher but I have never taught this to my students because usually we run out of time and we don’t get this far. I am hoping to teach a more advanced course and I want to share this with my students (these are high school kids) Is there a chart somewhere that has sample code to refer to as we work?

    Reply

    • Hi Amy,
      Wow! High school kids learning VBA!?!? That is AWESOME! Each video in this series has some sample code. I will email you another file that might be useful for your training. Please let me know if you have any questions as you go through the training. Thanks!

      Reply

  • Very helpful video.
    What makes it so good is that it not only shows the module and it’s result, but also explains the function of each command.
    Thanks Jon

    Reply

  • Hello Jon.

    Just looking around here. Nice videos. I am looking at ways to get the files i work with (rawdata files 200 Mb and bigger) easier to manage due to the lage file size. I dont have access to databases so i need to work alot with connected files. Now when doing that i always need to open the rawdata file before i open my report file to get the data refreshed. Now i heard about a way to do this with VBA so i did not have to open the connected files for my report files to be updated with the latest data. Thats why i was snooping around 🙂

    Reply