Explanation of VBA procedure

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Could someone please explain what is happening with this procedure .
For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell
.Offset(0, 1).Resize(1, 6).Locked = .Value
End With
Next cell
1. Does it mean that the cells in columns C through H are all locked if the
Value in the corresponding row in column B is true?
2. How does this procedure determine if the value in column B is true or
false?
3. How does the Offset procedure work. That is, why Offset(0,1) why not
(0,0). And Resize(1,6) instead of (0,6)?
4. How does the loop do its thing as I would also like to include a
procedure that deletes the formulas in the 5th column and replace them by
their value if the values in column A are true. (that is, how can I
incorporate a PasteSpecial (Value) in here?)
If someone can explain all this to me you'd be worth your weight in gold.
Therefore a lightweighted reply would be appreciated :)
Thankyou,
Rob
 
Hi Rob,
Could someone please explain what is happening with this procedure .
For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell
.Offset(0, 1).Resize(1, 6).Locked = .Value
End With
Next cell
1. Does it mean that the cells in columns C through H are all locked if the
Value in the corresponding row in column B is true?
Click to expand...

Yes, and unlocked in the value in column B is False
2. How does this procedure determine if the value in column B is true or
false?
Click to expand...

The 'For Each cell' tells the 'cell' variable to point to each cell in the
range B25:B1524 in turn.
The 'with cell' says 'using that cell...'
The '.Value' says 'get the value of the cell I'm using', which will be the
True or False bit.
3. How does the Offset procedure work. That is, why Offset(0,1) why not
(0,0). And Resize(1,6) instead of (0,6)?
Click to expand...

If you click on each of these and press F1, you should get the VBA help for
these functions, which says basically that the functions are:
Offset(<cells down>,<cells across>)
Resize(<number of rows>,<number of columns>)

So if 'cell' is B50, cell.Offset(0,1) is zero cells down and one cell across,
i.e. C50 and cell.Offset(0,1).Resize(1,6) is C50 resized to be one row high
and 6 columns wide, i.e. the range C50:H50.
4. How does the loop do its thing as I would also like to include a
procedure that deletes the formulas in the 5th column and replace them by
their value if the values in column A are true. (that is, how can I
incorporate a PasteSpecial (Value) in here?)
Click to expand...

The 'For Each cell' tells the 'cell' variable to point to each cell in the
range B25:B1524 in turn.
The 'with cell' says 'using that cell...'

Using the same .Offset method, we could do it thus:

For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell
'Lock/unlock C:H based on column B
.Offset(0, 1).Resize(1, 6).Locked = .Value

'If column A is True...
If .Offset(0, -1).Value = True Then

'Change column E to its value (instead of its formula)
.Offset(0, 4).Value = .Offset(0, 4).Value
End If
End With
Next cell

If you're going to be doing much more than that, though, it might be easier to
understand if you used a slightly different referencing mechanism. Instead of
basing everything as offsets column B, use indexes into the row:

For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell.EntireRow

'Lock/unlock C:H, based on column B
.Cells(1, 3).Resize(1, 6).Locked = .Cells(1, 2).Value

'If column A is True...
If .Cells(1, 1).Value = True Then

'... change column E to its value (instead of its formula)
.Cells(1, 5).Value = .Cells(1, 5).Value
End If
End With
Next cell

I find that easier to understand, as the .Cells(<row>,<column>) means that the
column numbes match those of the sheet, instead of having to work out that
.Offset(0, -1) means column A.

Hope that helps

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Yes Stephen, that's exactly what I wanted. AND, I really appreciate and
thank you for your time and expertise. (It's great getting answers to
problems via this group, but even better if an explanation is given with it
to help the beginners.)
Rob
 
