Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2014, 09:29 AM
Wries Wries is offline Variable in variable Windows 7 64bit Variable in variable Office 2007
Advanced Beginner
Variable in variable
 
Join Date: Jun 2009
Posts: 40
Wries is on a distinguished road
Default Variable in variable


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
Reply With Quote
  #2  
Old 05-09-2014, 03:16 PM
BobBridges's Avatar
BobBridges BobBridges is offline Variable in variable Windows 7 64bit Variable in variable Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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?
Reply With Quote
  #3  
Old 05-12-2014, 01:50 AM
Wries Wries is offline Variable in variable Windows 7 64bit Variable in variable Office 2007
Advanced Beginner
Variable in variable
 
Join Date: Jun 2009
Posts: 40
Wries is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable in variable 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
Variable in variable object variable or with block variable not set MJP143 Excel 1 02-11-2013 05:07 AM
Variable in variable 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:52 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft