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_NUMBER_COLUMN).End(xlUp).row
        Set columnRange = .Range(.Cells(2, COMPANY_NUMBER_COLUMN), .Cells(finalRow, COMPANY_NUMBER_COLUMN))
    End With

    Dim columnArray As Variant
    columnArray = columnRange

    Dim LB1 As Long, UB1 As Long
    AssignArrayBounds columnArray, LB1, UB1

    Dim ix As Long
    Dim elementValue As Variant
    Dim companyNumber As String

    For ix = LB1 To UB1
        elementValue = columnArray(ix, 1)
        companyNumber = CompanyNumberFromValue(elementValue)
        If Not targetCompanyNumbers.Exists(companyNumber) Then targetCompanyNumbers.Add companyNumber, companyNumber
    Next ix

    Set GetTargetCompanyNumbers = targetCompanyNumbers 

End Function

Public Function CompanyNumberFromValue(ByVal vValue As Variant) As String
    '/ Company Number: 8-character string, generally 8-digits but sometimes with text prefixes E.G. "OC374102"

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

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

    If IsNumeric(trimText) Then
        companyNumber = Right("00000000" &amp; trimText, 8) '/ Add back any leading zeroes that Excel may have stripped
    ElseIf Len(trimText) = 8 Then
        companyNumber = trimText
    Else
        '/ Not a number, not the correct number of digits. Invalid input.
        companyNumber = vbNullString
    End If

    CompanyNumberFromValue = companyNumber

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

'/ Folder Path for monthly CH downloads: &quot;S:\Investments\Data\Companies House\Monthly Companies House Downloads\&quot;
'/ Filename Strucutre of a Monthly Folder: [parentFolderPath]&quot;Accounts_Monthly_Data-&quot;[Full Month Name][yyyy]&quot;\&quot; - Square Brackets not in filename
'/ Filename Structure of an individual filing: [Monthly Folder Path]&quot;Prod224_0005_&quot;[8-character Company Registration Number]&quot;_&quot;[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(&quot;00000000&quot; &amp; 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, &quot;00000000&quot;)

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.