Excel Macro Variable Scope Problems

  • Hello all. I am a web designer/developer and am familiar with programming and variables, but I'm totally new to building macros and VB. This forum has answered many of my questions as I've been learning. So please forgive the potentially naive problem I'm having.

    Without boring you with too much detail, basically I'm consolidating tens of thousands of patient records from dozens of separate RTF files into a single Excel spreadsheet so that it can be compared with what already exists. (Please don't question the logic or practicality -- it's what the client wants. :))

    To sort of explain the idea behind the code snippet below, each of the original RTF files represents all the patients from a particular dentist office. And these files get imported complete with page numbers. So the macro I'm writing looks for "Page 1" and knows it's the beginning of a new location. So then I want to grab the location info and apply it to all the patients it finds in that range.

    Everything in the code works as expected except that the variable "LocName" doesn't seem to play nice. It works correctly for the very top of the file, but then I get blanks everywhere else.

    And if I replace the line where it's supposed to use the "LocName" variable

    Selection.Offset(0, 1).Value = LocName

    with a static string such as

    Selection.Offset(0, 1).Value = "Testing"

    the macro totally works like you'd think it would. So, I'm thinking it must be that the variable doesn't scope correctly somehow.

    Anyway, here's the code.

  • Re: Excel Macro Variable Scope Problems

    Have you tried dimensioning it as a Variant type? Not the best solution, but it might work.

  • Re: Excel Macro Variable Scope Problems

    These pages should help
    Excel VBA Variables
    Excel VBA Variables Scope and Lifetime

    Step through your code with F8 and hover over "ActiveCell.Value" at this line

    LocName = WorksheetFunction.Trim(ActiveCell.Value)

    It probably isn't what you think

  • Re: Excel Macro Variable Scope Problems

    Okay, so I set a watch on the variable and stepped through -- it turns out the code was doing exactly what I wanted. However, since I added a fake Page 1 at the end of everything -- it looped through one more time than intended and reset all except the first page to a blank. Ooops.

    Thanks so much for your help, guys.

  • Re: Excel Macro Variable Scope Problems

    Also, try to cut down on all those Select and Activate statements.

    For example (not tested though):



    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!