Validating company registration numbers

N.B. The next stage of this project, which covers parsing filenames and checking numbers against the list produced in this question, is covered here.

I have a list of company numbers (UK companies registered at Companies House) in an Excel spreadsheet. A CRN is an 8-character unique identifier attached to a specific registered corporation (usually 8-digits but not always).

Examples of company numbers:

• 02492078

• OC374102

• 03637689

This section of code handles:

• Retrieving the list of numbers

• Validating the numbers

• Adding any unique values to a Dictionary object

Later on, they will be used to check if a particular CRN is among the list I am targeting by using the Dictionary.Exists() method.

```
Option Explicit

Public Const COMPANY_NUMBER_COLUMN As Long = 1
Public Const parentFolderPath As String = "S:\Investments\Data\Companies House\Monthly Companies House Downloads\"

Public Sub ParseAllCompanyRecords()
'/ Data Structure: "Company Numbers", once input, will be stored as strings
'/ Company Number: 8-character string, generally 8-digits but sometimes with text prefixes E.G. "OC374102"

'/ Folder Path for monthly CH downloads: "S:\Investments\Data\Companies House\Monthly Companies House Downloads\"
'/ Filename Strucutre of a Monthly Folder: [parentFolderPath]"Accounts_Monthly_Data-"[Full Month Name][yyyy]"\" - Square Brackets not in filename
'/ Filename Structure of an individual filing: [Monthly Folder Path]"Prod224_0005_"[8-character Company Registration Number]"_"[yyyymmdd][.html OR .xml] - Square Brackets not in filename

Dim targetCompanyNumbers As Scripting.Dictionary
Set targetCompanyNumbers = GetTargetCompanyNumbers

End Sub

Public Function GetTargetCompanyNumbers() As Scripting.Dictionary

Dim targetCompanyNumbers As Scripting.Dictionary
Set targetCompanyNumbers = New Scripting.Dictionary

Dim finalRow As Long, columnRange As Range
With wsInputs
finalRow = .Cells(.Rows.Count, COMPANY

'/ Data Structure: "Company Numbers", once input, will be stored as strings
'/ Company Number: 8-character string, generally 8-digits but sometimes with text prefixes E.G. "OC374102"

'/ Folder Path for monthly CH downloads: "S:\Investments\Data\Companies House\Monthly Companies House Downloads\"
'/ Filename Strucutre of a Monthly Folder: [parentFolderPath]"Accounts_Monthly_Data-"[Full Month Name][yyyy]"\" - Square Brackets not in filename
'/ Filename Structure of an individual filing: [Monthly Folder Path]"Prod224_0005_"[8-character Company Registration Number]"_"[yyyymmdd][.html OR .xml] - Square Brackets not in filename

I don't see the need for the / slash here. The VBE renders comments in a very distinctive green color: really, you couldn't miss a comment block if you tried:

Why fully qualify Scripting.Dictionary, but not Excel.Range?

Seriously, your code isn't bad at all.

Except I'm not sure I understand the need for AssignArrayBounds, and LB1/UB1 are awful names, as much as ix would be better off as either i or index.

This part can be simplified:

If IsNumeric(trimText) Then
    companyNumber = Right("00000000" & trimText, 8) '/ Add back any leading zeroes that Excel may have stripped

If trimText is numeric, then you can use the Format function to perform the padding:

If IsNumeric(trimText) Then
    companyNumber = Format(trimText, "00000000")

Simple as that!

I'd do away with multiple declarations in a single instruction:

Dim textValue As String, trimText As String
Dim companyNumber As String

textValue = CStr(vValue)
trimText = Trim(textValue)

...and move everything closer to their first usage:

Dim textValue As String
textValue = CStr(vValue)

Dim trimText As String
trimText = Trim$(textValue)

Dim result As String ' clearly the local I'm returning

Notice Trim$ here. Under the hood, Trim$ is really calling the VBA.Strings._B_str_Trim function; on the other hand, Trim is calling the VBA.Strings._B_var_Trim function. One returns a String, the other returns a Variant. By using the Variant variant, you're forcing an implicit conversion to a String, implicit conversion that can completely be avoided by calling the String-returning function in the first place.

The same applies to Right/Right$, respectively VBA.Strings._B_var_Right and VBA.Strings._B_str_Right. Rubberduck's inspections wiki will be updated for the 2.0 release with a full list of all these not-quite-string functions.

StackExchange Code Review Q#126041, answer score: 3