Range, select, and printing arrays

[Jeff Ciaccio]

I am just trying to learn VBA for Excel and was wondering if somebody might
help me. I am trying to understand how to select a cell (or range), how to
position the curser somewhere in that range (is that the same as
"collapsing"?), and how to print each value in an array.

I have created a small macro that factors all of the numbers in the first
row. Now I'd like to ask the user if they'd like to see all of the prime
numbers (see VBA code below). If so, I would like to display those in a
msgBox, preferably one per line. If the number that was factored was prime,
then "PRIME" is placed in row 5.

1) Do I need to decalre P(j) as an array to use it?
2) Is the Else: P(j) = "" required. I'm putting it in just to set initial
conditions just in case.
3) Is there a more efficient way of finding the value of the cell above the
active cell? I have done an ActiveCell.Offset to select it, but can I find
the value without first selecting the cell and making it active?
4) Can somebody explain the .Range("A1") when I am doing an offset. It
seems that no matter what the active cell is, "A1" is filled in.
5) How do I print all the values in an array? Do I need a For Each loop, or
does VBA have an easier way?

'Display the prime numbers if the user would like
Response = MsgBox(prompt:="Do you want to see the prime numbers?",
Style:=vbYesNo)
If Response = vbYes Then
Range("A5").Select
For j = 1 To 255
If ActiveCell.Value = "Prime" Then
ActiveCell.Offset(1, 0).Range("A1").Select
P(j) = ActiveCell.Value
Else: P(j) = ""
End If

For Each j In P(j)
' make a textbox that prints all the prime numbers, 1 per line


Thanks in advance!!

 

[Per Jessen]

Hi Jeff

Look at the code below. It should do what you need. Notice that the
statement "Option Base 1" is used to indicate that the first element in the
array should be P(1). Otherwise the first element would be P(0)


Option Base 1
Dim p() As Integer
Dim Counter As Long
Dim Cell As Range

Sub Prime()

Set Cell = Range("A5")
'Display the prime numbers if the user would like
Response = MsgBox("Do you want to see the prime numbers?", vbYesNo)
If Response = vbYes Then
ReDim p(1)
For j = 1 To 255
If Cell.Value = "Prime" Then
Counter = Counter + 1
p(Counter) = Cell.Offset(-4, 0).Value
ReDim Preserve p(UBound(p) + 1)
End If
Set Cell = Cell.Offset(0, 1)
Next
End If
' make a textbox that prints all the prime numbers, 1 per line
For c = 1 To UBound(p) - 1
OutputString = OutputString & p(c) & vbLf
Next
msg = MsgBox(OutputString)
End Sub

Further comments below

1) Do I need to decalre P(j) as an array to use it?

Yes you always have to declare an array.

2) Is the Else: P(j) = "" required. I'm putting it in just to set initial
conditions just in case.

No I use the Counter variable to count the number of elements in the array.

3) Is there a more efficient way of finding the value of the cell above
the active cell? I have done an ActiveCell.Offset to select it, but can I
find the value without first selecting the cell and making it active?
MyVar=ActiveCell.Offset(1,0).value

4) Can somebody explain the .Range("A1") when I am doing an offset. It
seems that no matter what the active cell is, "A1" is filled in.

In Excel 2000 this statement have no effect at all.

5) How do I print all the values in an array? Do I need a For Each loop,
or does VBA have an easier way?

Build the output string using a loop then display it. See the code.

Regards,
Per

 

[Dave Peterson]

#1. It's always a good idea to declare your variables, but how you declare it
depends on how you populate it.

For instance:

dim p as Variant
dim myRng as Range
dim iCtr as long

with activesheet
set myrng = .range("A5", .cells(.rows.count,"b").end(xlup))
end with
p = myrng.value

P is now an array -- x rows (row 5 to the last used row in column B) by 2
columns. It doesn't include just the prime numbers.

Then I could loop through that array by:

'looping through the first dimension of that array
for ictr = lbound(p, 1) to ubound(p,1)
if lcase(p(ictr,1)) = lcase("Prime") then
msgbox p(ictr,2) 'show the number (column 2 of that array)
end if
next ictr

===========
Or you could loop through the cells and build the array.

dim p() as long
dim myRng as Range
dim myCell as range
dim pCtr as long
dim iCtr as long
with activesheet
'just a single column!
set myrng = .range("A5", .cells(.rows.count,"A").end(xlup))
end with

pctr = 0
for each mycell in myrng.cells
if lcase(mycell.value) = lcase("prime") then
pctr = pctr + 1
'keep the existing values, but make the array bigger
redim preserve p(1 to pctr)
'add the new element to the array
p(pctr) = mycell.offset(0,1).value
end if
next ictr

