VBA - Is there any alternative to the array() function to create a empty array in vba?

Question

After August 2019 Windows update there is a problem using the array() function in VBA.

Is there any other way to create an empty array in VBA for the purpose "Using multi value combobox on a form"?

The following statement to clear/delete all the selections:

me.cmbMultivalue=Array()

Answer 1

The array returned by Array() is not just an uninitialized array. It's an initialized array with a lower bound of 0 and an upper bound of -1, thus containing 0 elements. This is distinct from normal, uninitialized arrays, which don't have a lower and upper bound.

You can roll your own array function (which I often do for non-variant arrays).

For a variant array, it's really easy. Just take an input ParamArray, and assign that to a variant array:

Public Function altArray(ParamArray args() As Variant) As Variant()
    altArray = args
End Function

Then, you can use altArray() to get your special 0-element array.

However, I'm not sure this is also bugged for that specific version of Access. If it is, we can always create a 0-element array using WinAPI (slightly adapted version of this answer):

Public Type SAFEARRAYBOUND
    cElements As Long
    lLbound As Long
End Type
Public Type tagVariant
    vt As Integer
    wReserved1 As Integer
    wReserved2 As Integer
    wReserved3 As Integer
    pSomething As LongPtr
End Type

Public Declare PtrSafe Function SafeArrayCreate Lib "OleAut32.dll" (ByVal vt As Integer, ByVal cDims As Long, ByRef rgsabound As SAFEARRAYBOUND) As LongPtr
Public Declare PtrSafe Sub VariantCopy Lib "OleAut32.dll" (pvargDest As Any, pvargSrc As Any)
Public Declare PtrSafe Sub SafeArrayDestroy Lib "OleAut32.dll" (ByVal psa As LongPtr)

Public Function CreateZeroLengthArray() As Variant
    Dim bounds As SAFEARRAYBOUND 'Defaults to lower bound 0, 0 items
    Dim NewArrayPointer As LongPtr 'Pointer to hold unmanaged variant array
    NewArrayPointer = SafeArrayCreate(vbVariant, 1, bounds)
    Dim tagVar As tagVariant 'Unmanaged variant we can manually manipulate
    tagVar.vt = vbArray + vbVariant 'Holds a variant array
    tagVar.pSomething = NewArrayPointer 'Make variant point to the new variant array
    VariantCopy CreateZeroLengthArray, ByVal tagVar 'Copy unmanaged variant to managed return variable
    SafeArrayDestroy NewArrayPointer 'Destroy the unmanaged SafeArray, leaving the managed one
End Function

Answer 2

Alternative to clear all values from a multi-value field is with SQL. Example:

CurrentDb.Execute "DELETE Table1.Test.Value FROM Table1 WHERE ID = 1"

Answer 3

When you declare a new array, it is still an empty array. i.e. Dim x() As Variant

(1) As you mention in your question, your goal is to clear combobox values by assigning an empty array to it, it seems like this would work:

Dim EmptyArray() As Variant
Me.cmbMultivalue = EmptyArray

(2) Or if that doesn't work, assuming that Me.cmbMultivalue behaves like a regular array, the following would work:

Erase Me.cmbMultivalue

EDIT:

(3) Another possible workaround similar to (1) would be to create a non-empty array and then erase it as such:

Dim x() As Variant
x = Array(1)
Erase x

You could then use x as an empty array.

If all that fails and, as you mentioned, assigning the value Null or vbEmpty didn't work, it seems like your only options would be to revert the problematic Windows update or hope Microsoft can fix this quickly.