VBA empty array error

Question

I am writing a script that will lead an Excel spreadsheet through me and find out if there are duplicates of the selected cells. if there are duplicates, then the function will return an array of which the rows are duplicates, and create a comment, tell me which rows are duplicates. I was able to handle error 0, but now I get error 9 when I check the array if there are elements in it using the UBound function. Does anyone know how to check an array of integers, if its empty or does not call my code, it does not seem to do the job. Below is my code

Function IsArrayEmpty(anArray As Variant) As Boolean Dim i As Integer On Error Resume Next i = UBound(anArray, 1) Select Case (Err.Number) Case 0 IsArrayEmpty = True Case 9 IsArrayEmpty = True Case Else IsArrayEmpty = False End Select End Function 

Answer 1

Your function does not work, because if there is no error caused by UBound() (i.e. the size of the array), then Err.Number is 0 and:

 Case 0 IsArrayEmpty = True 

Returns an invalid result.

The easiest way is to simply catch the error:

 Function IsArrayEmpty(anArray As Variant) As Boolean On Error GoTo IS_EMPTY If (UBound(anArray) >= 0) Then Exit Function IS_EMPTY: IsArrayEmpty = True End Function 

Answer 2

Try checking an empty array:

 Dim arr() As String If (Not arr) = -1 Then Debug.Print "empty" Else Debug.Print "UBound is " & UBound(X) End If 

NTN!

Answer 3

Is your array variant empty or empty ()?

"Empty" is an uninitialized option: IsEmpty (myVar) will return true ... And you can be fooled into thinking that you have an empty array (which is "Empty ()" and not "Empty" - try to keep after this class will be conducted a short test), because IsEmpty (myArray) also returns True.

 Dim myVar as Variant 'this is currently Empty, and Ubound returns an error 

 Dim myArray () as variant 'this is currently Empty (), and Ubound returns an error  

Redim myVar (0 to 0) ', it is no longer empty and has a valid Ubound Redim myVray (0 to 0)', it is no longer empty and has a valid Ubound

A reliable way to check myVar is TypeName (myVar) - if it is an array, the name contains brackets:

  
 If Instr (Typename (myVar), "(")> 0 then  

     'we now know it is an array  
     If Not IsEmpty (myVar) Then  

        'We can now check its dimensions  
         If Ubound (myVar)> 0  
              'insert error-free code here  
         Endif  

     Endif  

 Endif  

Full answer: "Define array variant in Excel VBA" on Excellerando page.

Answer 4

.. I still get error # 9 script out of bounds

if you receive an error message number 9 .... does not mean that you get the information you need (the array is empty)?

Answer 5

You can check if the array is empty by counting the total number of elements using the JScript VBArray() object (works with variant type arrays, single or multidimensional):

 Sub Test() Dim a() As Variant Dim b As Variant Dim c As Long ' Uninitialized array of variant ' MsgBox UBound(a) ' gives 'Subscript out of range' error MsgBox GetElementsCount(a) ' 0 ' Variant containing an empty array b = Array() MsgBox GetElementsCount(b) ' 0 ' Any other types, eg Long or not Variant type arrays MsgBox GetElementsCount(c) ' -1 End Sub Function GetElementsCount(aSample) As Long Static oHtmlfile As Object ' instantiate once If oHtmlfile Is Nothing Then Set oHtmlfile = CreateObject("htmlfile") oHtmlfile.parentWindow.execScript ("function arrlength(arr) {try {return (new VBArray(arr)).toArray().length} catch(e) {return -1}}"), "jscript" End If GetElementsCount = oHtmlfile.parentWindow.arrlength(aSample) End Function 

It takes me about 0.3 microseconds for each element + initialization of 15 ms, so an array of 10M elements takes about 3 seconds. The same functionality can be implemented through ScriptControl ActiveX (it is not available in 64-bit versions of MS Office, so you can use a workaround, for example this ).