Re: Declaring Objects

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 03/14/05 

Date: Mon, 14 Mar 2005 09:26:55 -0000

Sharon,

The problem seems to lie with the line

Set Colors = Colors.Range("B1:Q400", "S1:AI400")

What this is saying is set the Colors variable to refer to the Colors sheet,
ranges ("B1:Q400" and "S1:AI400". Note that it is the worksheets codename
that you are using, not its Excel name. If you really have a worksheet
codename of Colors, you have a conflict as you have 2 objects with the same
name. You can either use

Set Colors = Range("B1:Q400", "S1:AI400")

if you just want to pick up the activesheet, or

Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400")

if you want to refer explicitly to a worksheet (a good practice) with the
Excel name of Colors, or

Set Colors = wsColors.Range("B1:Q400", "S1:AI400")

if you set the worksheet codename to wsColors.

BTW, this sort of conflict is partly why many of us use naming conventions
for our variables, and objects. I use sName for string variables, cName for
counting variables, iNAme for indexing variables, and oName for objects. For
objects , I sometimes further qualify like oRngName, or oWsName, for greater
clarity. 

-- 
HTH
RP
(remove nothere from the email address if mailing direct)
"Sharon" <Sharon@discussions.microsoft.com> wrote in message
news:3C6B22C0-B426-45D2-9810-AF4AE29D1A35@microsoft.com...
> I hope this post makes sense.  I am trying to learn VB on my own and I
know
> that I need to declare my objects and know that I would prefer to declare
> them early in the code.
>
> I want to write a macro that does a find and replace for "Green",
"Yellow",
> "Red", and "No" on Sheet1 and replaces the words with the values 1, 2, 3,
4,
> respectively.  The range of my data is from B1:Q400 and S1:AI400.  I named
> the non-contiguous range "Colors".  I would like to use the name of my
range
> in my code.
>
> Below is my first attempt to writing a macro, partially using the macro
> recorder.  The code the recorder gave me was:
>
> Sub FindAndReplace()
>
>     Selection.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>
> End Sub
>
> Since I don't want the user to have to select the range each time to run
the
> macro, I want to change the code to apply to the range I named "Colors".
So
> here's my first attempt at declaring my "Colors" range as my object.
Except,
> since I don't really know what I'm doing, it doesn't work.
>
> Can anyone help me declare my range "Colors"?
>
> Sub FindAndReplace()
>
> Dim Colors As Range
> Set Colors = Colors.Range("B1:Q400", "S1:AI400")
>
>     Colors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
> End Sub
>
> Thanks so much,
>
> Sharon
>

Relevant Pages

  • Declaring Objects
    ... that I need to declare my objects and know that I would prefer to declare ... Below is my first attempt to writing a macro, ... The code the recorder gave me was: ... Sub FindAndReplace() ...
    (microsoft.public.excel.programming)
  • Re: Converting C to Delphi
    ... macro and a million constants. ... Can this be imported into delphi or do I ... > Perform those calculations yourself and declare the constants with the ... One is temp, ...
    (alt.comp.lang.borland-delphi)
  • Re: Loop problem
    ... (declare (special a b)) ... will need a macro to type in the nest of dolists for you. ... The macroexpander will say "Oh I'm not finished yet." ...
    (comp.lang.lisp)
  • Re: VBA Word 2003 File Close
    ... It would be better to declare a variable as a Document and set that variable to the document that you are opening rather than rely on the active document. ... In a Word 2003 template document I have a macro triggered by a CheckBox ... document and then is supposed to close that second document. ... I get a run-time error 4198 "Command Failed" at the line that ...
    (microsoft.public.word.vba.general)
  • Re: one simple question
    ... You'll most likely find a macro called NULL in one of the Visual C++ ... this doesn't declare a function. ... Empty brackets would be equivalent to replacing the "null" with "void", ... also making this a function declaration. ...
    (comp.lang.cpp)