Referring to arrays using variables (1 Viewer)

solnajeff · Dec 29, 2019

Hi

I wonder if anyone can tell me if it is possible to refer to arrays using variables.

I have a number of arrays and rather than write out separate code for each I would like to refer to the array using a variable e.g "Array" & var.

I have tried all the combinations I can think of but cannot get it to work.

I would appreciate it if anyone could let me know if it is possible.

Regards

Jeff

vba_php · Dec 29, 2019

Jeff,

Not really sure I read the question correctly, but this might help:

=> you can define a static array by doing this:

Code:

dim [var] as array(element1, element2, etc...)

I have a number of arrays and rather than write out separate code for each I would like to refer to the array using a variable e.g "Array" & var.

if I read that right, *no* you can't do that. for instance, if you want to refer to an array *object* or *collection* (the entire collection variable([numberOfElements]) as [dataType]) as an actual variable itself, I doubt it is possible. Because, you are essentially asking if you can do this:

Code:

for x = 1 to 10
    x([someElement]) = 'do something
next x

where you're referring to 'x' as the actual array object or collection. and that is not possible. Does that make sense, or does it hinder you?

<edit>
actually, now that I read my words, they really don't make sense either. can you expand on your question. I think both you are I's words are confusing. clarify, will you? more explanation from you....???

gemma-the-husky · Dec 29, 2019

to access an array use this. Note that array indexes are zero-based.

myvar = arrayname - returns the nth item of the array.
myvar = arrayname(x,y,z) - used for multi-dimensioned array.

usefully, you can determine the upper bound of an array index by

myvar = ubound(arrayname) or
myvar = ubound(arrayname,dimension_number) for a multi-dimension array

NauticalGent · Dec 29, 2019

Greetings Jeff,

I have a little experience with Arrays but like NVA, I am not sure what you are trying to do.

The best site I have found that speaks to arrays is:
http://www.cpearson.com/Excel/MainPage.aspx

Choose Topic Index and browse to his one of many articles about arrays.

And as always, please post back here and let us know if you are stuck or if you have found a solution.

NauticalGent · Dec 29, 2019

Ciao Dave,

It is good to have you back!

MajP · Dec 30, 2019

Yes of course you can. I would look at this for passing arrays and returning arrays from functions / procedures.

http://www.cpearson.com/excel/passingandreturningarrays.htm

solnajeff · Jan 2, 2020

Hi

sorry for the delay in replying, I was away for the holiday.

I am going through everybodys suggestions and will update as soon as I can.

Many thanks for all your suggestions and observations.

Regards

Jeff

MajP · Jan 2, 2020

after rereading your post you may be interested in using a Jagged array to hold your individual arrays. A jagged array is an array of arrays.

Code:

Public Sub JaggedArray()
  'A jagged array is an array of arrays
  Dim Jagged(1) As Variant ' variants can hold arrays
  Dim arr1(2) As String
  Dim arr2(3) As Integer
  Dim i As Integer
  Dim j As Integer
  
  arr1(0) = "dog"
  arr1(1) = "cat"
  arr1(2) = "mouse"
  
  arr2(0) = 234
  arr2(1) = 456
  arr2(2) = 567
  arr2(3) = 678
  
  Jagged(0) = arr1
  Jagged(1) = arr2
  
  For i = 0 To UBound(Jagged)
    For j = 0 To UBound(Jagged(i))
      Debug.Print Jagged(i)(j)
    Next j
  Next i
End Sub

Then you can refer to a specific array by its index.

MajP · Jan 2, 2020

you can also hold individual arrays in a collection or a dictionary.

solnajeff · Mar 30, 2020

Hi

Thanks for the suggestions, I apologise for the delay in replying buth have been ill and am only just getting back into the swing of things.

Regards

Jeff

Referring to arrays using variables (1 Viewer)

solnajeff

Registered User.

vba_php

Forum Troll

gemma-the-husky

Super Moderator

NauticalGent

Ignore List Poster Boy

NauticalGent

Ignore List Poster Boy

MajP

You've got your good things, and you've got mine.

solnajeff

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

solnajeff

Registered User.

Similar threads

Users who are viewing this thread