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

 

 

68 comments

  1. Paul, your site is a gem. Your short, clear help on returning an array from a function saved the day. (Do I have to declare a preset number for items in the array, though?)

    1. Thanks – glad you liked it.
      You don’t need a preset size as you return to a dynamic array. When you declare a dynamic array you don’t set the size. VBA will set the size of the array when it is returned.

  2. Hi Paul,
    I bought this week your excel mastery book and I will today purchase your 41 habits book
    I have moved further with VBA in 1 week than I have in 4 years using an excel VBA book as your examples are more relevant – a BIG Thank u
    The question I have is (I can see that I need to extract from a string). In our case we get a very large string several 1,000 daily, and I need to extract just product name, currency, amount and whether vat applicable.
    CH,app_id,payment_type,product_type,payment_id,time_completed,recv_currency,recv_amount,fx_batch_id,fx_rate,settle_currency,reference_id,tax_country,tax_amountSD,113722035371451,S,P,814708421976839,2015-08-05 01:13:38 PDT,USD,9.99,412FC46A817E4D407D08C42B8A2276C0,1.0000000000,USD,846598368_468420301_100008249238630,BE,1.73
    can you give me guidance as to how to pick from here, ie how do I move from one, comma to another,and extract data as I go along (data can vary daily)?
    Thanks in advance for your help

    1. Hi Noel,

      Thanks for your comment. I’m glad you found the book useful. My aim was to provide a practical way for learning VBA so it’s great to hear positive feedback.
      Regarding your query – VBA provides a nice way of parsing a string into an array so you can easily access any part. This is done using the Split function.

      The code sample below splits the string and then accesses the currency type and the amount using the by index. Let me know if you have queries about it.

      [sourcecode language=”VB”]
      Sub ParseString()

      Dim s As String
      s = "113722035371451,S,P,814708421976839,2015-08-05 01:13:38 PDT,USD,9.99,412FC46A817E4D407D08C42B8A2276C0,1.0000000000,USD,846598368_468420301_100008249238630,BE,1.73"

      ‘ Parse string to array
      Dim arr As Variant
      arr = Split(s, ",")

      ‘ Access items (Debug.Print Prints to immediate window – Ctrl + G)
      Debug.Print arr(5) ‘ Currency
      Debug.Print arr(6) ‘ Amount

      End Sub
      [/sourcecode]

  3. Hi Paul,
    I recent purchased your Excel Macro Mastery book for Kindle. I have been going through it and have come across a few typos. I was not sure where to send the info so I thought I’d drop you a note here.
    Early in the book You have a function called UpdateCells(). The last line of code is missing a comma between the two Cells references. It should look like this:
    .Range(.Cells(r, c), .Cells(r2, c2)) = 24.99

    In your Use Strings() example the String variable is mistakenly declared as a Long. It should look like this:
    Dim CustomerName As String

  4. Hi Paul,

    I have found your site very useful in helping with a problem I have with VBA.

    However I am having an issue combining all the concepts to solve it.

    I am looking to return a range/ list of values to Column A of Sheet 1.

    This will be a dynamic list pulled from Column A on Sheet 2 based off criteria in Column C.

    Any advice

    Thanks

    Darren

    1. Hi Darren,

      This sample code should do the trick.
      It uses “Oranges” as the criteria. So if it finds “Oranges” in column C it takes the value in column A. You will need to adjust this to suit your own criteria.

      [sourcecode language=”vb”]
      Sub WriteVals()

      ‘ Use collection to store items
      Dim coll As New Collection

      ‘ Go through all rows in range
      Dim c As Range
      For Each c In Sheet2.Range("C1:C20")
      ‘ Check column C criteria
      If c.Value = "Oranges" Then
      ‘ Add value to collection
      coll.Add c.Offset(ColumnOffset:=-2)
      End If
      Next c

      ‘ clear all values from column A
      Sheet1.Columns("A").Clear

      ‘ Write out values from collection
      Dim i As Long
      For i = 1 To coll.Count
      Sheet1.Range("A" & i) = coll(i)
      Next

      End Sub
      [/sourcecode]

  5. Hi Paul,

    Thanks , much appreciated.

    Everything is nearly done but I wasn’t specific on the final part.

    I need the values from the collection to be entered into cell “B9” down.

    Thanks

    Darren

  6. Hi Paul —

    Quick question for you about the “table” functionality in Excel. On the surface, it seems like it could be handy in some instances.

    What is your opinion on tables (in general)? Good, bad, or it depends?

    My main concern is speed of execution – especially on large files. I’m beginning to test that out now, so hopefully that concern will either be alleviated or confirmed.

    Thank you so much for your website and books. I am enjoying your books so far — a lot of good information presented in a concise format. Very quick to learn and implement in a real world situation. I’m looking forward to your upcoming works. (I voted “10” on your survey today!)

    Best regards,
    Jim Thomure — St Louis, Missouri, USA

    1. Hi Jim,

      Thanks for your comment and vote. Glad you enjoyed the books. I spent 10 days working in St Louis many years ago. Really enjoyed it. My only visit to the USA to date.

      I’m a big fan of tables in Excel but I haven’t written much VBA for them. Most of the real world work I get usually involves data that comes from a database or some other automated source so it’s usually in raw form.

      In terms of speed it should not make a difference. If speed is an issue then make sure you use an array. Copying the data to an array and manipulating the data in the array is much quicker than accessing each cell to manipulate data.
      See this section of the Arrays post.

      Have a good day
      Regards
      Paul

      1. Thanks, Paul …. I had not even thought about copying to arrays ….

        Since I’m processing large amounts of data, speed was an issue. After trying this both ways, my benchmark times of using arrays vs. accessing the cells directly (either in a table or not) found that using arrays was 30 to 40 times faster ….

        One report I did took nearly 3 hours to run when accessing the cells directly — 5 minutes with arrays. The person I created this report for thought I was a genius … I said, “Well, there’s actually this Excel VBA genius named Paul …”.

  7. Hi Paul,

    Love your site. Have had a hiatus from Excel for a long time and are now playing catch-up. I’m wondering if you are still taking and answering questions as I’ve got a real brain buster (for me at least).

    Cheers
    Rick – Auckland, New Zealand

  8. Hello Paul,

    I found your site really very nice and informative. I want to learn VBA. Could you please let me know how should I go & start with it?

    I would be really very thankful if you take your time to respond me.

    Regards,
    Yogita

  9. Hi Paul,

    I have around 5 years of experience in reporting work of MS Excel. I know little bit about macro recording & editing. But I want to learn VBA to use it in my daily work & ease my work.

    I want to learn it in dept so that I can also be able to make projects by myself.

    Please assist me & let me know how should I go with it?

    What would be the easier & fastest way to learn VBA?

    Kindly help!

    Regards,
    Sandeep

    1. Hi Sandeep,

      I you are serious about learning VBA I would check out The Excel VBA Handbook. It has helped a lot of people to learn how to write professional quality VBA. There is a sample on the page if you want to try it out first.

      Regards
      Paul

  10. I quoted some excerpts from your article “Ultimate Guide to Collections” mentioning your article source. It was regarding Collections set to Nothing vs New Collection at Stack Overflow. I got following comments which I am not able to respond as I have just started learning VBA and do not have reasonable expertise to fairly evaluate the comments. Seek your valuable opinion. Comments are quoted below.
    ***Quotes Begin***
    That As New gotcha is the very reason why I added a “local object variable is self-assigned” code inspection in Rubberduck – too few people know about this! So, what actually happens to object references then? They’re cleanly deallocated when you set the collection to Nothing? Your examples only show value type items (a string is not an object in VBA) –

    Just tested both with adding 1024 x New Class1 references in a tight loop, monitoring the EXCEL.EXE process in task manager; it seems the memory leak is a story to scare kids when it’s late – nulling the reference does the job. – Mat’s Mug
    **** Quotes End***
    Warm Regards

    1. Hi Sunil,

      His comments are not very clear and took me a couple of reads to understand them.
      In the first comment he asks: Does setting the Collection to nothing automatically set all objects to nothing in the collection?
      In his second comment he says: Yes it does.

      Regards
      Paul

  11. Paul,

    Your latest email uses the CurrentRegion property to a return a range containing all of a sheet’s data. In most cases this is indeed correct. However, it relies an all of the sheet’s data being contained in a single region – if there are multiple regions (e.g. clear all the cells in row 11) then this causes problems for each of your examples.

    While it has its own issues (e.g. ignoring initial blank rows/columns), it’s much safer to use UsedRange.

    Cheers,
    David.

  12. I have a pivot table created using VBA and am using the the “Row Field” in “Data field” as “Count of Row Field”. Now when I try to rename that “Count of Row Field” to something else say “XYZ”, then the original “Row field” name changes too, which I don’t want. So, I tried the “.caption” option in VBA but it is changing both the row-field names.
    Contact – shinde.data at gmail.com

    Thank You.

  13. Hi Paul,

    Great site. Am planning to teach my team the basics of VBA and this looks like it could be a really good resource to help explain things (something I’m not great at). Sure I’ll learn something myself as my VBA skills are a bit rusty.

    Best wishes,

    Dom “Domski” Hill

  14. Received your VBA cheat sheet during the last week – it’s just the sort of generic VBA help I appreciate having. Many, many thanks, Paul.

  15. Paul Kelly, I have no words to say by reading your all articles and your E-book , I am really learning however I would like to say that not only learning but easily learning ,the way you have taken up to explain VBA theory is really great, I would really appreciate your efforts.

    A bundle of thanks for you.

  16. Hi,

    Am Ashok Kumar from India. Am working in IT in VBA Skill set
    Would like to subscribe in your news letter

    Thank you
    Ashok

  17. Hi Paul,

    How do I gain a deep knowledge in VBA? Do you give online classess?
    Like:- VBA(Array,desigh UDF,Integreated with other application,pull and upload data in Web page)

  18. Hi Paul
    I came across your blog because I was trying to find something about using multiple ranges to give me a download. For example, I set up a macro to transfer data to a template. However when the data gets more than 35 lines it doesn’t fit and requires to split the data, say line 1-35, then 36-70, 71 to end. My range worked fine until this. I have set up a pivot to know, the persons name which gives me the name of the template, how many lines, but I need to be able now set my range to read how many lines and split into two before transferring the data. can you help?

    1. Hi Stephen,

      Without seeing your code it is hard to give a precise answer. You can count the number of rows by setting the CurrentRegion and getting the rows

      Dim rg As Range, rgNew As Range
      Set rg = Sheet1.Range(“A1”).CurrentRegion
      lNumRows = rg.Rows.Count

      Or you can use the following to get the last row

      lNumRows = Sheet1.Range(“A” & sheet1.rows.count).End(xlUp).Row

      Once you have the last row you build you range using it.

  19. Paul

    Just want to say that your website is really excellent. I am an ex-programmer who is now working in operations, and I have had to build a very sophisticated planning spreadsheet. Your sections on modules and collections has helped me tremendously.

  20. Hi Paul, I’ve just bought your books on Amazon. Your blog is amazing! I’ve been learning a lot! Tks for sharing so much knowledge! All the best for you man! Greetings from Brazil!

  21. Hi Paul,

    Your site and explanations are great.

    I’m expecting a topic like “A Complete Guide to Recursion/Recursive Events in VBA” which would also include information about treeview data structures and FileSystemObject

  22. Hi Mr Kelly
    I’m Behnam Sarhadi of Iran , one of the Disciple You
    I’m Sorry I can not Pay the Cost of The Excel VBA Handbook , Because Payment by Credit Card is not Possible for me of Iran
    But I am very interested in reading your book
    I dont Know Wath to do
    Thanks

  23. Any tips or methods to distribute and update Excel/VBA applications? I’ve split the application in two: UI & code. Considering to split application in three parts: UI, data & code to also be able to update UI: the XML-ribbon.
    Is there any – even theoretical possibility to share application parts (allow simultanious users)?

  24. Hi Paul,
    I’m enjoying to study your email “How to Easily Create any VBA Macro” especially in step 5 :
    Step 5: Add the IF statement
    This will print only the names starting with A to the Immediate Window.
    For Each rCell In r
    If Left(rCell.Value, 1) = “A” Then
    Debug.Print rCell
    End If
    Next rCell
    I just wondering how to return the result into a excel range (for example range B1:B6) not only in the immediate windows.

    Thank you
    Omega

    1. You can do it like this

      Sub Test()
      
          Dim row As Long
          row = 1
          For Each rCell In r
              If Left(rCell.Value, 1) = "A" Then
                  Sheet1.Range("B" & row) = rCell.Value
                  row = row + 1
              End If
          Next rCell
          
      End Sub
      
  25. Hi Paul,

    I have gone through this, Which is very much useful and learning friendly portal

    I really loved from my bottom of my heart, and the way of explanation is as simple as it is which can be easily grasped

    If it is possible can you please give us few tips to connect web pages using VBA

    Thank you such a useful and helpful portal

    1. Hi Narayana,

      Thanks for your comment, glad you like the site. I hope to write a post in the future on connecting to webpages using VBA.

      Paul

      1. Hi Paul,

        Love your work. As I was browsing Module 2.6 in the VBA Handbook, I believe I found an error that you might want to take a look at. As brief as I can, in the VBA code of Clear Data, you set a constant of 5000 rows in the range clear definition. When you excecute the Clear Data event Button, it clears 5000 rows, however, there are over 6000 rows read from the database and only 5000 entries are cleared. I modified the max row in the clear data range to 7000 and appears to have worked perfectly. Seems to me that some how the range must be defined by the maximum number of rows in the Foods table and not 5000.

        Let me know if my assumptions are correct.

        John

  26. 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

  27. Hi,

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

Leave a Reply

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