Worksheet_Change sub routine
 

I am trying to understand various points of the programme listed below and
given in a book as an example (I have deleted some of the lines of code not
necessary to this question, and commented some of the lines €“ the original
had no commenting):-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant €˜returns TRUE or text string
Set VRange = Range("InputRange") €˜InputRange is defined area on spread
sheet
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then €˜is the cell in
InputRange
ValidateCode = EntryIsValid(cell)
If ValidateCode = True Then
Exit Sub
Else
€˜This outputs message of reason not valid entry
End If
End If
Next cell
End Sub

Private Function EntryIsValid(cell) As Variant
' Returns True if cell is an integer between 1 and 12 or blank
' Otherwise it returns a string that describes the problem
€˜ i.e. entry is Text, 12 or Not an Intiger
End Function

My queries are
1. Why the line Set VRange = Range("InputRange"), why not use the
expression Range("InputRange") ?
2. Why For Each cell in Target, when sub routine gives only one
cell reference?

Worksheet_Change sub routine
 

1. Do you mean why does the code use a "Set" statement, or why doesn't
the code just use Range("InputRange") to refer to the range in the
later parts of the code?

Since VRange is a Range Object, you have to use the "Set" keyword to
assign an object reference (the InputRange) to it. This makes the code
more readable by allowing you to use a descriptive name, makes it
easier to reference other objects (i.e. VRange.Cells(1)), and makes it
easier for someone else reading your code to understand what is going
on. Setting object references is just good programming practice.

2. "Target" is a Range object and this can be any number of cells. You
have to account for that with a "For Each" loop. If it happens to only
be one cell, then the loop only runs once.


HTH,
JP

On Apr 5, 7:31 am, Gandalph
wrote:
I am trying to understand various points of the programme listed below and
given in a book as an example (I have deleted some of the lines of code not
necessary to this question, and commented some of the lines - the original
had no commenting):-


My queries are
1. Why the line Set VRange = Range("InputRange"), why not use the
expression Range("InputRange") ?
2. Why For Each cell in Target, when sub routine gives only one
cell reference?