Accessing elements within arrays

**JP789** · 11-11-2009, 12:35 AM

Hi All

I've been teaching myself VBA for the purpose of processing some time resolved data from an instrument. I've come up against a problem and I just can't work out why the code is failing. (using excel 2007, VBA 6.5)

I declare some arrays as dynamic, then ReDim them to a user defined number (with Option Base 1) and input data from a spreadsheet into the array. I can then output the data into the spreadsheet again and the lbound and ubound functions return the correct values for the array. What I cannot do is actually access individual elements. If I specify array(i) I get the "Subscript is out of range" error.

The code relevant to this problem is as below (simplified as there are more arrays):

Please Login or Register  to view this content.

etc,etc

(The arrays in this example have 854 lines. so 1-854 are the bounds)

I have tried different methods for basically everything. Including ReDiming using only rowcount in brackets, not using y, using a For each loop. I tried just putting an integer in (e.g. an171rw(1)) and that has the same result.

I'm sure it is something simple but I just cannot figure out the problem.

Thanks for any help

**DonkeyOte** · 11-11-2009, 07:34 AM

Do you have error handlers in place anywhere ?

I ask because I'm curious as to why your code is not falling over earlier on - ie upon population in first instance.... by my reckoning this

Please Login or Register  to view this content.

should fail in it's own right (even if we assume RowCount variable to be legitimate value) ... ie I would think you would want to use

Please Login or Register  to view this content.

**JP789** · 11-11-2009, 06:12 PM

Thanks for your input DonkeyOte.

That is a no to the error handlers. What would you suggest using? As I said I'm only just learning this, so no doubt my work is full of bad habits and I know I have a lot to learn.

I have basically just been running the code with breakpoints and looking at the values that are assigned to variables in the code (and the output to the spreadsheet).

should fail in it's own right (even if we assume RowCount variable to be legitimate value) ... ie I would think you would want to use

I think you are correct here. I modified the code using your suggestion and the For Next Loop ran succesfully. However the an171rw array is populated only with 1s as opposed to the data in the spreadsheet. I also tried going back to the method I used for declaring the array and ReDiming it and that still produced the same result. That presumably means the fault is in the population of the array? Is there a further modification I can make to that?

For extra info the code for the Rowcount and cell values is:

Please Login or Register  to view this content.

I wasn't sure about declaring the BkStCell, etc as a string but it seemed to work okay and the values it returns work when assigning a range.

Thanks in advance again.

**JP789** · 11-11-2009, 09:01 PM

Success. Despite my poor VBA skills. Thanks muchly to Donkeyote

After some more plugging away after my last post this morning I discovered that Donkeyote's suggestion correctly populated the array (by running the code with breakpoints). The problem had become that my print to worksheet lines were not working properly after I changed the input method to the arrays. They now read:

Please Login or Register  to view this content.

which works. I guess they worked previously because I was basically just storing a range in the array and then outputting it again without properly assigning each element??? Changing the input meant that the two conflicted and the code only output the first value in the array (a 1 for this test data).

(From my last post I also changed string to integer. I think string was a remnant from my first thinkings about how to do this)

If people have the time or inclination, I would be interested to hear more about ways I could improve error detection with error handlers for this code. Or a good place to look for info on this. The project is still a long way from complete and any help would be appreciated.

Cheers JP

**DonkeyOte** · 11-12-2009, 06:06 AM

If you would like people to review your code then I'm afraid you will need to post in full - unfortunately working in piecemeal often leads to misinterpretation.

On an aside going back to an early point, quoting your code:

Please Login or Register  to view this content.

and subsequently your point:

Originally Posted by JP789

I wasn't sure about declaring the BkStCell, etc as a string but it seemed to work okay and the values it returns work when assigning a range.

In VBA each variable must be declared explicitly, in your code above:

AnEd is declared as Double, all others on that line are Variants

AnEdCell is declared as String, all others on that line are Variants

So for BkStCell etc to be Strings you would need to use

Please Login or Register  to view this content.

The Variant type can hold anything.

Were BkStCell a String rather than having been a Variant it would still have worked based on what we can see... ie

Please Login or Register  to view this content.

would set the String to be a Number Stored as Text, and subsequent math operations would simply have coerced the string to a number, eg:

Please Login or Register  to view this content.

would work given the * operator would coerce BkStCell from String to Number.

To reiterate though, as you can tell from the posts thus far, it's quite difficult given exact advice when we're unable to see the full picture - and / or are unaware as to the nature of the future use of a multitude of given variables etc ...
the more info we have the more likely we are to be able to impart (potentially) useful assistance

**JP789** · 11-17-2009, 08:47 AM

Thanks muchly Donkeyote. The individual declaration is obviously good to know. Apologies for the delayed response.

I appreciate that only seeing parts of the code is not particularly easy. Unfortunately the relevant code is spread across a number of modules with some userforms thrown in. Although I'm sure it's not a particularly complicated project code-wise the nature of it is more conducive to providing the file as a whole. I would prefer not to post the file on the forum which of course creates the problems.

Thanks
JP