VBA Excel中：添加/删除元素数组 [英] VBA Excel: Adding/Removing Elements to Array

问题描述

我有一个旋转按钮其增加和减少一个给定的阵列的尺寸。现在我需要添加和删除取决于阵列的尺寸元素。我完全失去了现在。任何帮助是AP preciated。

下面是补足的范围与阵列单元的功能：

 私人小组OptionButton4_Click（）随着应用
.ScreenUpdating =假
结束与
昏暗的RNG作为范围
昏暗的细胞作为范围
昏暗的柜台，只要
使用ReDim preserve pwmArray（I）OptionButton4.Height = 26.25
OptionButton4.Width = 87
OptionButton4.Left = 330.75
OptionButton4.Top = 408设置RNG =范围（B2，范围（B2）。偏移量（0，I））
计数器= 0pwmArray（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
如果OptionButton4.Value = TRUE然后
    对于每个单元RNG
    cell.Value = pwmArray（计数器）
    计数器=计数器+ 1
    下一个单元格
万一
随着应用
.ScreenUpdating = TRUE
结束与结束小组
 

下面是两个旋转按钮功能之一：

 私人小组SpinButton2_SpinUp（）昏暗DATACELL由于范围
设置DATACELL =范围（E23）dataCell.Value = dataCell.Value + 1  I = dataCell.Value结束小组
 

解决方案

看样子，你想两个独立的程序连接在一起。换句话说，你要使用微调过程设置的值 I ，你那么想你的选项按钮程序中使用。

我建议做这样的事情：

未测试

 选项基本1私人小组OptionButton4_Click（）
    常量lDefault只要= 31
    呼叫Set_Array_And_Range（lDefault）'＆LT; ~~这个数字将是默认_
                                 细胞/元素个数的时候可以选择_
                                 '按钮pssed $ P $。
    我假设你把这个code中的同一张纸上模块中的旋
    按钮code
    Me.SpinButton2.Value = lDefault'＆LT; ~~复位旋转按钮值
结束小组子Set_Array_And_Range（我只要）    如果I = 0，则退出小组随着应用
    .ScreenUpdating =假
结束与昏暗的RNG作为范围
昏暗的细胞作为范围
昏暗的柜台，只要
昏暗pwmArray（）作为字符串'REDIM阵列。数组没有任何值呢，所以我们不必preserve。
使用ReDim pwmArray（1至I）调整的范围
设置RNG =范围（B2）。调整（0，I）
计数器= 0code填充阵列放在这里...但是你可以使用像_
格雷格发布如果OptionButton4.Value然后
    对于每个单元RNG
        cell.Value = pwmArray（计数器）
        计数器=计数器+ 1
    下一个单元格
万一随着应用
    .ScreenUpdating = TRUE
结束与结束小组这code是工作表模块中，而不是常规的模块。
所以，如果你的按钮在Sheet2上，把这个code中的Sheet2的模块中。
私人小组SpinButton2_SpinUp（）
    呼叫Set_Array_And_Range（Me.SpinButton2.Value）
结束小组
 

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

解决方案

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

这篇关于VBA Excel中：添加/删除元素数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！