CODEWITHSUNDEEP
arraysperformanceexcelvba
Orphid
Orphid

Reputation: 2852

Excel VBA variable use: Is it better to use multiple variables or one array to store information

I've been using VBA for about a month now, and this forum has been a great resource for my first "programming" language. As I've started to get more comfortable with VBA arrays, I've begun to wonder what the best way to store variables is, and I'm sure someone here knows the answer to what's probably a programming newb question:

Is there any difference, say, between having 10 String variables used independently of each other or an array of String variables used independently of each other (by independent I mean their position in the array doesn't matter for their use in the program). There are bits of code I use where I might have around 9 public variables. Is there any advantage to setting them as an array, despite the fact that I don't need to preserve their order vis a vis one another? e.g. I could have

Public x As String
Public y As String
Public v As String
Public w As String

Or

Public arr(1 to 4) As String
arr(1) = x
arr(2) = y
arr(3) = v
arr(4) = w

In terms of what I need to do with the code, these two versions are functionally equivalent. But is there a reason to use one rather than the other?

Connected to this, I can transpose an array into an Excel field, and use xlUp and xlDown to move around the various values in the array. But I can also move through arrays in similar ways by looking for elements with a particular value or position in an array held "abstractly."* Sometimes I find it easier to manipulate array values once they have been transposed into a worksheet, using xlUp and xlDown. Apart from having to have dedicated worksheet space to do this, is this worse (time, processing power, reliability etc.) than looping through an "abstract"* array (if Applications.ScreenUpdating = False)?

*This may mean something technical to mathematicians/ serious programmers - I'm trying to say an array that doesn't use the visual display of the worksheet grid.

EDIT:

Thank you for your interesting answers. I'm not sure if the second part of my question counts as a second question entirely and I'm therefore breaking a rule of the forum, or if it is connected, but I would be very happy to tick the answer that also considered it

Upvotes: 1

Views: 1687

Answers (2)

Tim Williams
Tim Williams

Reputation: 166196

As others have noted, there's no need to use arrays for variables which are not related or part of a "set" of values. If however you find yourself doing this:

Dim email1 as String, email2 as String, email3 as String, _
    email4 as String, email5 as String

then you should consider whether an array would be a better approach.

To the second part of your question: if you're using arrays in your VBA then it would be preferrable to work with them directly in memory rather than dumping them to a worksheet and navigating them from there.

Keeping everything in-memory is going to be faster, and removes dependencies such as having to ensure there's a "scratch" worksheet around: such dependencies make your code less re-usable and more brittle.

Upvotes: 2

EkriirkE
EkriirkE

Reputation: 2295

Unless you need to refer to them sequentially or by index# dynamically do not use an array as a grouping of scratch variables. It is harder to read.

Memory-wise they should be near identical with slight more overhead on the array.

Upvotes: 3

Related Questions