How to pass variable between subs that are stored as array element through CreateObject("Scripting.Dictionary")

[PaulskinX1]

Dear all,

I have the below code which uses Dictonary object [CreateObject("Scripting.Dictionary")] to get the unique string name and number of occurrence of the specific string.

[
Sub CountUniqueStrings()

Dim lastRow As Long
Dim dict As Object
Dim cell As Range
Dim uniqueStrings() As Variant
Dim i As Long
Dim Key As Variant

' Set the worksheet where your data is located
Set wrksh = wsTB

' Find the last row in Column B
lastRow = wrksh.Cells(wrksh.Rows.Count, "E").End(xlUp).Row

' Create a dictionary to store unique strings
Set dict = CreateObject("Scripting.Dictionary")

' Get the unique values - string and occurrence number
For Each cell In wrksh.Range("E2:E" & lastRow)
If Not dict.Exists(cell.Value) Then
dict.Add cell.Value, 1
Else
dict(cell.Value) = dict(cell.Value) + 1
End If
Next cell

' Resize the array to store unique strings and occurrence counts
ReDim uniqueStrings(1 To dict.Count, 1 To 2)

' Populate the array
i = 1
For Each Key In dict.Keys
uniqueStrings(i, 1) = Key
uniqueStrings(i, 2) = dict(Key)
i = i + 1
Next Key


' Example: Print the unique strings and their counts
For i = LBound(uniqueStrings, 1) To UBound(uniqueStrings, 1)
Debug.Print "String: " & uniqueStrings(i, 1) & ", Count: " & uniqueStrings(i, 2)
Next i
End Sub
]



My problem is , I am not sure how to pass the elements of the array uniqueStrings(1 To dict.Count, 1 To 2) to another sub.
I want my another sub to use the first element (string) to create a new sheet and use the 2nd element of the array (number of occurrences) however not sure how to pass those 2 elements between subs.


I tried something like this but to no avail.

(uniqueStrings(i as Integer,S as String),uniqueStrings(i as Integer,S2 as String))

 

[Micron]

Your called procedure would need to be written so as to accept parameters. The parameter declaration needs to be suitably typed (as in data type). Since these values are in an array, then you can't pass 2 parts of the array, as in mySub uniqueStrings(i,1) because that would look like 2 parameters to the called procedure. You'd have to create a variable, pass the array element to it, then pass the variable to the procedure. If you need to pass (i,1) and (i,2) you'll need 2 variables. If you want to process the parameters in the called procedure and return them to the calling sub, the called procedure must be a function.
HTH

Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.