if pctr = 0 then
msgbox "no primes!"
else
for ictr = lbound(p) to ubound(p)
msgbox p(ictr)
next ictr
end if

========

Another way that would be a little quicker redimming the array each time you
need it is to make it as big as you need. Populate the array in order. Then
chop off the elements you didn't use:

dim p() as long
dim myRng as Range
dim myCell as range
dim pCtr as long
dim iCtr as long
with activesheet
'just a single column!
set myrng = .range("A5", .cells(.rows.count,"A").end(xlup))
end with

redim p(1 to myrng.cells.count) 'big enough to hold all the values!

pctr = 0
for each mycell in myrng.cells
if lcase(mycell.value) = lcase("prime") then
pctr = pctr + 1
'keep the existing values, but make the array bigger
'add the new element to the array
p(pctr) = mycell.offset(0,1).value
end if
next ictr

if pctr = 0 then
msgbox "no primes!"
else
'chop off the stuff we didn't use:
redim preserve p(1 to pctr)
for ictr = lbound(p) to ubound(p)
msgbox p(ictr)
next ictr
end if

========
Usually it's quicker to pick up the data in one giant swoop, then looping
through the array. Looping through the cells on the worksheet will be slower.

============
#2. I wouldn't populate the array with "" if the number isn't a prime--unless
there was a reason I needed to know. But if the array only held primes, I know
each element is a prime.

============
#3. One of the things you can do to speed up your code is to elimate the
..select's and .activate's.

============
#4. You can address cells/ranges in lots of way. But
activecell.offset(0,1).range("a1") is exactly the same as activecell.offset(0,1)

The .range("A1") is a way of saying to "stay" in the same cell.

try this:
range("A1").select
msgbox activecell.offset(0,1).range("C9").address
msgbox activecell.range("D9").address
msgbox activecell.offset(8,3).address

Chip Pearson has some notes written by Alan Beban that show some other ways to
address ranges:
http://www.cpearson.com/excel/cells.htm

===========
#5. Do you really mean print or just display? If you're making flashcards, I'd
just populate a different worksheet (giant font) with each prime and print that.

If you mean display, you could use the msgbox.

If you wanted to see all the primes at once, I'd dump all this and apply
Data|filter|autofilter to column A and show just the rows with Prime in that
column.

 

[Kenneth Hobson]

If you are showing a range in a textbox on a userform, something like this
can be done.

Sub t()
Dim r As Range
[a1] = "a"
[b1] = "b"
[c1] = "c"

Set r = [a1:c1]
UserForm1.TextBox1.MultiLine = True
UserForm1.TextBox1.Value =
Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(r.Value)), vbCr)
UserForm1.Show
End Sub

 

[Jeff Ciaccio]

Thanks - your way is MUCH faster than looping through the cells!! If you
have another second, could you answer a few more questions?

ReDim p(1)

Why do you only ReDim the array if the user wants to see the primes? Does
the (1) just tell the compiler that it's 1 dimensional?

If Cell.Value = "Prime" Then

How do you tell VBA not to be case specific?

ReDim Preserve p(UBound(p) + 1)

I'm still not too sure on this. It looks like you are dimensioning the
array and keeping the values currently in the array. Why do you need the
+1? If the index goes from 1 to UBound (since we set this option), wont
UBound(p) do the trick?

Set Cell = Cell.Offset(0, 1)

Thanks for this - much easier to understand!

For c = 1 To UBound(p) - 1

Don't we lose the last value in the array with the - 1 ?

OutputString = OutputString & p(c) & vbLf

Ahh... that's how VBA does special string formatting characters - thanks!

Regards,
Per

Thanks so much!!
- Jeff

 

[Per Jessen]

Hi Jeff

Thanks for your reply.

Why do you only ReDim the array if the user wants to see the primes? Does
the (1) just tell the compiler that it's 1 dimensional?

The ReDim statement is needed to declare the size of the array before you
can assign any value to it, ie not needed if the user don't want to see the
primes. See the array as a worksheet with 1 row and one column. As the code
ReDim later on the array will have 2 rows and 1 column and so on..

How do you tell VBA not to be case specific?

If LCase(Cell.Value) = "prime" Then

I'm still not too sure on this. It looks like you are dimensioning the
array and keeping the values currently in the array. Why do you need the
+1? If the index goes from 1 to UBound (since we set this option), wont
UBound(p) do the trick?

UBound(p) is the current size of the last dimension in the array, so +1 is
needed to expand the array.

Preserve is as you think used to keep the values currently in the array.

Don't we lose the last value in the array with the - 1 ?

No because the array is expaded after the last number is assigned to the
array. If you remove the -1 you will have a zero value as last item.

Hopes this was explaination enough.

Best regards,
Per