VBA - How to define and assign values in Array

F

FKalinx

New Member
Joined
Jul 2, 2014
Messages
39
Hi,

I am trying to do some analysis on an array in Excel and want to use VBA.

As a first step I need to define a range or array (not sure) that includes the 28 cells below. Then I need to pull out certain characteristics (using Offset, UCase) and display these in a table. I will probably need help on this but as a first step how do I define my array?

Dim aRng As Range
Dim aCell As Range
Set aRng = Selected

I've tried Set aRng = Range ("A1:A28") but this doesn't work.

Please help!

______

[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]UK[/TD]
[/TR]
[TR]
[TD]Brand Star[/TD]
[/TR]
[TR]
[TD]Total Cinemas 109[/TD]
[/TR]
[TR]
[TD]Total Screens 868[/TD]
[/TR]
[TR]
[TD]ITALY[/TD]
[/TR]
[TR]
[TD]Brand Holy[/TD]
[/TR]
[TR]
[TD]Total Cinemas 46[/TD]
[/TR]
[TR]
[TD]Total Screens 466[/TD]
[/TR]
[TR]
[TD]IRELAND[/TD]
[/TR]
[TR]
[TD]Brand Bird[/TD]
[/TR]
[TR]
[TD]Total Cinemas 10[/TD]
[/TR]
[TR]
[TD]Total Screens 68[/TD]
[/TR]
[TR]
[TD]PORTUGAL[/TD]
[/TR]
[TR]
[TD]Brand Holy[/TD]
[/TR]
[TR]
[TD]Total Cinemas 3[/TD]
[/TR]
[TR]
[TD]Total Screens 45[/TD]
[/TR]
[TR]
[TD]SPAIN[/TD]
[/TR]
[TR]
[TD]Brand Cinesa[/TD]
[/TR]
[TR]
[TD]Total Cinemas 46[/TD]
[/TR]
[TR]
[TD]Total Screens 535[/TD]
[/TR]
[TR]
[TD]GERMANY[/TD]
[/TR]
[TR]
[TD]Brand Kinowelt[/TD]
[/TR]
[TR]
[TD]Total Cinemas 23[/TD]
[/TR]
[TR]
[TD]Total Screens 203[/TD]
[/TR]
[TR]
[TD]AUSTRIA[/TD]
[/TR]
[TR]
[TD]Brand Kinowelt[/TD]
[/TR]
[TR]
[TD]Total Cinemas 3[/TD]
[/TR]
[TR]
[TD]Total Screens 38[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Sektor said:
Dim aRng As Variant
Click to expand...
Just as a follow-up to Sektor's posting.... the array produced by assigning a range to a Variant variable will always be a two-dimensional array (rows are first dimension, columns are second dimension) even if the range is a along a single row or down a single column. Also, these arrays will always be one-based (first index will always be 1, never 0).
 
Upvote 0
Rick Rothstein said:
Just as a follow-up to Sektor's posting.... the array produced by assigning a range to a Variant variable will always be a two-dimensional array (rows are first dimension, columns are second dimension) even if the range is a along a single row or down a single column. Also, these arrays will always be one-based (first index will always be 1, never 0).
Click to expand...

Many thanks guys.

So, I've written this as I just want to test whether the array has been defined correctly using select (in the same way that you use MsgBox)?

This doesn't work?

Sub ArraySelect()
Dim aRng As Variant
Dim aCell As Range
Set aRng = Range("B2:B29")
Range(aRng).Select
End Sub
 
Upvote 0
Any help on this chaps? I want a way like MsgBox just to make sure the Range has been correctly defined?
 
Upvote 0
Try this code , Step through Using "F8" with code window open.
Code: 
Sub ArraySelect()
Dim aRng As Range 'This is a range Object
Dim aCell As Range 'So is this
Set aRng = Range("B2:B29")
aRng.Select
For Each aCell In aRng
    MsgBox aCell.Value 'Shows individual cell values
Next aCell
End Sub
 
Upvote 0
MickG said:
Try this code , Step through Using "F8" with code window open.
Code: 
Sub ArraySelect()
Dim aRng As Range 'This is a range Object
Dim aCell As Range 'So is this
Set aRng = Range("B2:B29")
aRng.Select
For Each aCell In aRng
    MsgBox aCell.Value 'Shows individual cell values
Next aCell
End Sub
Click to expand...

Thanks Mick! Works like a VBA dream.
 
Upvote 0
FKalinx said:
Thanks Mick! Works like a VBA dream.
Click to expand...
The only thing is, with the code you originally proposed and the modification Mick did to it, you are not working with an array, rather you are still working with the range itself (via the variable you set to reference it). While you only show the Value property, all of the range's properties are still intact. Mick's code could have been written this way and the result would have been the same... and the execution speed would have been the same as well.
Code: 
Sub ArraySelect()
  Dim aCell As Range
  For Each aCell In Range("B2:B29")
      MsgBox aCell.Value
  Next aCell
End Sub
The speed advantage of true arrays has not been realized with either Mick's or my above code; that is, you are still visiting the worksheet on each iteration of the loop. To get the true speed advantages of arrays, you have to create a real VB array of values from the range and then work with that real VB array in memory (that is, the worksheet will not be visited during processing). Here is how to do that for a rectangular range....
Code: 
[table="width: 500"]
[tr]
	[td]Sub ArrayValues()
  Dim R As Long [COLOR="#008000"]'Variable to index rows in the array[/COLOR]
  Dim C As Long [COLOR="#008000"]'Variable to index columns in the array[/COLOR]
  Dim Data As Variant [COLOR="#008000"]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:D5") [COLOR="#008000"]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR="#008000"]'Loop the rows (first dimension)[/COLOR]
    For C = 1 To UBound(Data, 2) [COLOR="#008000"]'Loop the columns (second dimension)[/COLOR]
      MsgBox Data(R, C) [COLOR="#008000"]'Shows individual values in the array[/COLOR]
    Next C
  Next R
End Sub[/td]
[/tr]
[/table]
Now your range was in a single column and this is how the loop would look for that...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ArrayValuesSingleColumn()
  Dim R As Long [COLOR="#008000"]'Variable to index rows in the array[/COLOR]
  Dim Data As Variant [COLOR="#008000"]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:B29") [COLOR="#008000"]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR="#008000"]'Loop the rows (first dimension)[/COLOR]
    MsgBox Data(R, 1) [COLOR="#008000"]'Shows individual values in the array (note fixed second dimension for single column of values)[/COLOR]
  Next R
End Sub[/td]
[/tr]
[/table]
This last code will be super fast in execution although you won't see it because of the individual values being displayed in MessageBoxes... if instead of MessageBoxes, you did something physical with the values, you could then store them back to the same array, create another array where the processed values would be stored, and write those values back to the worksheet in the blink of an eye. For example...
Code: 
Sub ArrayValuesSingleColumn()
  Dim R As Long [COLOR="#008000"]'Variable to index rows in the array[/COLOR]
  Dim Data As Variant [COLOR="#008000"]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:B29") [COLOR="#008000"]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR="#008000"]'Loop the rows (first dimension)[/COLOR]
    Data(R, 1) = Data(R, 1) * Data(R, 1) [COLOR="#008000"]'Change the stored values to their squares[/COLOR]
  Next R
  Range("C2").Resize(UBound(Data, 1)) = Data [COLOR="#008000"]'Output the values to the next column[/COLOR]
End Sub
 
Upvote 0
Rick Rothstein said:
The only thing is, with the code you originally proposed and the modification Mick did to it, you are not working with an array, rather you are still working with the range itself (via the variable you set to reference it). While you only show the Value property, all of the range's properties are still intact. Mick's code could have been written this way and the result would have been the same... and the execution speed would have been the same as well.
Code: 
Sub ArraySelect()
  Dim aCell As Range
  For Each aCell In Range("B2:B29")
      MsgBox aCell.Value
  Next aCell
End Sub
The speed advantage of true arrays has not been realized with either Mick's or my above code; that is, you are still visiting the worksheet on each iteration of the loop. To get the true speed advantages of arrays, you have to create a real VB array of values from the range and then work with that real VB array in memory (that is, the worksheet will not be visited during processing). Here is how to do that for a rectangular range....
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ArrayValues()
  Dim R As Long [COLOR=#008000]'Variable to index rows in the array[/COLOR]
  Dim C As Long [COLOR=#008000]'Variable to index columns in the array[/COLOR]
  Dim Data As Variant [COLOR=#008000]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:D5") [COLOR=#008000]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR=#008000]'Loop the rows (first dimension)[/COLOR]
    For C = 1 To UBound(Data, 2) [COLOR=#008000]'Loop the columns (second dimension)[/COLOR]
      MsgBox Data(R, C) [COLOR=#008000]'Shows individual values in the array[/COLOR]
    Next C
  Next R
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Now your range was in a single column and this is how the loop would look for that...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ArrayValuesSingleColumn()
  Dim R As Long [COLOR=#008000]'Variable to index rows in the array[/COLOR]
  Dim Data As Variant [COLOR=#008000]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:B29") [COLOR=#008000]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR=#008000]'Loop the rows (first dimension)[/COLOR]
    MsgBox Data(R, 1) [COLOR=#008000]'Shows individual values in the array (note fixed second dimension for single column of values)[/COLOR]
  Next R
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
This last code will be super fast in execution although you won't see it because of the individual values being displayed in MessageBoxes... if instead of MessageBoxes, you did something physical with the values, you could then store them back to the same array, create another array where the processed values would be stored, and write those values back to the worksheet in the blink of an eye. For example...
Code: 
Sub ArrayValuesSingleColumn()
  Dim R As Long [COLOR=#008000]'Variable to index rows in the array[/COLOR]
  Dim Data As Variant [COLOR=#008000]'Variable to house the values from the range[/COLOR]
  Data = Range("B2:B29") [COLOR=#008000]'Assign the values in the range to a two-dimensional array[/COLOR]
  For R = 1 To UBound(Data, 1) [COLOR=#008000]'Loop the rows (first dimension)[/COLOR]
    Data(R, 1) = Data(R, 1) * Data(R, 1) [COLOR=#008000]'Change the stored values to their squares[/COLOR]
  Next R
  Range("C2").Resize(UBound(Data, 1)) = Data [COLOR=#008000]'Output the values to the next column[/COLOR]
End Sub
Click to expand...

So I now understand the power of storing information in arrays rather than ranges. I've also swotted up on UBound and For Arrays.

I am now trying to add an If Function into the For Loop that identifies if the cell is UpperCase. I can't work out how to identify if a Cell in the Range is Uppercase :s

Please advise!

Dim R As Long 'Variable to index rows in array
Dim Data As Variant 'Variable to house the values from the range
Dim aCell As Range
Data = Range("B2:B29") 'Assign the values to a two-dimensional array
For Each aCell In Data
If aCell.Value = UCase(aCell.Value) Then
aCell.Value = "Uppercase"
End If
Next aCell
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Macro not working with button and returning error message.
Replies
9
Views
879
RoryA
RoryA
I
  • Question Question
Missing Value Checker not working
Replies
1
Views
234
iliauk
I
E
  • Question Question
2 Issues: Inefficient Array Read/Write Procedure & Compile Error Procedure Too Large
Replies
3
Views
704
rlv01
R
A
  • Question Question
Excel 2010 VBA Array: Replace Values in a Column Of Cells
Replies
4
Views
4K
ajay_gajree
A
S
  • Question Question
OS information in excel using script and VBA
Replies
7
Views
8K
xenou
xenou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top