Excel VBA combining variable with array to create new Array of Strings

Question

I have an array of Dates. In my code, I extract the array of dates from a column in a sheet which has date data, drop the unique values using scripting dictionary.

arrayIDAll = WorksheetFunction.Transpose(Sheets(2).Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Value)



Dim dc As Object

Set dc = CreateObject("Scripting.Dictionary")


For i = LBound(arrayDateAll) To UBound(arrayDateAll)
   If Not dc.Exists(arrayDateAll(i)) Then
      dc.Add arrayDateAll(i), i
   End If
      If Not dcID.Exists(arrayIDAll(i)) Then
      dcID.Add arrayIDAll(i), i
   End If
Next i

Array1 = dc.Keys()

So lets say that Array1 was like below:

 Array1 = Array(1/1/2012, 6/1/2012, 1/1/2013)

I have two string variables

Name1 = "Weight"
Name2 = "Cholesterol"

I want to create an array of Strings that looks like this:

   ArrayDateNames= Array("1/1/2012_Weight","6/1/2012_Weight","1/1/2013_Weight","1/1/2012_Cholesterol","6/1/2012_Cholesterol","1/1/2013_Cholesterol")

The code I am trying is not working

Sub combinearray()

      Dim arr As Variant, arr2 As Variant



      arr = Array("1 / 1 / 2012", "6 / 1 / 2012", "1 / 1 / 2013")

      ReDim Preserve arr(1 To 3)

      Name1 = "Weight"

      Name2 = "Cholesterol"

      ReDim arr2(1 To 2 * UBound(arr))

      For i = 1 To 2 * UBound(arr)

            If (i < 4) Then
                  arr2(i) = CStr(arr(i)) & "_" & Name1
            Else
                  arr2(i) = CStr(arr(i - 3)) & "_" & Name2
            End If

            Debug.Print arr2(i)
      Next i




End Sub

The debugger is giving me stuff like:

1 / 1 / 2012_Weight
6 / 1 / 2012_Weight
1 / 1 / 2013_Weight
1 / 1 / 2012_Cholesterol
6 / 1 / 2012_Cholesterol
1 / 1 / 2013_Cholesterol
2.98210735586481E-03Weight
4.96770988574267E-04Weight
4.97017892644135E-04Cholesterol
2.98210735586481E-03Cholesterol
4.96770988574267E-04Cholesterol

 2.98210735586481E-03 
 4.96770988574267E-04

Also, in this MWE I am entering dates in quotes but my actual array has date data. I am concerned that that might mess up the string concatenation.

Bernard Saucier · Accepted Answer · 2014-01-09 17:10:03Z

1

On my system, your code executes perfectly with the debugger output :

1 / 1 / 2012_Weight
6 / 1 / 2012_Weight
1 / 1 / 2013_Weight
1 / 1 / 2012_Cholesterol
6 / 1 / 2012_Cholesterol
1 / 1 / 2013_Cholesterol

Maybe if you try : For i = 1 To UBound(arr2) instead of For i = 1 To 2 * UBound(arr)

answered Jan 9, 2014 at 17:10

Bernard Saucier

2,2601 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amatya Over a year ago

Ok that did it! Thanks! I'll keep the question open in case someone suggests more efficient way for constructing the array.

Sorceri Over a year ago

@amatya If BernardSaucier answered your original question then mark it answered and create a new one for Optimizing your array. Unfair to Benard to supply you the answer and you not accept it.

Amatya Over a year ago

@Sorceri Ok, I'll form another question for optimizing the array.

Collectives™ on Stack Overflow

Excel VBA combining variable with array to create new Array of Strings

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related