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

查看:1791
本文介绍了如果元素是特定值VBA,则删除数组中的元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

Do
如果prLst(eachHdr)=0,那么
prLst(eachHdr).Delete
count2 = count2 + 1
End If
keepTrack = totHead - count2
'MsgBoxprLst =& prLst(eachHdr)
eachHdr = eachHdr + 1
循环直到eachHdr> keepTrack

这不行。如果元素是0

$ b,如何有效地删除数组 prLst 中的元素
$ b


注意:这是一个较大的程序的一部分,可以在这里找到描述:排序行Excel VBA宏

解决方案

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



(A)编写一个自定义的删除'对于你的数组的函数(未经测试)

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

'将所有元素移回一个位置
对于i = index + 1 To UBound(prLst)
prLst(i - 1)= prLst(i)
下一个

'将数组缩小一个,删除最后一个
ReDim Preserve prLst(Len(prLst) - 1)
End Sub

(B)只需将虚拟值设置为值,而不是实际删除元素

 如果prLst(eachHdr)=0则
prLst(eachHdr)=n / a
如果$ b $ (C)停止使用数组并将其更改为VBA.Collection。一个集合是一个(唯一的)键/值对结构,您可以在其中自由添加或删除

  Dim prLst As New Collection 


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

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


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

解决方案

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) 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) 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) 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