+ Reply to Thread
Results 1 to 5 of 5

Represent (define?) a section of code with a variable (shorten long code lines)?

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    Long Beach, California
    MS-Off Ver
    Excel 2010
    Posts
    47

    Represent (define?) a section of code with a variable (shorten long code lines)?

    Hi, all --

    I'm not having much luck searching the forum -- I think because I may not be using the word "variable" correctly.
    I'm hoping someone will get past my (mis)?use of terminology and grasp what I'm shooting for.

    I've got a macro that uses a lot of cell.Offset(x,y).Value statements.
    Without going into too much excruciating detail, it basically loops through a column, looks at the data in the same row in a different column, and then inserts that data into yet another column.


    Here's the code in question:
    Please Login or Register  to view this content.
    I'm going to have to do 5 or 6 more columns worth of this.
    It's going to be a little time-consuming to type out each and every cell.Offset(x,y).value statement, and (for me anyway) it's going to get a little confusing to keep track of the various Offsets so that the right cells are being read and written to.
    What I'd like to do is assign a variable "name" to each one. For example, cell.Offset(0,2).value refers to cells in a column titled "studio."
    The idea I had was to define a variable that would represent that snippet of code.

    Thus, instead of this section:
    Please Login or Register  to view this content.
    I could have something that looked like this:
    Please Login or Register  to view this content.
    To that end, I've tried a number of variations on:
    Please Login or Register  to view this content.
    But it doesn't work; the code just ignores the "StudioA" and "StudioB" section.
    Obviously defining them as Strings isn't working.
    I tried defining them as Ranges and using this:
    Please Login or Register  to view this content.
    but that just errors out.

    Can this be done and I'm just missing something, or am I attempting something that Excel just doesn't do?
    Fingers crossed that it's me and not Excel

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Represent (define?) a section of code with a variable (shorten long code lines)?

    I have good news and bad news. The bad news is...it's you The good news is I can show you how to do this and then you will learn something

    First I suggest indenting your code to show the structure.

    You can set a variable like this
    Please Login or Register  to view this content.
    But the value of StudioA won't automatically change if Cell changes. You have to update the variable every time. This mean that you haven't really gained anything in terms of shortening code.

    However, you can use a Function to do this. You can use it in the comparison and also to build the string. Changes to your code shown in red.

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-25-2011
    Location
    Long Beach, California
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Represent (define?) a section of code with a variable (shorten long code lines)?

    Oh, OUTSTANDING!
    Works like a charm! Thanks for your solution, and for the lesson.

    Have a great weekend, Jeff; my Friday just got a whole lot better!

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Represent (define?) a section of code with a variable (shorten long code lines)?

    You're welcome and thanks for the rep!

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

  5. #5
    Registered User
    Join Date
    05-25-2011
    Location
    Long Beach, California
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Represent (define?) a section of code with a variable (shorten long code lines)?

    Whoops! Knew I forgot something. Thanks again!

+ 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. [SOLVED] Shorten the lines of code
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2015, 02:43 PM
  2. [SOLVED] Splitting very long code over multiple lines in the editor....
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2014, 08:22 AM
  3. Loop to shorten repetitive and long code?
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 03:29 AM
  4. Replies: 4
    Last Post: 06-28-2012, 12:42 AM
  5. How to shorten my msgbox code or show on mutiple lines?
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2012, 06:54 PM
  6. Break long lines of code, Map enter key with OnKey
    By capngene in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2012, 08:22 AM
  7. Break long range into 2 code lines
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-13-2011, 01:50 PM

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