CODEWITHSUNDEEP
excelvariablesexcel-2010vba
B-Ballerl
B-Ballerl

Reputation: 1

Calling a variable by a variable excel

I wondering how I can use a variable to call another variable. For example Apple1, Apple2, Apple3, Apple4, Apple5, Apple6 and AppleNum.

Let us say AppleNum is 4. How can I use AppleNum to call for the code to use Apple4?

Also, What if Apple1, Apple2...ect are objects?

My first thought on how to solve this was by using some sort of Array?

Note: Using Select Case here will work but doesn't simply the code I have and would require each case to be written out individually (a lot of work)

Just out of interest, ts there a way to define AppleNum number of variables in the code?

If you can help, Thanks!

Upvotes: 0

Views: 44

Answers (1)

Floris
Floris

Reputation: 46375

It is possible to have an array of variable size in VBA, using the following (for example):

Dim myArray() as Double
Dim AppleNum as Integer, arraySize as Integer
AppleNum = 4
arraySize = 6

ReDim myArray(1 to arraySize)

this would create an array myArray, and change its size to be 6 elements long (with index starting at 1). You can declare the entire array in one statement (without redimensioning) with

myArray = Array(Apple1, Apple2, Apple3, Apple4, Apple5, Apple6)

Assuming that Apple1 etc. have been previously declared.

If you declare myArray as Variant rather than Integer (or don't give a type - defaults to Variant) then your array can contain anything you want.

You can also use

ReDim Preserve myArray(1 to arraySize * 2)

if at a later point you want the array to be twice as large, but you don't want to lose the first six elements you had assigned.

I hope these things will get you moving with your problem.

Upvotes: 0

Related Questions