The series where we answer comments from our popular Excel Userforms For Beginners series.

A question about Excel userforms that I receive often is:

“How Do I Insert A Reference Number For A Row Of Data Using Excel Userforms?” 

It’s a good question and there are a few ways to do it using Excel formulae and Excel VBA. Let’s look at 3 approaches here, including the approach I actually use in my real-world Excel VBA development projects. Watch until the end for that one!

CLICK HERE TO WATCH THE VIDEO

CLICK HERE TO DOWNLOAD THE FILE

 1. Use =ROW() formula (with =IF())

Yes, we might be able to solve this using only a formula! Let’s check out how …

Have you used the =ROW formula before? Formulae such as =ROW, =COLUMN and =ADDRESS can be useful when executing tasks such as this, which are concerned with position control. This is because the =ROW formula returns a number, and a number can be used with VBA to navigate to a particular cell.

Try it out in the Excel file yourself. Simply entering =ROW() returns the row number. Cool! So, have we found a solution using this simple formula? Possibly, but there are a few drawbacks.

First, the approach is not ideal from a presentational perspective, since it adds additional clutter to our dataset. Nobody wants to look at numbers that don’t really mean anything!

We can mitigate this, however, by embedding the =ROW formula inside an =IF formula, with a condition to check if there is an entry in an adjacent cell. If there is no entry, we can ask the formula to return a blank by using two speech marks (“”). This at least makes it look like the cells are blank.

But even this approach is less than ideal. The user might inadvertently clear the cell, not realising formulae were present. Further, the user may wish to sort the dataset, and sorting formulae is often problematic. So, might there be a ‘leaner’ solution involving no formulae at all?

 2. Count the rows somewhere else in the file, insert with VBA

The second approach combines the power of formulae with VBA. You thought we were done with formulae?! Well, yes, but here we are talking about a single formula that sits on a backend sheet, in our case the ‘Engine’ sheet. The user need not interact with this sheet and we could even ‘hide’ it to ensure the mechanics are not disturbed.

The COUNTA formula is exactly what we need since it counts the number of entries in given range. This poses a tricky question and uncovers one of the weaknesses of this approach: how many rows should we include in this range?

The easy answer would be to reference the whole column. But, this is best avoided because asking Excel to check over a million cells is resource intensive. You need to make a design decision at this point – what is the likely size of the dataset? Use prompts on the different sheets to remind you what the max capacity is.

Over the years, I have found this approach provides the best balance between efficiency and flexibility. If you don’t like it, using Excel tables is a possible alternative.

With the formula in place and returning the number of entries in the column, we can easily reference the figure in VBA to create a reference number for the new entry. It might need a tweak (eg. -7.) Another workable solution with a couple of tolerable drawbacks. But is there an even better option?

3. Use VBA to find the next row

The final approach, and the actual approach I use on real-world projects, is to shift the whole process of finding the next row to VBA. These short but powerful VBA instructions can be difficult to understand, particularly if you are new to coding, but it is well worth investing the time to understand them. I build one up step-by-step in the video. 

A single VBA instruction allows us to get what we need without formulae or other infrastructure in the spreadsheet. Drawbacks? Well, there aren’t many. You could say that having to use VBA is a drawback – but, if you are using Userforms, you are doing that already. Having text below the dataset could be problematic, so make sure you remove any prompts you’ve previously inputted. 

Other cons that I have missed? Let me know! 

And there it is, a powerful and flexible solution that should keep your file going for years.