Store cell values in an array in Visual basic code?
Store cell values in an array in Visual basic code?
(OP)
I tried to get values of each cell in column("D:D1) using the following code:
sub get_cell_values()
dim n,k as range
set k=columns("D:D")
'
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
sub get_cell_values()
dim n,k as range
set k=columns("D:D")
'
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
RE: Store cell values in an array in Visual basic code?
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.
RE: Store cell values in an array in Visual basic code?
I am going to keep you busy for the next couple of months.
Thanks a million
IJR
RE: Store cell values in an array in Visual basic code?
Range("D" & n)
stuff. This means n component of range D?
regards
RE: Store cell values in an array in Visual basic code?
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.
RE: Store cell values in an array in Visual basic code?
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
RE: Store cell values in an array in Visual basic code?
I'd like to add with ref to the code:
[QUOTE]
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").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").End(xlup)) to work upto the last non-blank cell
c. If just the non-blank cells use
Range("D:D").specialcells(xlcelltype...(blanks, num..see the help file)
Mala
RE: Store cell values in an array in Visual basic code?
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.
RE: Store cell values in an array in Visual basic code?
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
RE: Store cell values in an array in Visual basic code?
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
RE: Store cell values in an array in Visual basic code?
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.
RE: Store cell values in an array in Visual basic code?
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").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
RE: Store cell values in an array in Visual basic code?
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").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
RE: Store cell values in an array in Visual basic code?
I reproduce your code :
sub get_cell_values()
dim n,k as range
set k=columns("D:D")
'
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:D")
'
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:D")
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")
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:D").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:D") .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:D")' to 'Set k = Range("D:D")'. 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
RE: Store cell values in an array in Visual basic code?
Read 'Note that I've changed 'Set k = Range("D:D")' to 'Set k = Range("D:D")'
as
Note that I've changed 'Set k = Columns("D:D")' to 'Set k = Range("D:D")'
I must log off - this is getting to me. See u guys.
RE: Store cell values in an array in Visual basic code?
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:D")
For Each n In k
If n.Value = 1 Then n.Font.Bold = True
Next
End Sub
RE: Store cell values in an array in Visual basic code?
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
RE: Store cell values in an array in Visual basic code?
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:D") or Columns(4) we are referring to that particular column in the Columns collection. So there's nothing wrong with reference Columns("D:D").
The original code was
sub get_cell_values()
dim n,k as range
set k=columns("D:D")
'
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!!
RE: Store cell values in an array in Visual basic code?
Thanks
IJr
RE: Store cell values in an array in Visual basic code?