Erasing Arrays after use question

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
681
Hi,
I have a macro that uses multiple arrays and after use I have a reset button which clears these:
Code:
erase MyArray1
etc...

However in some cases not all arrays are initiated so when I attempt to erase those I get an error as they don't exist.

Is there a way, when erasing them to check if they exist first? Or some way to catch the errors.

Thanks
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Code:
Function IsArrayInitialized(av As Variant) As Boolean
  Dim i      As Long

  On Error Resume Next
  If IsArray(av) Then
    i = UBound(av)
    If Err.Number Then Err.Clear Else IsArrayInitialized = True
  End If
End Function
Then

Code:
If IsArrayInitialized(MyArray1) Then Erase MyArray1
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
681
Code:
Function IsArrayInitialized(av As Variant) As Boolean
  Dim i      As Long

  On Error Resume Next
  If IsArray(av) Then
    i = UBound(av)
    If Err.Number Then Err.Clear Else IsArrayInitialized = True
  End If
End Function
Then

Code:
If IsArrayInitialized(MyArray1) Then Erase MyArray1
Perfect thankyou
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
You're welcome.

More compactly,

Code:
Function IsArrayInitialized(av As Variant) As Boolean
  On Error Resume Next
  IsArrayInitialized = Not IsError(UBound(av))
  Err.Clear
End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,044
Messages
5,466,210
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top