VBA Arrays with References to Multiple Sheets

[Dschwarz]

I would like to collect data from several cells on multiple sheets into an array but this is presenting a problem. I can fill an array with data from cells on the active sheet using the code below:

Sub store_data()
Dim d(4) As String
d(0) = Range("E7").Value
    d(1) = Range("E8").Value
    d(3) = Range("E9").Value
    d(4) = Range("E10").Value
End Sub

However, when I attempt to collect data from the current sheet and then another sheet, the d(0) and d(1) expressions run but I get an error at the d(2) expression (Runtime error '9': subscript out of range).

Sub store_data()
Dim d(4) As String
d(0) = Range("E7").Value
    d(1) = Range("E8").Value
    d(2) = Sheets("sheet6").Range("E9").Value
    d(3) = Sheets("sheet6").Range("E10").Value
End Sub

Any help would be appreciated.

[Dschwarz]

Solved my own problem...

Sub store_data()
Dim d(4) As String
d(0) = Range("E7").Value
    d(1) = Range("E8").Value
    d(2) = Sheet6.Range("E9").Value
    d(3) = Sheet6.Range("E10").Value
End Sub

[Aussiebear]

Been looking at this thread for a couple of days now and wondering how the logic of it works. To begin with the OP only dims 1 variable, namely "d(4)" as a String, yet uses four other variables as the solution. Also the reference, Sheets ("Sheet 6").Range is said to not work, but Sheet6.Range does. Why is this so?

As a further input. If I saw something like d(4), I'd be expecting an Array with 4 components , namely d(0), d(1), d(2), & d(3), however the OP dimmed it as a String rather than an Array, so how did the array get formed as suggested per the first post.

[ZloVsky]

Hello
You could have a look at Paul Site
See you

[Paul_Hossler]

Been looking at this thread for a couple of days now and wondering how the logic of it works. To begin with the OP only dims 1 variable, namely "d(4)" as a String, yet uses four other variables as the solution. Also the reference, Sheets ("Sheet 6").Range is said to not work, but Sheet6.Range does. Why is this so?

As a further input. If I saw something like d(4), I'd be expecting an Array with 4 components , namely d(0), d(1), d(2), & d(3), however the OP dimmed it as a String rather than an Array, so how did the array get formed as suggested per the first post.

Dim d(4) As String

dim's a 5 element string array with indices of 0, 1, 2, 3 and 4

(BTW I like to Dim d(1 to 4) as String)

My guess is that Sheet6 is the Code Name, but the WS Name is something else, not = "Sheet6"

[Aussiebear]

So effectively "Dim d(4) as a String" is shorthand coding.

[Paul_Hossler]

So effectively "Dim d(4) as a String" is shorthand coding.

I wouldn't call it shorthand, I've always seem it that way and I don't know any other way to define an array of Strings, without the 'As String" they'd all be Variants

Capture.JPG


https://learn.microsoft.com/en-us/of...6)%26rd%3Dtrue

The examples are good

[Aussiebear]

Thank you Paul. There's a hundred ways to skin a cat when using coding. Some don't seem natural to me.

For example "dim d(4) as String". If you hadn't read the preamble about wanting to create an array of data, then the line "dim d(4) as string", would lead to anyone to believe that d(4) was a string. Yet we know from the preamble that d(4) is meant as an array by the OP. Subsequent use of the indices d(0), d(1), d(2), & d(3) are to me deceptive. As in having a very similar format as the name of the array.

I looked at your example in the post above, and it shows exactly why I'm a little confused here. D(4) equals a value "E" therefore could not be regarded as an Array.

[Paul_Hossler]

A variable name cannot have a paren in it

A variable D4 is not the same as an array element D(4)

[Aussiebear]

I must be confusing you as well as myself..


In this image this line, "Dim D(4) as String" is predetermined to be an Array of Strings, yet later on, 7 lines later, D(4) is used as a name of a indice. I'm inclined to believe that the original line should have read Dim d(0 to 4) As String

[Aflatoon]

Should is pretty subjective though.

Although I also prefer to specify all fixed bounds in my real code - and sometimes in posted code, since I've lost track of the number of times I've seen people try and declare say a 5 element array using d(5), for example - you don't have to. The presence of the Dim statement makes the meaning clear. At least it wasn't written as:

Dim D$(4)

[Paul_Hossler]

I must be confusing you as well as myself..
In this image this line, "Dim D(4) as String" is predetermined to be an Array of Strings, yet later on, 7 lines later, D(4) is used as a name of a indice. I'm inclined to believe that the original line should have read Dim d(0 to 4) As String

Dim D(4) As String defines a 5 element array named D

To put data in or get it out of a specific "slot" you need something like D(4) where 4 is the index into D's 4th "slot" (actually 5th or UBound(D)

[Aussiebear]

Got it. Thank you.

[Dschwarz]

Here is my best explanation, if I understand your question correctly. d(4) is an array with 4 addresses (actually its 5 addresses 0,1,2,3,4). Each array address is used to store a text string. The text strings stored in each array address come from specific cells in the workbook located on different worksheets. If you use a button to initiate the macro on a specific sheet, lets say sheet 1, then you can reference a cell on the same sheet as the button (the active sheet) like this:
d(0) = Range("E7").Value
d(1) = Range("E8").Value


However if you want to populate the array using cells on worksheets other than the active worksheet (sheet 1 where the macro was called) you must do this:

d(0) = Range("E7").Value
d(1) = Sheet6.Range("E8").Value

or this:

d(0) = Sheet1.Range("E7").Value
d(1) = Sheet6.Range("E8").Value

[Aussiebear]

Thank you.