Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

Worksheet_Change sub routine

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gandalph - 05 Apr 2008 12:31 GMT
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?
JP - 05 Apr 2008 13:02 GMT
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 <Ganda...@discussions.microsoft.com>
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
[quoted text clipped - 6 lines]
> 2.      Why       For Each cell in Target,     when sub routine gives only one
> cell reference?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.