Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB routine to print to PDF? | Excel Worksheet Functions | |||
Sort Routine | Excel Discussion (Misc queries) | |||
simplifying routine | New Users to Excel | |||
How to create a routine | Excel Discussion (Misc queries) | |||
Solver VBA routine | Excel Discussion (Misc queries) |