如果元素是某个值 VBA，则删除数组中的元素 [英] Deleting Elements in an Array if Element is a Certain value VBA

问题描述

我有一个全局数组，prLst()，它可以是可变长度的.它将数字作为字符串"1" 到Ubound(prLst).但是，当用户输入 "0" 时，我想从列表中删除该元素.我编写了以下代码来执行此操作:

I have a global array, prLst() that can of variable length. It takes in numbers as strings "1" to Ubound(prLst). However, when the user enters "0", I want to delete that element from the list. I have the following code written to perform this:

count2 = 0
eachHdr = 1
totHead = UBound(prLst)

Do
    If prLst(eachHdr) = "0" Then
        prLst(eachHdr).Delete
        count2 = count2 + 1
    End If
    keepTrack = totHead - count2
    'MsgBox "prLst = " & prLst(eachHdr)
    eachHdr = eachHdr + 1
Loop Until eachHdr > keepTrack

这不起作用.如果元素为 "0"，如何有效地删除数组 prLst 中的元素?

This does not work. How do I efficiently delete elements in the array prLst if the element is "0"?

注意:这是一个更大的程序的一部分，可以在这里找到它的描述:对行组进行排序 Excel VBA 宏

NOTE: This is part of a larger program, for which the description of can be found here: Sorting Groups of Rows Excel VBA Macro

推荐答案

数组是具有一定大小的结构.您可以在 vba 中使用动态数组，您可以使用 ReDim 缩小或增长这些数组，但不能删除中间的元素.从您的示例中不清楚您的数组如何工作或您如何确定索引位置(eachHdr)，但您基本上有 3 个选项

An array is a structure with a certain size. You can use dynamic arrays in vba that you can shrink or grow using ReDim but you can't remove elements in the middle. It's not clear from your sample how your array functionally works or how you determine the index position (eachHdr) but you basically have 3 options

(A) 为您的数组编写一个自定义的删除"函数，例如(未经测试)

(A) Write a custom 'delete' function for your array like (untested)

Public Sub DeleteElementAt(Byval index As Integer, Byref prLst as Variant)
       Dim i As Integer

        ' Move all element back one position
        For i = index + 1 To UBound(prLst)
            prLst(i - 1) = prLst(i)
        Next

        ' Shrink the array by one, removing the last one
        ReDim Preserve prLst(Len(prLst) - 1)
End Sub

(B) 只需设置一个 'dummy' 值作为值而不是实际删除元素

(B) Simply set a 'dummy' value as the value instead of actually deleting the element

If prLst(eachHdr) = "0" Then        
   prLst(eachHdr) = "n/a"
End If

(C) 停止使用数组并将其更改为 VBA.Collection.集合是一个(唯一的)键/值对结构，您可以在其中自由添加或删除元素

(C) Stop using an array and change it into a VBA.Collection. A collection is a (unique)key/value pair structure where you can freely add or delete elements from

Dim prLst As New Collection

这篇关于如果元素是某个值 VBA，则删除数组中的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！