VBA 中引用/指针的良好替代品? [英] A good substitute for references/pointers in VBA?

问题描述

你能给我推荐一个替代 VBA 中引用或指针类型的好方法吗?我一直在为这样的表达而苦苦挣扎:

Can you recommend me a good substitute for reference or pointer types in VBA? I have been struggling for long with expressions like this:

dblMyArray( i * lngDimension0 + j * lngDimension1 + k * lngDimension2, l * lngDimension3 + m * lngDimension4 ) = dblMyArray( i * lngDimension0 + j * lngDimension1 + k * lngDimension2, l * lngDimension3 + m * lngDimension4 ) + 1

如果我想在多维数组中累积值，例如C++，我可以这样写:

If I wanted to accumulate values in a multidimensional array in e.g. C++, I could write this:

double& rElement = dblMyArray[ i * lngDimension0 + j * lngDimension1 + k * lngDimension2 ][ l * lngDimension3 + m * lngDimension4 ];
rElement += 1;

或

double* pElement = &dblMyArray[ i * lngDimension0 + j * lngDimension1 + k * lngDimension2 ][ l * lngDimension3 + m * lngDimension4 ];
*pElement += 1;

我正在寻找这样的东西.

I am looking for something like this.

我不想重复赋值右侧的元素，也不想调用带有 ByRef 参数的函数，因为这会使代码的维护变得更加困难.

I don't want to repeat the element on the right side of the assignment and I don't want to call a function with ByRef arguments because that would make the maintenance of the code much more difficult.

有什么想法吗?

推荐答案

VBA 支持指针，但仅限于非常有限的范围，并且主要用于需要它们的 API 函数(通过 VarPtr、StrPtr 和 ObjPtr).您可以做一些小技巧来获取数组内存区域的基地址.VBA 将数组实现为 SAFEARRAY 结构，所以第一个棘手的部分是获取数据区的内存地址.我发现这样做的唯一方法是让运行时将数组放入 VARIANT 中，然后将其拆开:

VBA supports pointers, but only to a very limited extent and mostly for use with API functions that require them (via VarPtr, StrPtr, and ObjPtr). You can do a little bit of hackery to get the base address of an array's memory area. VBA implements arrays as SAFEARRAY structures, so the first tricky part is getting the memory address of the data area. The only way I've found to do this is by letting the runtime box the array in a VARIANT and then pulling it apart:

Public Declare Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (Destination As Any, Source As Any, _
    ByVal length As Long)

Private Const VT_BY_REF = &H4000&

Public Function GetBaseAddress(vb_array As Variant) As Long
    Dim vtype As Integer
    'First 2 bytes are the VARENUM.
    CopyMemory vtype, vb_array, 2
    Dim lp As Long
    'Get the data pointer.
    CopyMemory lp, ByVal VarPtr(vb_array) + 8, 4
    'Make sure the VARENUM is a pointer.
    If (vtype And VT_BY_REF) <> 0 Then
        'Dereference it for the variant data address.
        CopyMemory lp, ByVal lp, 4
        'Read the SAFEARRAY data pointer.
        Dim address As Long
        CopyMemory address, ByVal lp, 16
        GetBaseAddress = address
    End If
End Function

第二个棘手的部分是 VBA 没有取消引用指针的本机方法，因此您需要另一个辅助函数来执行此操作:

The second tricky part is that VBA doesn't have a native method to dereference pointers, so you'll need another helper function to do that:

Public Function DerefDouble(pData As Long) As Double
    Dim retVal As Double
    CopyMemory retVal, ByVal pData, LenB(retVal)
    DerefDouble = retVal
End Function

然后你就可以像在 C 中一样使用指针了:

Then you can use the pointer just like you would in C:

Private Sub Wheeeeee()
    Dim foo(3) As Double
    foo(0) = 1.1
    foo(1) = 2.2
    foo(2) = 3.3
    foo(3) = 4.4

    Dim pArray As Long
    pArray = GetBaseAddress(foo)
    Debug.Print DerefDouble(pArray) 'Element 0
    Debug.Print DerefDouble(pArray + 16) 'Element 2
End Sub

这是否是一个好主意或是否比您现在正在做的更好，留给读者作为练习.

Whether or not this is a good idea or is better than what you're doing now is left as an exercise for the reader.

这篇关于VBA 中引用/指针的良好替代品?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！