+ Reply to Thread
Results 1 to 3 of 3

Syntax for passing ranges as variables

Hybrid View

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Syntax for passing ranges as variables

    I'm trying to make my code more generic. There's a range that I refer to multiple times within my code that I'd like to set as a variable. That way when this hard-coded range needs to be updated, I only have to make the change in one line of code (actually two - but it's the same concept for two different ranges) instead of several or many.

    Here's what I'm playing with. I'd like to update the first two lines with the group of four lines commented below, but I can't quite get the syntax correct when those variables are called in the last two commented lines.

        '-------Hard coded for now:  Need to ID your color bar cells and Z-property cells--------------
        Set rngColorBar = Range("E67", Range("E67").End(xlDown))
        Set rngZprop = Range("D67", Range("D67").End(xlDown))
        
        '-------preferably this would be written with variables, so you only have to change the Range at the top, not in several places.  But, this isn't working yet.
        'FirstColorBarCell = Range("E67")   'User updates this range reference manually, recompiles sub, and runs it.
        'FirstZpropCell = Range("D67")      'User updates this range raference manually, recompiles sub, and runs it.
        'Set rngColorBar = Range(FirstColorBarCell, Range(FirstColorBarCell).End(xlDown))
        'Set rngZprop = Range(FirstZpropCell, Range(FirstZpropCell).End(xlDown))
    Any ideas?

    Thanks,
    g

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Syntax for passing ranges as variables

    You basically have it right as long as FirstColorBarCell is declared as a range. Ranges are objects, with objects you have to use to the term set.

    Dim FirstColorBarCell as Range
    Set FirstColorBarCell = Range("E67")
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Syntax for passing ranges as variables

    Solus, your comment "Ranges are objects, with objects you have to use the term set" was key to my understanding. Thanks.

    My final code ended up a little bit differently. I assign a string (including quotation marks and cell reference), and use that as a variable in the range definitions that follow.


        Dim strFirstColorBarCell As String
        Dim strFirstZpropCell As String
        Dim rngColorBar As Range
        Dim rngZprop As Range
        
        strFirstColorBarCell = "E67"    '-------user defined variable----edit and recompile as needed---------
        strFirstZpropCell = "D67"       '-------user defined variable----edit and recompile as needed---------
        
        Set rngColorBar = Range(strFirstColorBarCell, Range(strFirstColorBarCell).End(xlDown))
        Set rngZprop = Range(strFirstZpropCell, Range(strFirstZpropCell).End(xlDown))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Passing Variables to Sub
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2010, 01:28 AM
  2. Syntax for Passing Arrays to Functions
    By ffffloyd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-21-2009, 08:34 PM
  3. Passing variables
    By lordfa9 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-17-2007, 06:33 AM
  4. [SOLVED] Passing variables from one sub to another
    By Yasha Avshalumov in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-19-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1