Hi, Welcome to my Excel VBA Blog. My name is Paul Kelly and I have created this blog to help you master VBA – the programming language for Excel Macros. I like to take complex VBA topics and explain them in easily understood blog posts. The posts are comprehensive and provide practical and useful VBA information and examples.

VBA Handbook Queries
If you wish to contact me regarding the Excel VBA Handbook or my Amazon books you can email at

General VBA Queries

Due to the large volume of emails that I receive, I do not provide help with individual queries unless they are related to my courses or subscriptions. I also don’t provide individual one-to-one training or consultancy for individuals at this time. If you have a question about VBA then you can post to my Facebook Group where there are lots of people who can help.

Live VBA Webinars
I regularly host exclusive free webinars for members of my email list. You can join my list by signing up for any of the many bonus items on this website including the one on the front page of the website. You can gain access to the webinar archive by signing up here.


  1. Hi Paul : I am trying to learn the set vba. I copy one of the VAB code on the web page to excel. I am getting a compile error on the Dim oCustomer As clsCustomer line. The error that I got is user-defined type not defined. Here is the VBA code . Really appreciate for any help on this subject

    Sub ReadCustomerData()
    Dim coll As New Collection
    Dim lLastRow As Long
    lLastRow = Sheet1.Range(“A” & Sheet1.Rows.Count).End(xlUp).Row
    Dim oCustomer As clsCustomer
    Dim i As Long
    For i = 1 To lLastRow
    Set oCustomer = New clsCustomer
    oCustomer.Firstname = Sheet1.Range(“A” & i)
    oCustomer.Surname = Sheet1.Range(“B” & i)
    coll.Add oCustomer
    Next i
    End Sub

    1. Hi Kin,
      It’s VBA, not VAB.

      clsCustomer isnt an object.
      From your code, it looks like you want to use a Collection.

    2. Kin,
      You need to insert class Module first, rename it to clsCustomer, add firstname and surname properties and some data to sheet 1.

      There’s a wonderful post here on class modules. Paul: MANY THANKS FOR IT!!!

  2. Hi,

    I learned a lot on this. Wondering if you have plan to create a guide about vba Excel to interact with Access 🙂

  3. I am very happy that I found this site.
    Almost everething is cristalclear.

    And Paul, greatest explanations an video.

    (sorry for my bad Englisch)

  4. Dear Paul,

    I need help on importing a data from excel file into text file. We are manually encoding the a particular payment code on each line of a customer in text file and this taking so much of our time. I hope you could give a a macro command that will capture a payment code from the excel tracker and import that data into a text file.

    Excel File tracker where the Company ID is unique:

    Centre Name Centre Number Country Company Name Company ID Payment Method Payment Code
    MEXICO CITY, Cygni Santa Fe 194 Mexico ACCENTURE SC 7400593 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Arinso México, S.A. de C.V. 6122913 Check 02
    MEXICO CITY, Cygni Santa Fe 194 Mexico Asesoria Especializada en Recursos Humanos Incoraxis S de RL 6741392 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Atencion Integral y Corporativa de Seguridad y Valores Roler 6741462 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Audio Codes Inc. 5116845 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Automatización en Internet S.A. de C.V. 5685043 Payment Card 04
    MEXICO CITY, Cygni Santa Fe 194 Mexico Balam Technology SA de CV 6953814 No Definido No Identificado
    MEXICO CITY, Cygni Santa Fe 194 Mexico Baselogistika, S. C. 6892355 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Bleumind Executive Search S.C. 4842592 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico BX REAL ESTATE MEXICO SC 7340234 No Definido No Identificado
    MEXICO CITY, Cygni Santa Fe 194 Mexico COMERCIAL VERYSE SA DE CV 7581649 Electronic Transference 03
    MEXICO CITY, Cygni Santa Fe 194 Mexico Comercialización y Marketing Torcord SA de CV 6950577 No Definido No Identificado
    MEXICO CITY, Cygni Santa Fe 194 Mexico Comercializadora Tec- Nor SA de CV 6741470 Electronic Transference 03

    And here is the text file where payment code should be place. On this string where after the word Original is the payment code “04” and after the 5 pipes is the equivalent of payment code which is Payment card.
    |Original||04|0||||Mexico, Mexico|||||Payment Card|

    H1|1314|1314/18895||20170530|PAGO EN UNA SOLA EXHIBICIÓN|Factura Comercial|2013|1314/18895|20170620|MXN|18.5121000000|P||cuatro mil quinientos cincuenta y dos pesos 50/100||||||||||30|Original||04|0||||Mexico, Mexico|||||Payment Card|
    H2|Regus Management México SA de CV|RMM080101V54|Av.Reforma|350|11|Juarez|México|Cuauhtemoc|México D.F.|México|06600||||Regimen General de Ley Personas Morales|||
    H3|Calle 39 Poniente No. 3515 – Piso 5 Col. Las Animas Puebla Puebla 72400|México|
    H4|Servicios Inmobiliarios Auge Hogar SA de CV|SIA0410154R6|2 Sur 1701|Col. El Carmen|Mexico|72530|Pedro Enrique Haces Gonzalez||8012208||Puebla Puebla|
    D|Oficina Virtual Plus – Tarifa inicial.|JUN 01 2017 – JUN 30 2017|1.00|3924.565200|3924.57|16.00|627.93|0.00|0.00|||No Aplica|4552.50||||

    I hope you could able to help me in populating the text file with the actual payment code with description of payment code.

    I can send you the actual text file with the payment code tracker in excel file for your reference.

    Thank you,

  5. Hi Paul, I have benefited greatly since I first encountered your course on Udemy. I have literally spent years trying to come to grips with writing VBA code and in the short time that I have been associated with you I have learned more and am much more confident in writing VBA code. Your site is a magnificent resource and I have complemented it by by purchasing your Handbook and Videos, plus I have purchased your books, 41 Success Habits for Creating Excel Macros and Excel Macro Mastery – How You Can Write VBA Like a Professional in 15 Easy Steps.
    Thanks heaps.
    John, Melbourne Australia

  6. Hi Sir,
    i have written a code for looping through occurrence of a number in a range. my range A2: A contains numbers from 1 to thousands or more. i just want to repeat value of another cell with adding some string. the code is as follows:
    Sub myfn()
    Dim lastrow As Long
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Sheets(“Sheet2”)
    Dim i, k As Long
    Dim m As String
    Dim p1 As String
    lastrow = WS.Cells(WS.Rows.Count, “A”).End(xlUp).Row
    m = “Mukesh”
    j = 1
    For i = 2 To lastrow

    If Cells(i, “A”).Value = j Then
    p1 = “” & Cells(i, “H”).Value & “” + vbCrLf
    m = m + p1
    End If
    Next i
    j = j + 1
    Debug.Print m
    End Sub
    But the problem is that j is not increasing by +1
    Pls help. I shall be very gratefull to you

  7. Hi Paul,
    I noticed that intellisense doesn’t work for some VBA objects. For instance, for the following code:

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    after writing Cells(Rows.Count, 1). intellisense doesn’t pop up and you need to seek in every place in order to know how to continue writing the full code line.
    Is there any way to solve this problem and have access to intellisense in these situations?
    Thank you, and congratulations for your outstanding service.

    1. Cells is considered to be a Variant/Object/Range. Variant means the type is not known until the application is running so that’s why there is no Intellisense

      You can use a range variable and then use the Intellisense of this.

      Dim rg As Range
      Set rg = Sheet1.Cells(Rows.Count, 1)
  8. Thank you for all the work you are doing! just yesterday i realized you have some videos which is awesome 🙂 Something perhaps you know Windows key and + or – will do for the magnifier.

  9. Just to say thanks for this website. I’ve just discovered it today and have spent about 3 hours learning from it already. Really helpful, really enjoyable. to learn from. You are a great teacher, thank you!

    1. Hi Rolf,

      If you mean selected row and column you can Selection to get the Range.
      If you mean the active cell you can use ActiveCell to get the cell range.

      Once you have the range you can get the rows or columns from the range.


  10. I hope you can help me figure out how to summarise an array e.g. Column “A” has a set of repeated name, each name has a different value corresponding in Column “B” and a different date in Column “C”. Can you set column “A” to have each name once and sum the figures for Column “B” for that name in an array?

  11. The Data Model is offered each time I retrieve a new set of data into a tab. It says, “Add to the Data Model” and starts offering functions previously reserved for Access. Paul can you look into covering this move towards “Big Data” processing that Excel now offers?

  12. Paul,

    As a struggling student of all things VBA I wish to give my sincere thanks to you for sharing your knowledge (at no cost) to anyone one interested enough to seek to educate them selves.

    The power of VBA consistently amazes me and I find new ways to use code every day.

    I frequently revisit your blogs when trying to make square things fit into round holes and you just have a way of explaining things in a way that just makes sense even with the minimal understanding of VBA that I have learnt.

    I thank you for your commitment to educating people on VBA, this person greatly appreciates the effort and work that obviously goes into your work.

  13. Hi Paul. your website is brilliant and very helpful. currently I am struggling with a vb script which reads the UDP packets. there is no problem when the packets are being sent but when the packets are lost(let say network disconnected …) I got much load on the software .I think the reason is that my error handling is not efficient.
    Would you please have a look at my code and advice me?

    1. Hi Mehdi,

      Due to the volume of emails/queries that I receive, I can only deal with queries relating to the VBA Vault, The Excel VBA Handbook or specific blog posts.


  14. Hi Paul,

    Just wanted to say a big thank you for sharing your VBA knowledge in such a structured, beginner friendly way!

    I’m only part way through Part 1 and felt compelled to thank you already!


  15. Hey Paul,

    I received your email regarding the Class Modules webinar taking place on 11/28/2018, and in the section where it has the start times, you have the following: 11pm Pacific, 1pm Central, 2pm Eastern, 8pm London.
    Shouldn’t the start time for Pacific be 11am instead of 11pm?

    Thanks for all the knowledge you share! Great stuff!


  16. Hi Paul,
    I have a Mac and am just starting out with VBA. Do you know if there is a significant difference in VBA code between Macs and Windows?

    1. The VBA code is the same on both platforms. However on Windows, you can use external libraries which are not available on the Mac.

  17. Apreciado amigo Paul, estoy sorprendido por su generosidad, que el Señor siga incrementado su sabiduría.
    Muchas gracias

  18. Hi Paul,

    I just have a quick question:

    Is there a Range object that using the copy function would paste into the highlighted cells. (Not a defined range, you would decide before hand where the data will be added) I have to add values to a file every morning and some values are not calculated everyday so I can’t use offset with the xldown.

    Thank you,


Leave a Reply

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