Turning an Array into a String and Vice Versa

[Saladsamurai]

So here's the story:

I have a UserForm that has 48 TextBoxes. I then Loop through the TextBoxes and store each value of the TextBoxes as an element of an Array so long as the TextBox is not empty.

I then want to combine the elements of the Array into a string, use the Replace Function to do some stuff and break up the long string into its constituent elements and place in an array again.


Examplle: The User has entered $, 1, 2, 3, 4, $, $, 5, 6, 7, 8, $ so I now have the array:

Array($, 1, 2, 3, 4, $, $, 5, 6, 7, 8, $)

I would like to make this into a string and remove the "double $" using Replace and then send the elements back to the Array as

Array($, 1, 2, 3, 4, $, 5, 6, 7, 8, $)

Any good functions to do this?

[Bob Phillips]

[vba]

ary = Array("$", 1, 2, 3, 4, "$", "$", 5, 6, 7, 8, "$")
ary = Split(Replace(Join(ary, ","), "$,$", "$"), ",")
[/vba]

[Saladsamurai]

Thanks xld. But remember that the commas don't actually
exist (or do they?). I just put them there to denote that they
are individual elements of an array.

[Bob Phillips]

Try it with your array. Commas separate array elements.

[Saladsamurai]

Thanks!!!

[Saladsamurai]

[vba]

ary = Array("$", 1, 2, 3, 4, "$", "$", 5, 6, 7, 8, "$")
ary = Split(Replace(Join(ary, ","), "$,$", "$"), ",")
[/vba]

Ok. Something fishy is going on with this:

When I MsgBox Replace(Join(Power, ","), "$,$", "$")

The "Double $" has been removed.

But when I add the Split() onto it and then MsgBox Power(i) element-by-element, the "Double $" is back!!! WorkBook attached.

What am I screwing up here?

[vba]Private Sub OKButton_Click()

Dim i As Integer, j As Integer
Dim fPower() As Variant
Dim TextBox As Control
Dim Power() As Variant
ReDim fPower(Controls.Count)
ReDim Power(Controls.Count - 2)
Dim NextRow As Integer
Dim X As String
' Loop Through Controls in PowerForm and Assign Values Inputted from User to Power Array
' The last 2 values must be removed as they are the Boolean values of the 'OK' and 'Cancel'
' Buttons

i = 1
For Each TextBox In Me.Controls

fPower(i) = TextBox
i = i + 1

Next TextBox

j = 1
For i = 1 To (Controls.Count - 2)
If fPower(i) <> "" Then
Power(j) = fPower(i)
j = j + 1
End If
Next i

ReDim Preserve Power(j)

Power(j) = Split(Replace(Join(Power, ","), "$,$", "$"), ",")



For i = 1 To (j - 1)
MsgBox Power(i)
Next i

Unload PowerForm
End Sub

[/vba]
Here is an example of what I am inputting:

[Saladsamurai]

I think that the problem is that I am storing the new array, back into Power(i).

If I define a new array, it seems to work fine EXCEPT that it is not storing it into the new array with Option Base 1. That is, the first element is in NewArray(0).

I don't understand this.
[VBA]Option Explicit
Option Base 1


Private Sub CancelButton_Click()
Unload PowerForm
End Sub

Private Sub CancelButton2_Click()
Unload PowerForm
End Sub

Private Sub OKButton_Click()

Dim i As Integer, j As Integer
Dim fPower() As Variant
Dim TextBox As Control
Dim Power() As Variant
ReDim fPower(Controls.Count)
ReDim Power(Controls.Count - 2)
Dim NextRow As Integer
Dim X As Variant
' Loop Through Controls in PowerForm and Assign Values Inputted from User to Power Array
' The last 2 values must be removed as they are the Boolean values of the 'OK' and 'Cancel'
' Buttons

i = 1
For Each TextBox In Me.Controls

fPower(i) = TextBox
i = i + 1

Next TextBox

j = 1
For i = 1 To (Controls.Count - 2)
If fPower(i) <> "" Then
Power(j) = fPower(i)
j = j + 1
End If
Next i

ReDim Preserve Power(j)

X = Split(Replace(Join(Power, ","), "$,$", "$"), ",")



For i = 0 To (j - 1)
MsgBox X(i)
Next i


NextRow = Worksheets(1).UsedRange.Rows.Count + 1
Unload PowerForm
End Sub
[/VBA]

Do I need to declare "Option Base 1" before EVERY Sub in the Module?

[Bob Phillips]

[vba]

Private Sub OKButton_Click()

Dim i As Integer, j As Integer
Dim fPower() As Variant
Dim TextBox As Control
Dim Power As Variant
ReDim fPower(Controls.Count)
ReDim Power(Controls.Count - 2)
Dim NextRow As Integer
Dim X As String
' Loop Through Controls in PowerForm and Assign Values Inputted from User to Power Array
' The last 2 values must be removed as they are the Boolean values of the 'OK' and 'Cancel'
' Buttons

i = 1
For Each TextBox In Me.Controls

fPower(i) = TextBox
i = i + 1

Next TextBox

j = 1
For i = 1 To (Controls.Count - 2)
If fPower(i) <> "" Then
Power(j) = fPower(i)
j = j + 1
End If
Next i

