Store cell values in an array in Visual basic code? 4

[IJR]

I tried to get values of each cell in column("D1) using the following code:

sub get_cell_values()
dim n,k as range
set k=columns("D&quot
'
for each n in k
if n.value=1 then n.font.bold = true
next k

Problem is n.value= 1 statement fails(Type mismatch error).

What is the problem?. I will later on try to save all values in cells in column D into an array.

Any comment will help on this kind of problems?

THX
IJr

 

[dsi]

First, your Next statement should increment n, not k.

Try something like this:

Public Sub get_cell_values()
    Dim n
    For n = 1 To 9999
        If Range("D" & n).Value = 1 Then
            Range("D" & n).Font.Bold = True
        End If
    Next n
End Sub

If you want to store the values in an array, you can use the Redim Preserve method to increase the array size as you traverse through the cells without destroying the data already stored.

Option Explicit
Option Base 1

Public Sub StoreCellValues()
    Dim MyValues()
    Dim iRow As Long, iIdx As Long
    iRow = 1
    iIdx = 1
    Do While Len(Range("D" & iRow).Text) > 0
        ReDim Preserve MyValues(iIdx)
        MyValues(iIdx) = Range("D" & iRow).Value
        iRow = iRow + 1
        iIdx = iIdx + 1
    Loop
    
    'Relay the values
    For iIdx = LBound(MyValues) To UBound(MyValues)
        MsgBox MyValues(iIdx)
    Next iIdx
End Sub

Hope this helps! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[IJR]

Dsi Master of it programming man. I was waiting for you. And it is really nice to see you keep visiting here

I am going to keep you busy for the next couple of months.

Thanks a million
IJR

 

[IJR]

By the way DSI, do you want to get more into explaining the

Range("D" & n)

stuff. This means n component of range D?

regards

 

[dsi]

No problem! Glad I could help. The Range object accepts cell locations. So, if I want to explicitly reference cell B4, I would use the range object Range("B4&quot. In the case above, you wanted to traverse through the cells in column "D". So, I used n to represent the iterative row number. The Cells object is similar to the Range object, but you pass row and column indecies, instead of using the columns by name. I like using the Range object when I am operating on a single column. But, if you want to traverse through columns, you should use the Cells object which has this format: Cells(Row Index, Column Index). We could have used this for your solution, where the column index would be set to 4 for column D.

One thing that I forgot to mention about the array solution is that the loop will stop of the first empty cell in column D. The first code segment cycles through the first 9999 rows. You should modify this accordingly to avoid excess run time, especially if you only have 20 rows of data.




DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[IJR]

Dsi boss. Thanks

Guess what, immediately after my last post, I figured out what range("d" & n) mean. It simply mean D4 if n=4 and so on.

But no harm boss, cause you supplied more than I could have ever known otherwise

Keep in sacred health boss-we need your help

regards
IJr

 

[Mala]

dsi, maybe this is not very relevant to the thread but it'd be useful, nevertheless
I'd like to add with ref to the code:

Public Sub get_cell_values()
Dim n
For n = 1 To 9999
If Range("D" & n).Value = 1 Then
Range("D" & n).Font.Bold = True
End If
Next n
End Sub
[END QUOTE]
I such a situation when the upper limit of the range in which operations are to be done is not directly known then the following may be useful:
Suppose the data is in col D starting D1 then for the upper limit of the counter we could use:
a. Range("D1",Range("D1&quot.End(xldown)) to work on the entire contiguous range - like shift+end+dn
b. If the Data may contain gaps use -
Range("D1",Range("D65535&quot.End(xlup)) to work upto the last non-blank cell
c. If just the non-blank cells use
Range("D&quot.specialcells(xlcelltype...(blanks, num..see the help file)
Mala

 

[dsi]

Mala:

That is definately a useful tip. I do not like hardcoding the upper limit because it usually results in useless processing. Although all of the projects that I have worked on do not have any gaps in the data, it should definately be considered. How would you rewrite the loop for your case B above? DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[IJR]

Dsi and Mala

Thanks for keeping the faith and enrichening this discussion.

Your comments came up pretty well timed for me. Because I have just discovered the "SpecialCells" stuff and xlend properties.

However, let me add up to this discussion an extra question:

In most sample VBA codes I usually come across

1) Application.volatile
2) Application.ScreenUpdating=True/False

What do these apparently important methods and properties mean

Back to the 1999 versus 65535 issue. Dsi is right when he mentions waste, and Mala's specialcells idea is perfect, but cant we set up Excel templates to consist of only say 2000 cells. Dsi is right, large spreadsheets are against most users' habitual use, cause people dont usually love scrolling around. They want pop and go kind of stuff. What is wrong with me?

Regards
IJr

 

[yakpol]

IJR, your initial solution was the best of all, you just made a syntax error in declaration of "n"
you wrote:
dim n, k as range
it declares only k as range, n will be variant and you will get type mismatch in for each-next loop.
try to declare:
dim n as range, k as range
and it will work! To limit a range is a good idea.

Cheers,

yakpol

 

[dsi]

The help on the Volatile and ScreenUpdating are phrased better than I can explain. If your macro takes a while to run (10 seconds or so), I would turn screen updating off, especially if you are switching sheets.

yakpol:
I tried making that change, but modifying the font on 'n' did not work. Were you able to get it to work? DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Mala]

Dear IJR

1) Application.volatile means that user defined functions starting with this statement will get recalculated whenever the worksheet gets calculated.

2) Application.ScreenUpdating=True/False
This is a very useful command if u're running a longish program - i.e. a program that takes a long time to execute.
Setting it to false ensures that the screen remains the same until the procedure ends or until it meets a Screenupdating=True command. The effect is that the system resources required to update screen each time activity occurs is freed up and is available to the program - in short, YOUR PROGRAM WORKS MUCH FASTER.

To illustrate try the following on a blank book

Sub FillNums()
Rem Application.ScreenUpdating = False
Range("A:A&quot.ClearContents
StartTime = Timer
For n = 1 To 25000
ActiveSheet.Cells(n, 1).Select
Selection.Value = n
Next
TimeElapsed = Timer - StartTime
MsgBox ("Time taken" & Str(TimeElapsed))
End Sub

Run this code as it is, note the time displayed
Run it again after removing the Rem from the first line of code - u'll find the difference in execution time SHOCKING - see for yourself and make it a habit to use ScrUpdt=false if u do a lot of VBA work in Excel
Mala

 

[Mala]

dsi:
Strictly speaking b. and c. abovre are very much the same.
u'd use the follwing code:

Dim RangeToProcess as Range
Set RangeToProcess = Range("D1",Range("D65535&quot.End(xlup)).SpecialCells(xlCellTypeConstants, xlNumbers)
For each cell in RangeToProcess

[Statements]...

Next cell

Now if the intermediate cells are blanks or errors or text they'll be ignored and processing will only be done for the cells containing numbers (which is what engineers always do).
Mala

 

[Mala]

IJR:
I reproduce your code :

sub get_cell_values()
dim n,k as range
set k=columns("D&quot
'
for each n in k
if n.value=1 then n.font.bold = true
next k

1. Next n IS incorrect - k is the counter variable for the loop
2. You dont even have to put 'Next k' just 'Next' will do -Excel remembers which level of loop the Next statement refers to

Now for the interesting part:
When we've defined k as a range, EACH n in k can only mean cell - a collection can contain only things of the same type.
for example we can say "For each ch in Activesheet.ChartObjects" to cycle thru each embedded chart in a worksheet - ch in nothing defined in xl but in the context of the collection ChartObjects it can be nothing but a single chartobject. This is a good thing the designers of Excel have done. So your code simply would be:

sub get_cell_values()
dim n,k as range
set k=columns("D&quot
'
for each n in k
if n.value=1 then n.font.bold = true
next

Now consider the statement:
dim n,k as range
by not putting 'n as range' u declared n as a variant - now this type of variable is a chameleon - a variant can literally hold (signify) anything - from a single char, a number, an object, a range, a worksheet, or even an entire a workbook - it can hold anything that can be declared with a Dim statement.
So, contrary to Yakpol's view - its not NOT declaring n as a range that's causing the problem 'coz as I said, a variant can also stand for a range.

Now consider the statement:
set k=columns("D&quot
You see, a columns collection can only contain columns even if k is dimensioned as a range (after all, a column is also a range, as is a row)
So the variant n can only mean a full column at a time.

To clarify if we said:
set k=columns("D:F&quot
In this case the variant 'n' will loop THRICE, for D, E and F. Now since we are checking in the code to make bold if the value is 1 - a cell can have a value but not an ENTIRE column - hence the error msg.

What u should do is use thd following:

Sub get_cell_values1()
Dim n, k As Range
Set k = Range("D&quot.SpecialCells(xlCellTypeConstants, xlNumbers) 'IN KEEPING WITH ANOTHER THEME IN THIS THREAD
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next n
End Sub

or

Sub get_cell_values2()
Dim k As Range REM 'n' REMOVED
Set k = Range("D&quot .SpecialCells(xlCellTypeConstants, xlNumbers)
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next 'REM 'n' REMOVED
End Sub

Note that I've changed 'Set k = Range("D&quot' to 'Set k = Range("D&quot'. Mull over this in light of the foregoing discussion - u'll get it.
I hope I'm clear - by this time MY head is throbbing.





so 'For each n in

 

[Mala]

There's a mistake in the above:
Read 'Note that I've changed 'Set k = Range("D&quot' to 'Set k = Range("D&quot'
as
Note that I've changed 'Set k = Columns("D&quot' to 'Set k = Range("D&quot'

I must log off - this is getting to me. See u guys.

 

[yakpol]

To dsi
Sorry I noticed more problems in the original code, the function "Columns" does not exist in VBA.
the following code fixes the problem and I verified it.

Sub get_cell_values()
Dim n As Range, k As Range
Set k = Range("D&quot
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next
End Sub

 

[IJR]

Thanks to

Mala,dsi and yakpol for the great responses.

Within a week I have understood the basics of Excel VBA and the knowledge could have cost me 4 times as much time and effort if I sought it somewhere else.

Just to let you know.

IJR

 

[Mala]

To yakpol:

Perhaps my long winded explanation above was not good enough:
Here's some more
'Columns' is not a function of VBA - its a 'range' object contained within a 'sheet' object which itself is a member of the 'sheets' collection within the 'workbook' object (contained in thw 'workbooks' cokkection), which is contained within the'application' object (in our case, Excel. Its important to be clear about the heirarchy of objects to be able to refer to them properly. When we say Columns("D&quot or Columns(4) we are referring to that particular column in the Columns collection. So there's nothing wrong with reference Columns("D&quot.

The original code was

sub get_cell_values()
dim n,k as range
set k=columns("D&quot
'
for each n in k
if n.value=1 then n.font.bold = true
next k

Now that k is defined as a collection of columns, each n in k can only mean a single column, not a cell

Had we used

'for each n in k.cells', in the original code
it would have worked.

As I've said before - its easier to sense than to explain

Let us be clear that VBA is the common programming language for all MS Office applications - each application exposes different objects having their own distinctive properties to VBA for manipulation - some of these objects may be common across applications - e.g. TextBox object, Font object,Shape object while others may be unique to the application for which the code is being written - e.g. Range object or Row object(Excel), TableDef/QueryDef (Access), Slide object(Powerpoint) and so on....

The object/methods/properties model of VBA can be a bit confusing initially - but once u get the hang of it, its easy - so everyone - just persevere and u'll be rewarded!!

 

[IJR]

Mala, you aimed the post above to yakpol, but man, that one was a useful bit. I made an error in my first macro, actually it was a typo, I meant to put "next n" there instead of "next k". But I see now why that one did not work in my actual code.

Thanks
IJr

 

[Mala]

Thank you IJR - glad the point got home