October 5th, 2017
vba2010

VBA 2010 Video Tutorials

Visual Basic for Applications (VBA) allows you to incorporate powerfull code into your standard Microsoft Office Documents. These tutorials will teach you the ins and outs of how to use VBA within excel from nifty tricks to fully fledged mutli-user systems.

All our tutorial samples are free however if you appreciate our work and would like to make a voluntary donation then please do.

Download the samples for this series for free!

Recommended Reading

Both the below books by John Walkenbach are fantastic books and he is a brilliant author, I would recommend picking one of these books up even if just for a reference. I have read power programming cover to cover and it is easy to follow and is a must have for any serious VBA programmer.

If you have read any books and feel they are worth recommending then please post about them on our Forum.

Java Video Tutorials

HTML Video Tutorials

9 Comments

  1. Ganesh
    June 19, 2015

    Hi,

    I went through all your macro videos and it was very helpful. Now I’m trying to write a macro, which will run once the batch job is completed. I am also triggering a batch job using the same macro. But as the batch job takes some time to run i want the macro to wait till the batch is completed. Could you please help me in this?

    Thanks.

    Reply
    • Matthew
      June 21, 2015

      only thing i can think of is that you get the batch file to generate a temp txt file once its completed that the vba then identifies as being there and then does what it needs to and deletes the file. Its not the best though and vba not having threads is going to make it messy.

      Reply
  2. Ajesh Singh
    July 24, 2015

    Hi Matt,

    I was doing this piece of code:
    For Each cel In Target.Cells
    cel.Interior.ColorIndex = cel.Interior.ColorIndex + 1
    Next cel

    but this give me error: subscript out of range (in Excel 2013). This is exactly the same code as your tutorial 35.

    Please suggest.

    Reply
    • Ajesh Singh
      July 24, 2015

      I get the error in the line where i’m trying to update the colorindex

      Reply
  3. Waz
    August 13, 2015

    Dear Matt,
    Your videos are great mate and thanks for sharing with us. I was following videos couple of weeks now to master the VBA.
    I got a question for you in the VBA tutorial 41 and I having a issue when saving the actual new workbook excel file for your particular code. Further, in my workbook it doesn’t allow be to save macro enable workbook for this code for the newly opened workbook. (however, I have already selected the option to run all the macros via the Developer>>Macro Security option). Further, when I try to save in “Save as” the dialog box, I couldn’t find the “Macro Enable workbook” . kindly please help

    Waz

    Reply
  4. Carl
    August 20, 2015

    Hello Matt,
    I went through your tutorial on VBA and i must admit that these are the best i have ever seen. It has helped me a lot in understanding the concepts.
    I had a query if you can help me with.
    I have a folder inside which i have several notepads. I want to search and replace any string for all of those notepads without having to individually open each of those.Would you able to suggest me a macro as to how can we take inputs from the user as to which strings he wants to replace and also what he wants to replace that with and run a macro to do the same.

    Cheers!!!
    Carl

    Reply
  5. Rachel
    December 7, 2015

    Hi Matty, I am new to VBA and hope that you can help me with my PO-Template – Excel VBA which I copied below:
    ‘What I want is for any user or myself to be able to
    ‘Open the PO-Template;
    ‘Generate a new PO number;
    ‘Complete the PO-Template with purchase order information in each required cell;
    ‘Send the information from the designated cells to the PO-Log;
    ‘Print the PO-Template;
    ‘Save a copy in both .pdf and .xlsx formats in the parent folder \PO-2015\;
    ‘Clear all the cells;
    ‘Keep the PO-Template in .xlsm format to start all over again.
    Once I completed my PO-Template, I looked at my files and deleted the .xlsx file thinking that it was not the one I needed and kept the .xlsm file. I no longer had my macros and had to start all over again! This is what I could remember. I need help putting it together in the right order and with only one or two buttons.
    Sub NextPO()
    ‘This allows the PO-Template to change PO number when clicked
    ‘The cells are cleared to create a new PO
    Range(“B4″).Value = Range(“B4″).Value + 1
    Range(“B5″) = Date
    Range(“A11:D23″) = “”
    Range(“B6″) = “”
    Range(“B7″) = “”
    Range(“B8″) = “”
    Range(“D24″) = “”
    Range(“D26″) = “”
    Range(“D28″) = “”

    ActiveWorkbook.SaveAs “PO-Template.xlsm”
    End Sub

    Sub POReport()
    ‘Once the PO-Template is filled, copy the information from the cells PO number (B4), Project Name (B6) and Supplier (B7) to the PO-Log
    Dim myFile As String, lastRow As Long
    myFile = “C:\Users\Brisson\Documents\Database\” & Sheets(“PO-Template”).Range(“B4″) & “-” & Sheets(“PO-Template”).Range(“B6″) & “-” & Sheets(“PO-Template”).Range(“B7″) & “.pdf”
    lastRow = Sheets(“PO-Log”).UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    ‘Transfer data to Log
    Sheets(“PO-Log”).Cells(lastRow, 1) = Sheets(“PO-Template”).Range(“B4″)
    Sheets(“PO-Log”).Cells(lastRow, 2) = Sheets(“PO-Template”).Range(“B6″)
    Sheets(“PO-Log”).Cells(lastRow, 3) = Sheets(“PO-Template”).Range(“B7″)
    Sheets(“PO-Log”).Cells(lastRow, 4) = Now
    Sheets(“PO-Log”).Hyperlinks.Add Anchor:=Sheets(“PO-Log”).Cells(lastRow, 6), Address:=myFile, TextToDisplay:=myFile
    ‘Saves the PO (PO number (B4), the Project Name (B6) and the Supplier (B7)) in PDF format
    ‘The syntax below was working and now it does not save the document in .pdf format in the \PO-2015\ folder.
    Sheets(“PO-Template”).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
    ‘Saves the PO (PO number (B4), the Project Name (B6) and the Supplier (B7)) in xlsx format
    ActiveWorkbook.SaveAs “C:\Users\Brisson\Documents\Database\PO-2015\” & Sheets(“PO-Template”).Range(“B4″) & “-” & Sheets(“PO-Template”).Range(“B6″) & “-” & Sheets(“PO-Template”).Range(“B7″) & “.xlsx”, FileFormat:=51
    ‘ActiveWorkbook.Close
    Application.DisplayAlerts = True
    End Sub
    I am not sure I have everything in the right order. If you wish to see my PO-Template, I can send it to you. Thank you so much for the time you will take to help me with this. I will be happy to give you a donation! Rachel

    Reply
  6. dan
    June 13, 2016

    Jon:
    I see the PROPERTIES & METHODS for an object at:
    https://msdn.microsoft.com/en-us/library/office/ff193284.aspx.
    However, the following statement I coded tells me that “Worksheets” is an object within “Workbooks” :

    Application.Workbooks(1).Worksheets(3).Range(“B8″).Value = 138

    But when I click on “Workbooks Members” at the (above) link it does not show that “Worksheets” is contained in “Workbooks”…how come?

    Thanks,
    Dan

    Reply

Leave a comment

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