VBA Excel: Adding/Removing Elements to Array

Question

I have a spin button which increases and decreases the dimensions of a given array. Now I need to add and remove elements depending on the dimensions of the array. I'm totally lost right now. Any help is appreciated.

Here is the function which fills a range of cells with the array:

Private Sub OptionButton4_Click()

With Application
.ScreenUpdating = False
End With


Dim rng As Range
Dim cell As Range
Dim counter As Long
ReDim Preserve pwmArray(i)

OptionButton4.Height = 26.25
OptionButton4.Width = 87
OptionButton4.Left = 330.75
OptionButton4.Top = 408

Set rng = Range("B2", Range("B2").Offset(0, i))
counter = 0

pwmArray(0) = "0"
pwmArray(1) = "10"
pwmArray(2) = "0"
pwmArray(3) = "10"
pwmArray(4) = "10"
pwmArray(5) = "0"
pwmArray(6) = "10"
pwmArray(7) = "10"
pwmArray(8) = "10"
pwmArray(9) = "0"
pwmArray(10) = "10"
pwmArray(11) = "10"
pwmArray(12) = "10"
pwmArray(13) = "10"
pwmArray(14) = "0"
pwmArray(15) = "10"
pwmArray(16) = "10"
pwmArray(17) = "10"
pwmArray(18) = "10"
pwmArray(19) = "10"
pwmArray(20) = "0"
pwmArray(21) = "10"
pwmArray(22) = "10"
pwmArray(23) = "10"
pwmArray(24) = "10"
pwmArray(25) = "10"
pwmArray(26) = "10"
pwmArray(27) = "0"
pwmArray(28) = "0"
pwmArray(29) = "0"
pwmArray(30) = "0"


If OptionButton4.Value = True Then
    For Each cell In rng
    cell.Value = pwmArray(counter)
    counter = counter + 1
    Next cell
End If
With Application
.ScreenUpdating = True
End With

End Sub

Here is one of the two spin button functions:

Private Sub SpinButton2_SpinUp()

Dim dataCell As Range
Set dataCell = Range("E23")

dataCell.Value = dataCell.Value + 1

  i = dataCell.Value

End Sub

Answer 1

The Redim will remove any excess elements. I'm guessig you're looking to repopulate the remaining elements(whether it be more or less) after the redim, using the pattern you've shown. The below code should do this for you.

Dim k As Long
Dim j As Long
Dim tens As Long
k = -1
j = 0
Do While j <= UBound(pwmArray)
    For tens = 0 To k
        If j <= UBound(pwmArray) Then
            pwmArray(j) = "10"
            j = j + 1
        End If
    Next
    k = k + 1
    If j <= UBound(pwmArray) Then
        pwmArray(j) = "0"
        j = j + 1
    End If
Loop

Answer 2

It appears that you're trying to connect two independent procedures together. In other words, you're trying to use the spinner procedure to set the value of i which you then want to use in your option button procedure.

I'd suggest doing something like this:

Untested

Option Base 1

Private Sub OptionButton4_Click()
    Const lDefault As Long = 31
    Call Set_Array_And_Range(lDefault) '<~~ this number would be the default _
                                 ' number of cells/elements when the option _
                                 ' button is pressed.
    'I'm assuming you'll place this code in the same sheet module as the spin
    'button code
    Me.SpinButton2.Value = lDefault '<~~ Resets the spin button value
End Sub

Sub Set_Array_And_Range(i As Long)

    If i = 0 Then Exit Sub

With Application
    .ScreenUpdating = False
End With

Dim rng As Range
Dim cell As Range
Dim counter As Long
Dim pwmArray() As String

'ReDim the array. The array doesn't have any values yet, so we don't have to Preserve.
ReDim pwmArray(1 To i)

'Resize the range
Set rng = Range("B2").Resize(0, i)
counter = 0

'Code for filling your array goes here... but you can use something like _
'Greg posted

If OptionButton4.Value Then
    For Each cell In rng
        cell.Value = pwmArray(counter)
        counter = counter + 1
    Next cell
End If

With Application
    .ScreenUpdating = True
End With

End Sub

'This code is in the worksheet module, not a regular module.
'So if your button is on sheet2, put this code in the Sheet2 module.
Private Sub SpinButton2_SpinUp()
    Call Set_Array_And_Range(Me.SpinButton2.Value)  
End Sub