I read this while looking for help –
“There’s only two types of programming languages: the ones people complain about, and the ones no one uses.”
I’d like to think it’s true.
I was searching for help on handling error 9 which pops up when referring to an array that isn’t initialised. I was seeing this error when monitoring the MyBets sheet. My code for logging the changes to MyBets works like this –
- On change to MyBets – lift the entire data range into an array (Array1)
- Check this data against previous data lift (Array2) to find any changes
- Log changes to another array (Array3)
- Once Array1 is checked, place Array3 in another sheet
- Save Array1 to Array2 for next time
- Repeat
- On market change – erase Array2
This doesn’t work on the first change to MyBets on a new market as Array2 isn’t initialised. It could be initialised if it was a known size but this can change dependant on other data I add to Array1. I could write code to size the array on each market entry, but I was looking for a quick way of saying – if referencing the array gives an error, load it with the current data and move on. I found a way to do this using the Not operator. The code looks like this –
If Not Not Array2 = 0 Then…
Now, instead of an error when referencing Array2, this “Not Not” will return a 1 or 0, so the code flows and continues unhindered.
Reading around various forums it’s said that this method is actually a bug from VB6, which is what VBA is based on. And some say that this method may disappear if the bug is fixed. I also read that VB6 is no longer maintained, so the chance of the bug being fixed is low.
For me, this works for now so I’ll use it where I need to.
TRADER247 