Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

check if array is empty (vba excel)

Tags:

excel

vba

These if ... then statements are getting the wrong results in my opinion. The first is returning the value 'false' when it should be 'true'. The fourth returns the right value. The second and third return an error.

Sub empty_array()
  Dim arr1() As Variant

  If IsEmpty(arr1) Then
    MsgBox "hey"
  End If

  If IsError(UBound(arr1)) Then
    MsgBox "hey"
  End If

  If IsError(Application.match("*", (arr1), 0)) Then
    MsgBox "hey"
  End If

  ReDim arr1(1)
  arr1(1) = "hey"

  If IsEmpty(arr1) Then
    MsgBox "hey"
  End If
End Sub
like image 388
user147178 Avatar asked Oct 10 '14 01:10

user147178


People also ask

How do you check if an array is empty in Excel?

Creating Function to Check if an Array is Empty in VBA This function, called Array_Empty with one parameter (var as a variant) will check every value in our array and give us the results: 1) False if any value if all of the values in our array are populated and 2) True if our array is empty.

How check array is empty in VB net?

Solution 1WriteLine("array is not initialized"); else if (myArray. Length < 1) System. Console. WriteLine("array is empty");

Is Empty check in VBA?

If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function. In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.


2 Answers

Arr1 becomes an array of 'Variant' by the first statement of your code:

Dim arr1() As Variant

Array of size zero is not empty, as like an empty box exists in real world.

If you define a variable of 'Variant', that will be empty when it is created.

Following code will display "Empty".

Dim a as Variant

If IsEmpty(a) then
  MsgBox("Empty")
Else
  MsgBox("Not Empty")
End If
like image 85
Fumu 7 Avatar answered Sep 20 '22 14:09

Fumu 7


Adding into this: it depends on what your array is defined as. Consider:

dim a() as integer
dim b() as string
dim c() as variant

'these doesn't work
if isempty(a) then msgbox "integer arrays can be empty"
if isempty(b) then msgbox "string arrays can be empty"

'this is because isempty can only be tested on classes which have an .empty property

'this do work
if isempty(c) then msgbox "variants can be empty"

So, what can we do? In VBA, we can see if we can trigger an error and somehow handle it, for example

dim a() as integer
dim bEmpty as boolean

bempty=false

on error resume next
bempty=not isnumeric(ubound(a))
on error goto 0

But this is really clumsy... A nicer solution is to declare a boolean variable (a public or module level is best). When the array is first initialised, then set this variable. Because it's a variable declared at the same time, if it loses it's value, then you know that you need to reinitialise your array. However, if it is initialised, then all you're doing is checking the value of a boolean, which is low cost. It depends on whether being low cost matters, and if you're going to be needing to check it often.

option explicit

'declared at module level
dim a() as integer
dim aInitialised as boolean

sub DoSomethingWithA()
if not aInitialised then InitialiseA
'you can now proceed confident that a() is intialised
end sub

sub InitialiseA()
'insert code to do whatever is required to initialise A
'e.g. 
redim a(10)
a(1)=123
'...
aInitialised=true
end sub

The last thing you can do is create a function; which in this case will need to be dependent on the clumsy on error method.

function isInitialised(byref a() as variant) as boolean
isInitialised=false
on error resume next
isinitialised=isnumeric(ubound(a))
end function
like image 25
jeminar Avatar answered Sep 18 '22 14:09

jeminar