About

About This Blog

portrait-paul-kelly-smallest

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.

About Me

I have been a software developer for over 20 years. I have worked for the largest insurance company in the UK, was a senior software consultant for one of the biggest Car Rental Companies in the world and have built card processing systems for a number of leading financial institutions.

In recent years, a great deal of my work has been building advanced Excel applications and training Excel users to get the most from Excel Macros in very competitive environments.

I have built a number of VBA tools including an automated code generator, a formula version tracker, broken link identifier, module version control and many more.

When not writing code I like to travel around South America, try my luck at Poker and follow my favourite team in the ancient Gaelic sport of hurling. I currently reside in the Emerald Isle.

VBA Handbook Queries
If you wish to contact me regarding the Excel VBA Handbook or my Amazon books you can email at paul@excelmacromastery.com.

Live VBA Workbooks
I regularly host free live VBA workshops on my YouTube Channel. If you wish to be notified of upcoming workshops then click on the subscribe button on the YouTube page.

Free Ebook
Please feel free to subscribe to my newsletter below and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.
Free VBA eBook

 

 

88 comments

  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

  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|
    H5|||
    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||||
    S|3924.57|627.93|4552.50|16.00|3924.57|||

    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,
    Al

  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.

      Paul

Leave a Reply

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