How to determine an array is empty in VBA
-
I'm writting VBA macro code with array start index as 1 rather 0 Option Base 1 Dim array() As String If()Then ReDim array(5) 'fill array with elements Else ReDim array(0) 'This statement gives error End If I try to use UBound() and LBound() to determine if the array is empty, but can't make it work. Someone please tell me if there is other way to make it work.
-
I'm writting VBA macro code with array start index as 1 rather 0 Option Base 1 Dim array() As String If()Then ReDim array(5) 'fill array with elements Else ReDim array(0) 'This statement gives error End If I try to use UBound() and LBound() to determine if the array is empty, but can't make it work. Someone please tell me if there is other way to make it work.
You can't ReDim an array down to 0 when the Option Base is 1. The lowest you can ReDim down to, obviously, is 1. I don't know what you mean by "if the array is empty". It appears as though you're trying to ReDim a variable that was an array to no longer be an array. You can't do that. Once a variable is declared, you cannot change it's type. If it's declared as an Integer or Long, it has to stay an Integer or Long. You can't ReDim it to make it an array of Longs. The opposite is also true. Once delcared as an array, it must stay as an array. You can't ReDim it down to a single-valued variable. I think the function you're looking for is
IsArray(_var_). This will return True or False depending on if the _var_ is an array or not. **Dave Kreskowiak _Microsoft MVP - Visual Basic_** -
You can't ReDim an array down to 0 when the Option Base is 1. The lowest you can ReDim down to, obviously, is 1. I don't know what you mean by "if the array is empty". It appears as though you're trying to ReDim a variable that was an array to no longer be an array. You can't do that. Once a variable is declared, you cannot change it's type. If it's declared as an Integer or Long, it has to stay an Integer or Long. You can't ReDim it to make it an array of Longs. The opposite is also true. Once delcared as an array, it must stay as an array. You can't ReDim it down to a single-valued variable. I think the function you're looking for is
IsArray(_var_). This will return True or False depending on if the _var_ is an array or not. **Dave Kreskowiak _Microsoft MVP - Visual Basic_**Hi, Dave, I declared an array, then under certain conditions, this array will be filled with elements, otherwise it will stay empty. After get through the condition statements, I need to determine if the array is empty. I tried to use UBound() and LBound(), but I receive compile error on it when the array is not ReDim and filled with elements. what is the best way to determine if the array is empty? Thanks Vicky
-
Hi, Dave, I declared an array, then under certain conditions, this array will be filled with elements, otherwise it will stay empty. After get through the condition statements, I need to determine if the array is empty. I tried to use UBound() and LBound(), but I receive compile error on it when the array is not ReDim and filled with elements. what is the best way to determine if the array is empty? Thanks Vicky
"Empty" can be different things. If the variable is not declared an array, or if the variable is declared an array but has no elements, and it can be declared an array and have empty elements. It depends on the call that's creating the array. You just have to test it to find out. IsArray will tell you if the variable is declared as an array, but that's it. If that test passes, then you can check its UBound. If it's greater than 0, the array has elements. After that, you check the value of the first element to see if that value is valid.
Dave Kreskowiak Microsoft MVP - Visual Basic
-
I'm writting VBA macro code with array start index as 1 rather 0 Option Base 1 Dim array() As String If()Then ReDim array(5) 'fill array with elements Else ReDim array(0) 'This statement gives error End If I try to use UBound() and LBound() to determine if the array is empty, but can't make it work. Someone please tell me if there is other way to make it work.