Streamlining settings...by array?

**beeawwb** · 06-03-2008, 11:17 PM

Hi all,

Just interested in a bit of discussion at the moment. I've got a portion of my code which reads settings (Variable = Cell) to store them for later use.

Problem is, I've defined each variable 1 at a time. Now, that's all well and good, but I'm sure there must be a better, more streamlined way of doing things.

The flow on effect is, if I'm able to streamline this section of code, I can likely turn some other sections into arrays as well.

The thought that did occur to me would be to store the cell rows / columns in an array, and then use that to get the cell positions...

E.g.

Please Login or Register  to view this content.

The actual code I'm currently using is...

Please Login or Register  to view this content.

Any thoughts / comments are greatly appreciated.

-Bob

**Andy Pope** · 06-04-2008, 05:37 AM

First a common gotcha in your code.
Only the last item, DISCODE , is a string variable the rest are variant
For them all to be string varaibles you would need to add As String to each one.

Please Login or Register  to view this content.

You could load your range directly into a variant array, like this,
Although the locations of your data would mean an array where only certain elements are actually used.

[code]
Sub x()

Dim vntData As Variant
Dim lngIndex As Long

vntData = Range("A1:A7")
For lngIndex = 1 To 7
Debug.Print vntData(lngIndex, 1)
Next

End Sub
[/vba]

In order to have a compact array you would still need to have a line of code for each element assignment. Plus the variable name does not help you understand what the code is doing. Make's it very hard to understand and amend at a later date.

Either use a Type variable or a Class object

Both will allow you to name the elements with meaningful names.
They will still need to be loaded line by line but that can be done in a separate routine in order to reduce code clutter in your main entry routine.

**beeawwb** · 06-04-2008, 07:00 PM

Hi Andy,

First, thank you for pointing out the "Gotcha" in my variables. I never realised that they needed to be defined seperate! Looks like I may need to revisit some past projects - they may not be behaving in exactly the ways I expect.

I'll look into the Type variables and Class objects, but I guess you've hit the nail on the head. At the end of the day, I still need to load each line seperately, so as far as reducing clutter goes, I would make more progress moving things to another module / routine.

Thanks again for your input, I really appreciate it.

-Bob