VBA - create unique ID of String / Hash

First of all I want so say sorry for not showing any code but right now I need some guidelines on how to take out a unique ID of a string.

So I have some problems of how to organize data. Lets say that the data is organized so that each dataID has their unique name. I collect the data into a array that holds it.

The problem I now have is that I want a easy way to search for these nameID. Imagine that the data is a lot bigger and contain more than a few hundred of different unique combinations of nameID's. Therefor I do not think searching for the id itself would be appropriate and I'm thinking of creating an hash that I could use an algorithm on to search the array. I want to do this because later on I will compare the names and add the values to the respective nameID. Keep in mind that the nameID will most of the time have the same structure but eventually a new name like total_air could be implemented and then I need to search in the array to get right value.

Updated:

Example of an code that collect the data from excel:

For Each targetSheet In wb.Worksheets
    With targetSheet
    'Populate the array
        xData(0) = Application.Transpose(Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Value2)
        cnt = UBound(xData(0))
    End With
    Call dData.init(cnt)
    'Populate the objectarray
    dData.setNameArray = xData(0)
Next targetSheet

Type object:

Private index As Integer
Private id As String
Private nameID() As Variant
Private data() As Variant
Private cnt As Integer

Public Sub init(value As Integer)
    index = 0
    cnt = value
    id = ""
    ReDim nameID(0 To cnt)
    ReDim data(0 To cnt)
End Sub

Property Let setID(value As String)
    id = value
End Property

Property Let setNameArray(value As Variant)
    nameID = value
End Property

dList that inherit the dataStruct:

Private xArray() As dataStruct
Private listInd As Integer

Public Sub init(cnt As Integer)
    ReDim xArray(1 To cnt)
    Dim num As Integer
    For num = 1 To cnt
        Set xArray(num) = New dataStruct
    Next
    listInd = 1
End Sub
Property Let addArray(value As dataStruct)
    Set xArray(listInd) = value
    listInd = listInd + 1
End Property

How the hole list will look like:

I would strongly advocate using a dictionary. Not only is it much faster to find an item (I would assume that it is implemented with some kind of hashing), it has big advantages when it comes to adding or removing items.

When you have an array and want to add an item, you either have always to use redim preserve which is really expensive, or you define the array larger than initially needed and always have to keep the information how many items are really used. And deleting an item from an array is rather complicated.

You cannot add a typed variable as item value into a dictionary, but you can add a object. So instead of your Type definition, create a simple class module, containing only these lines (of course you can create the class with properties, getter and setter but that's irrelevant for this example)

Public id As Long
Public name As String
Public value As Long

Then, dealing with the dictionary is rather simple (note that you have to add a reference to the Microsoft Scripting Runtime

Option Explicit

Dim myList As New Dictionary

Sub AddItemValues(id As Long, name As String, value As Long)
    Dim item As New clsMyData
    With item
        .id = id
        .name = name
        .value = value
    End With
    Call AddItem(item)
End Sub

Sub AddItem(item As clsMyData)
    If myList.Exists(item.id) Then
        set myList(item.id) = item
    Else
        Call myList.Add(item.id, item)
    End If
End Sub

Function SearchItem(id As Long) As clsMyData
    If myList.Exists(id) Then
        Set SearchItem = myList(id)
    Else
        Set SearchItem = Nothing
    End If
End Function

Function SearchName(name As String) As clsMyData
    Dim item As Variant
    For Each item In myList.Items
        If item.name = name Then
            Set SearchName = item
            Exit Function
        End If
    Next item
    Set SearchName = Nothing
End Function

So as long as you deal with Id's, the dictionary will do all the work for you. Only if you search for the name, you have to loop over all items of the dictionary, which is as easy as looping over an array.

Some test (of course you should add some error handling)

Sub test()
    Call AddItemValues(32, "input_air", 0)
    Call AddItemValues(45, "air_Procent", 99)
    Call AddItemValues(89, "output_air", 34)

    Debug.Print SearchItem(45).name
    Debug.Print SearchName("output_air").value

    ' Change value of output_air
    Call AddItemValues(89, "output_air", 1234)
    Debug.Print SearchName("output_air").value
End Sub