Variable in variable

Wries · #1 05-09-2014, 09:29 AM

Hello All,
Is something like the below possible?

Code:

Dim i as Byte
Dim Rank1, Rank2, Rank3 as String

for i = 1 To 3
Rank&i = 'cell reference value
next i

thanks a lot,
Wries

BobBridges · #2 05-09-2014, 03:16 PM

In VBScript you can do something like that, Wries. I don't know of a way to do it directly in VBA. But depending on what you need, you may not have to. Do you just need to set three (or more) values in a loop? If so, there are several really good ways.

1) Use an "array". An array is a series of variables with a name plus a number, any number you like from x to y. In your example it would work like this:

Code:

Dim Rank(1 To 3)
For i = 1 to 3
  Rank(i) = 'cell reference value
  Next i

For "cell reference value" you plug in whatever you like.

2) Use a collection. A collection is like an array but more flexible. For one thing you can name the items in a collection, as well as number them. For another, items of a collection can be objects, not just scalar values (numbers or character strings, I mean). And you don't have to decide ahead of time how many you want. With an array you have to use the Dim statement to give the array a predetermined lower and upper bound; but a collection doesn't have to know ahead of time how many items it'll hold. Like this:

Code:

Set Coll = New Collection
For i = 1 to 3
  Coll.Add ActiveWorksheet.Rows(i + 1)
  Next i

This stores in the collection three entire rows (2, 3 and 4) of the currently active worksheet.

3) Use a worksheet. If for some reason you don't like either of the above two choices, you can store the data in a spare worksheet, if you have one handy.

Code:

Set os = ThisWorkbook.Worksheets("Sheet3")
For i = 1 to 3
  os.Cells(i, 5).Value = "some value"
  Next i

This isn't as good, in most cases, because making something happen in the worksheet is slower than doing it in an array or collection. Three items doesn't make any difference, but you'll definitely notice if you try it with a thousand. Still, it's an option.

Are any of those a help?

Wries · #3 05-12-2014, 01:50 AM

Dear Bob,
Thank you very much for the reply.
Of course the array would be perfect solution. I am using arrays often no idea why i didnt associated them with this problem.
Kind regards,
Peter

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
Need help sumif with variable for VBA	jingo	Excel Programming	4	01-23-2014 11:02 AM
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
Variable fields?	Emalee77	PowerPoint	0	01-30-2011 05:58 PM