Stephen (or anybody that can help),
I used your (Stephen's) second suggestion with some amendments as follows
and put it as Public Sub Worksheet_Calculate() in the worksheet modules
("ALL A-C") so that it would calculate after an entry was made:

Public Sub Worksheet_Calculate()
For Each cell In Sheets("ALL A-C").Range("A25:A1524")
With cell.EntireRow
If .Cells(1, 1).Value = True Then
.Cells(1, 6).Value = .Cells(1, 6).Value
End If
End With
Next cell
End Sub

For a reason I cannot work out, the procedure does not calculate unless I
make an entry to either columns I, K or L.
What is wrong with this?
Could you also explain the significance of the line, With Cell.EntireRow
(eg. why it's Cell and not Cells and why it isn't, or can't be, specific to
just the one cell in the row, namely the cell in column F that this
procedure is actually changing?)
Also, I don't understand the use of the period before the word Cells.
I would really appreciate any help on these matters.
Rob


Stephen Bullen said:
Hi Rob,


Yes, and unlocked in the value in column B is False


The 'For Each cell' tells the 'cell' variable to point to each cell in the
range B25:B1524 in turn.
The 'with cell' says 'using that cell...'
The '.Value' says 'get the value of the cell I'm using', which will be the
True or False bit.


If you click on each of these and press F1, you should get the VBA help for
these functions, which says basically that the functions are:
Offset(<cells down>,<cells across>)
Resize(<number of rows>,<number of columns>)

So if 'cell' is B50, cell.Offset(0,1) is zero cells down and one cell across,
i.e. C50 and cell.Offset(0,1).Resize(1,6) is C50 resized to be one row high
and 6 columns wide, i.e. the range C50:H50.


The 'For Each cell' tells the 'cell' variable to point to each cell in the
range B25:B1524 in turn.
The 'with cell' says 'using that cell...'

Using the same .Offset method, we could do it thus:

For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell
'Lock/unlock C:H based on column B
.Offset(0, 1).Resize(1, 6).Locked = .Value

'If column A is True...
If .Offset(0, -1).Value = True Then

'Change column E to its value (instead of its formula)
.Offset(0, 4).Value = .Offset(0, 4).Value
End If
End With
Next cell

If you're going to be doing much more than that, though, it might be easier to
understand if you used a slightly different referencing mechanism. Instead of
basing everything as offsets column B, use indexes into the row:

For Each cell In Sheets("ALL A-C").Range("B25:B1524")
With cell.EntireRow

'Lock/unlock C:H, based on column B
.Cells(1, 3).Resize(1, 6).Locked = .Cells(1, 2).Value

'If column A is True...
If .Cells(1, 1).Value = True Then

'... change column E to its value (instead of its formula)
.Cells(1, 5).Value = .Cells(1, 5).Value
End If
End With
Next cell

I find that easier to understand, as the .Cells(<row>,<column>) means that the
column numbes match those of the sheet, instead of having to work out that
Offset(0, -1) means column A.

Hope that helps

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
Click to expand...
 
Hi Rob,
For a reason I cannot work out, the procedure does not calculate unless I
make an entry to either columns I, K or L.
Click to expand...

Excel will only call the Worksheet_Calculate routine if it has something in
the sheet to calculate when you change a cell. In other words, only if you
have other formulas that refer to the cell you're changing. Just typing a
number into a cell won't trigger the routine.
Could you also explain the significance of the line, With Cell.EntireRow
(eg. why it's Cell and not Cells and why it isn't, or can't be, specific to
just the one cell in the row, namely the cell in column F that this
procedure is actually changing?)
Also, I don't understand the use of the period before the word Cells.
I would really appreciate any help on these matters.
Rob
Click to expand...

The 'cell' in question is just a variable, so we could just as easily write
the routine as:

Public Sub Worksheet_Calculate()
Dim oRobsCell As Range

For Each oRobsCell In Sheets("ALL A-C").Range("A25:A1524")
With oRobsCell.EntireRow
If .Cells(1, 1).Value = True Then
.Cells(1, 6).Value = .Cells(1, 6).Value
End If
End With
Next oRobsCell
End Sub

The 'With' statement is a VBA shortcut, to avoid having to repeat that call.
Within the With...End With block, anything that starts with a period (e.g.
.Cells) is applied to the 'With' item, so the same routine could be written
as:

Public Sub Worksheet_Calculate()
Dim oRobsCell As Range

For Each oRobsCell In Sheets("ALL A-C").Range("A25:A1524")
If oRobsCell.EntireRow.Cells(1, 1).Value = True Then
oRobsCell.EntireRow.Cells(1, 6).Value = _
oRobsCell.EntireRow.Cells(1, 6).Value
End If
Next oRobsCell
End Sub

In this example, you're iterating through the cells in column A, so the
.EntireRow is redundant, oRobsCell.Cells(1, 6) is the same as
oRobsCell.EntireRow.Cells(1, 6). The .EntireRow was there in the previous
example because in your original post, you were iterating through the cells
in column B, in which case the .EntireRow was used to return a range that
started in column A.

If you're getting into VBA now, I suggest you buy one of the many good books
about the subject, that will explain these for you. The best idea would be
to go to a local book store and browse through the Excel VBA titles to see
which seems to suit your reading style and level of knowledge.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Thank you very much Stephen. That helps perfectly and yes, a book on the
matter would be useful, in fact someone recently gave me John Walkenbach's
"Excel 2000 Power Programming" and I was trying to use it as a reference
book to show me how things were done without much success. But now I've
started reading from the beginning and VBA is making much more sense!
Even though it's a great book so far, more examples with explanations would
have been helpful. Maybe another book will do that?
Rob
 
Back
Top