VBA检查数组是否为一维 [英] VBA check if array is one dimensional

问题描述

我有一个数组（来自SQL），可能有一行或多行。

我想知道数组是否刚刚一行。

UBound似乎没有帮助。对于二维数组 UBound（A，1）和 UBound（A，2）返回行数，列，但是当数组只有一行时， UBound（A，1）返回列数， UBound（A，2）返回一个<下标超出范围> 。

我也看过此Microsoft帮助页面用于确定数组中的维数。这是一个非常可怕的解决方案，涉及使用错误处理程序。

如何确定数组是否只有一行（希望不使用错误处理程序）？ p>

解决方案

如果您真的想避免使用 On Error ，您可以使用知识的 SAFEARRAY 和VARIANT 结构，用于存储阵列下的封装以提取维度信息从实际存储在内存中。将以下内容放在名为 mdlSAFEARRAY

的模块中，

 私有声明Sub CopyMemory Lib kernel32.dll别名RtlMoveMemory（ByVal Destination As Long，ByVal Source As Long，ByVal Length As Integer）
 
私有类型SAFEARRAY 
 cDims As Integer 
 fFeatures As Integer 
 cbElements As Long 
 cLocks As Long 
 pvData As Long 
结束类型
 
私人类型ARRAY_VARIANT 
 vt As Integer 
 wReserved1 As Integer 
 wReserved2 As Integer 
 wReserved3 As Integer 
 lpSAFEARRAY As Long 
 data（4）As Byte 
结束类型
 
私有枚举tagVARENUM 
 VT_EMPTY =& H0 
 VT_NULL 
 VT_I2 
 VT_I4 
 VT_R4 
 VT_R8 
 VT_CY 
 VT_DATE 
 VT_BSTR 
 VT_DISPATCH 
 VT_ERROR 
 VT_BOOL 
 VT_VARIANT 
 VT_UNKNOWN 
 VT_DECIMAL 
 VT_I1 =& H10 
 VT_UI1 
 VT_ UI2 
 VT_I8 
 VT_UI8 
 VT_INT 
 VT_VOID 
 VT_HRESULT 
 VT_PTR 
 VT_SAFEARRAY 
 VT_CARRAY 
 VT_USERDEFINED 
 VT_LPSTR 
 VT_LPWSTR 
 VT_RECORD =& H24 
 VT_INT_PTR 
 VT_UINT_PTR 
 VT_ARRAY =& H2000 
 VT_BYREF =& H4000 
结束枚举
 
公共函数GetDims（VarSafeArray As Variant）As Integer 
 Dim varArray As ARRAY_VARIANT 
 Dim lpSAFEARRAY As Long 
 Dim sArr As SAFEARRAY 
 CopyMemory VarPtr（varArray。 vt），VarPtr（VarSafeArray），16& 
如果varArray.vt和（tagVARENUM.VT_ARRAY或tagVARENUM.VT_BYREF）然后
 CopyMemory VarPtr（lpSAFEARRAY），varArray.lpSAFEARRAY，4& 
如果不是lpSAFEARRAY = 0然后
 CopyMemory VarPtr（sArr），lpSAFEARRAY，LenB（sArr）
 GetDims = sArr.cDims 
 Else 
 GetDims = 0'数组未初始化
结束If 
 Else 
 GetDims = 0'不是数组 - 可能需要一个错误而不是
如果
结束函数
  

这是一个快速测试功能来显示用法：

 code> public Sub testdims（）
 Dim anotherarr（1，2，3）As Byte 
 Dim myarr（）As Long 
 Dim strArr（）As String 
 ReDim myarr（9）
 ReDim strArr（12）
 Debug.Print GetDims（myarr）
 Debug.Print GetDims（anotherarr）
 Debug.Print GetDims（strArr）
 End Sub 
  

I have an array (that comes from SQL) and can potentially have one or more rows.

I want to be able to figure out if the array has just one row.

UBound doesn't seem to be helpful. For 2-dimensional arrays UBound(A,1) and UBound(A,2) returns the number of rows and columns respectively, but when the array has only one row, UBound(A,1) returns the number of columns and UBound(A,2) returns a <Subscript out of range>.

I have also seen this Microsoft help page for determining the number of dimensions in an array. It is a very horrifying solution that involves using the error handler.

How can I determine whether the array has just one row (hopefully without using the error handler)?

解决方案

If you REALLY want to avoid using On Error, you can use knowledge of the SAFEARRAY and VARIANT structures used to store arrays under the covers to extract the dimension information from where it's actually stored in memory. Place the following in a module called mdlSAFEARRAY

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Long, ByVal Source As Long, ByVal Length As Integer)

Private Type SAFEARRAY
    cDims As Integer
    fFeatures As Integer
    cbElements As Long
    cLocks As Long
    pvData As Long
End Type

Private Type ARRAY_VARIANT
    vt As Integer
    wReserved1 As Integer
    wReserved2 As Integer
    wReserved3 As Integer
    lpSAFEARRAY As Long
    data(4) As Byte
End Type

Private Enum tagVARENUM
    VT_EMPTY = &H0
    VT_NULL
    VT_I2
    VT_I4
    VT_R4
    VT_R8
    VT_CY
    VT_DATE
    VT_BSTR
    VT_DISPATCH
    VT_ERROR
    VT_BOOL
    VT_VARIANT
    VT_UNKNOWN
    VT_DECIMAL
    VT_I1 = &H10
    VT_UI1
    VT_UI2
    VT_I8
    VT_UI8
    VT_INT
    VT_VOID
    VT_HRESULT
    VT_PTR
    VT_SAFEARRAY
    VT_CARRAY
    VT_USERDEFINED
    VT_LPSTR
    VT_LPWSTR
    VT_RECORD = &H24
    VT_INT_PTR
    VT_UINT_PTR
    VT_ARRAY = &H2000
    VT_BYREF = &H4000
End Enum

Public Function GetDims(VarSafeArray As Variant) As Integer
    Dim varArray As ARRAY_VARIANT
    Dim lpSAFEARRAY As Long
    Dim sArr As SAFEARRAY
    CopyMemory VarPtr(varArray.vt), VarPtr(VarSafeArray), 16&
    If varArray.vt And (tagVARENUM.VT_ARRAY Or tagVARENUM.VT_BYREF) Then
        CopyMemory VarPtr(lpSAFEARRAY), varArray.lpSAFEARRAY, 4&
        If Not lpSAFEARRAY = 0 Then
            CopyMemory VarPtr(sArr), lpSAFEARRAY, LenB(sArr)
            GetDims = sArr.cDims
        Else
            GetDims = 0  'The array is uninitialized
        End If
    Else
        GetDims = 0  'Not an array - might want an error instead
    End If
End Function

Here is a quick test function to show usage:

Public Sub testdims()
    Dim anotherarr(1, 2, 3) As Byte
    Dim myarr() As Long
    Dim strArr() As String
    ReDim myarr(9)
    ReDim strArr(12)
    Debug.Print GetDims(myarr)
    Debug.Print GetDims(anotherarr)
    Debug.Print GetDims(strArr)
End Sub

这篇关于VBA检查数组是否为一维的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！