VBA Excel: Extending & Shortening Cell Range

Depending on which option button is selected, a different array is loaded into a range of cells (see example below). I want to add the ability to extend and shorten that range of cells (and in turn the array) through a spin button. How would I go about doing that?

Basically what I'm asking: is there a way to add another element to this array and have that be reflected in the range of cells being used?

Add on: there is a graph which represents this range of cells, how would I make that graph's series dynamic? (increase/decrease with the range)

Private Sub OptionButton4_Click()

With Application
.ScreenUpdating = False
End With


Dim rng As Range
Dim cell As Range
Dim counter As Long
OptionButton4.Height = 26.25
OptionButton4.Width = 87
OptionButton4.Left = 330.75
OptionButton4.Top = 408

Set rng = Range("B2", "AF2")
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

Answer

From the code you've posted, it looks like you're using a static array. You need a dynamic array for this. Chip Pearson has a great write up on Arrays. I encourage you to check it out.

So for this scenario, you'd want to do something like this:

Option Base 1 '<~~ This sets the lower bound to 1 instead of the default 0

Sub Main()

Dim pwmArray() As Long

ReDim pwmArray(1 To 31)

'Fill your array with values here

'Now if an element needs to be added to the array (and you want to keep _
' the current values) do the following:
ReDim Preserve pwmArray(1 To 32)
pwmArray(32) = x

End Sub

Note the Preserve keyword. This will keep the values in an array while increasing the size of only the last dimension.

Now, the ReDim statement is performance hungry, so I generally try to only ReDim once if possible in the code (i.e. keeping the ReDim outside of loops when possible).