Power = Split(Replace(Join(Power, ","), "$,$", "$"), ",")

For i = LBound(Power) To UBound(Power)

MsgBox Power(i)
Next i

NextRow = Worksheets(1).UsedRange.Rows.Count + 1
Unload PowerForm
End Sub
[/vba]

[Saladsamurai]

That's Great xld. Was it the syntax of my Split function? i.e. Power(j) = ...

instead of Power = ... ?

Also, what about the Option Base 1 thing? Can I just declare it once per module? Or does it have to be above each Sub ?

[lucas]

saladsamurai, you can edit your own thread at the top of the page under thread tools to remove the solved part. I did it for you on this one.

I deleted the other thread to keep confusion to a minimum.

[Bob Phillips]

The whole use of Power was askew, you were using a single element of it rather than the whole array.

[Saladsamurai]

Lucas: Thanks! I am spoiled at my other Forum where we have a "Marked as Unsolved Button"

The whole use of Power was askew, you were using a single element of it rather than the whole array.

xld: Sorry, I am not quite sure what you mean? Could you elaborate on what it is? And what is askew?

I just want to know to look out for it in the future

EDIT: I might be with you now xld.

But here is what the code is doing that I am not following:

After ... [VBA]Power = Split(Replace(Join(Power, ","), "$,$", "$"), ",")[/VBA]

...the first element in Power() has the index "0" which does not adhere to the Option Base 1 does it?

I thought that if I used Option Base 1, the element Power(0) did not even exist. It should start at Power(1) shouldn't it?

[Bob Phillips]

But here is what the code is doing that I am not following:

After ... [VBA]Power = Split(Replace(Join(Power, ","), "$,$", "$"), ",")[/VBA]

...the first element in Power() has the index "0" which does not adhere to the Option Base 1 does it?

I thought that if I used Option Base 1, the element Power(0) did not even exist. It should start at Power(1) shouldn't it?

You have just discovered why using Option Base is a waste of time, VBA can just ignore it. I always use LBound and UBound and forget Option Base.

[Saladsamurai]

You have just discovered why using Option Base is a waste of time, VBA can just ignore it. I always use LBound and UBound and forget Option Base.

Word. Here is another problem with my code regarding [vba]NextRow = Worksheets("Cold CI ISX").UsedRange.Rows.Count + 1[/vba]
In the following code (which is he same exact code as before except we are now in WorkSheets("Cold CI ISX")

[vba]Option Explicit
Option Base 1

Private Sub OKButton_Click()
Dim i As Integer, j As Integer
Dim fColdCI_ISX() As Variant
Dim TextBox As Control
Dim ColdCI_ISX As Variant

Dim NextRow As Integer
Dim X As String

ReDim fColdCI_ISX(Controls.Count)
ReDim ColdCI_ISX(Controls.Count - 2)
' Loop Through Controls in ColdCI_ISXForm and Assign Values Inputted from User to ColdCI_ISX Array
' The last 2 values must be removed as they are the Boolean values of the 'OK' and 'Cancel'
' Buttons

i = 1
For Each TextBox In Me.Controls

fColdCI_ISX(i) = TextBox
i = i + 1

Next TextBox

j = 1
For i = 1 To (Controls.Count - 2)
If fColdCI_ISX(i) <> "" Then
ColdCI_ISX(j) = fColdCI_ISX(i)
j = j + 1
End If
Next i


ReDim Preserve ColdCI_ISX(j - 1)
ColdCI_ISX = Split(Replace(Join(ColdCI_ISX, ","), "$,$", "$"), ",")

' For i = LBound(ColdCI_ISX) To UBound(ColdCI_ISX)
' MsgBox ColdCI_ISX(i)
' Next i

NextRow = Worksheets("Cold CI ISX").UsedRange.Rows.Count + 1
MsgBox NextRow

' For i = LBound(ColdCI_ISX) To UBound(ColdCI_ISX)
' Worksheets("Cold CI ISX").Cells(NextRow, i + 1) = ColdCI_ISX(i)
' Next i
'

Unload Cold_CI_ISX_Form

End Sub

[/vba]
There is absolutely nothing in Rows 27 - 72 ....NOTHING...but the variable "NextRow" is MsgBox-ing "72"

Dear God why? SAMPLE.xls attached

Is there a better way to assign to the variable "NextRow" the index of the next available empty row?

[tpoynton]

I seem to recall that using 'clear' instead of 'delete' when deleting a selection of rows will have blank rows show up in the usedrange. an alternative, looking at a single column, would be:

[vba]Worksheets("Cold CI ISX").range(cells(1, 2), cells(rows.count, 2).end(xlup)).rows.count + 1[/vba]

[Saladsamurai]

I seem to recall that using 'clear' instead of 'delete' when deleting a selection of rows will have blank rows show up in the usedrange. an alternative, looking at a single column, would be:

[vba]Worksheets("Cold CI ISX").range(cells(1, 2), cells(rows.count, 2).end(xlup)).rows.count + 1[/vba]

That's what I thought, but I have gone and manually 'deleted'
and 'cleared' the rows so I think I am pretty much screwed
here.

Hmmm...