Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Some basic questions from a VBA beginner

    Hello (this refers to VBA for XL 97):
    I know I could find answers to these questions if I searched hard enough in the texts I have bought (3 or 4 so far), but I figured someone could probably give me real quick answers. So here goes:
    1) I noticed that some of the modules I ahve created have more than one set of VBA code but most have only one. I know how code gets associated with the WS and the WKBK but what are the different ways that sets of code end up in a Module?
    2) I keep recalc on manual when I am making changes. I have noticed that F9 does not always work for all WS and all cells. I know about CNTRL, ALT, F9. But I am concerned about things recalculating when I set it to AUTO and give it to a user to run. Are the same cells that do not manually recalc going to present problems to my users when auto Calc is turned on?
    3) I am in VBE and I click on on one of the WS and "General" shows up in the Left-hand pane of the code-screen (or whatever it is called). I then click the down arrow next to the right-hand pane and I can select one of the CmdClick buttons I created and see the code -- no problem. I click on another WS and I now see the CmdClick Buttons listed in the Left-hand pane (accessable by clicking the down arrow) and the word "Click" appears in the right-hand pane. WHAT is this supposed to mean to me? I mean, is it supposed to be telling me something?
    4) Some times I see that one of my Pvt Tables does not look right. Like, there are no rows for one of the fields (no sub rows under a field), I have found that, if I double click within the PT (like on that field) that the rows will appear.
    I feel like I need to know what is going on with these things before I can start providing my models to users and collecting what may be due for my efforts.
    I would appreciate some advise (hopefully, it won't be go back to school or find another profession).
    Thanks in advance to whomever is kind enough to answer.
    Stephen <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some basic questions from a VBA beginner

    I'll try to help with the first two:

    1- A module in the Modules collection is usually created by you by selecting Module from the Select menu. You can add as many modules as you want, and those are just used to manage your code the way you want to. You can group your code into modules is ways that make it easy for you to find it. If you are creating these macros by recording them, then the macro recorder may put each one you record in a new module. I'm not sure as I don't normally create macros by recording them.

    2- You are using Excel 97, but you didn't say what service level you have applied. Excel 97 have MANY bugs in recalculate which caused cells to not recalculate when they were supposed to. I think all of those were fixed in service releases (If I remember correctly, there were three for 97). If you do not have the latest Service Release, I strongly recommend installing it.

    3- I am not sure I understand this question. The only thing I can think of is that you have a procedure named Click in that module.

    4- I don't know much about pivot tables, so I can't help with this.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Some basic questions from a VBA beginner

    Hi Stephen,

    A brief attempt to throw a little light on your queries,

    1 Most likely at each new session of Excel, a new module is created for the first macro you record for that session, in that particular workbook. You can manually re-organize the actual modules if you like to keep things in order, in fact it is good practice to do so. By organizing your modules you can have certain variables available to all macros in that module by declaring them at the top before any sub procedures.

    2 What sort of recalculation problems are you having, i.e. are there any particular calculation types that fail to update. If you base any criteria on formatting, they most likely will not recalculate without special provisions being made.

    3 I think what is happening here is simply that Excel (VBA) is providing you with a skeleton should you wish to place some code in those events, such as Click, Key Press etc. Which events are available depends on what controls etc are in the sheet. Unless you have a particular use for them, just ignore.

    4 The Pivot Table problem I do not fully understand. If you could post a sample of a table that displays the behaviour you describe, may I, or somebody else, could explain what is happening.

    Hope some clarification can be had from that,

    Andrew

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some basic questions from a VBA beginner

    Legare:
    I am experiencing the problems at work, where we have XL97 SR2. I have XL97 SR2(1) at home. I do not know if I would have the same problems at home. However, I tried to convince our Systems people to get SR2(1). They checked the MS Download site and saw where MS still is providing a download fix for Recalculation problems for SR1 saying that the fix is included with SR2. So, they do not see any reason to get SR2(1). Do you know whether SR2(1) provides any additional fixes for this problem (that are not included in SR2). I think I need to provide some evidence that we need SR2(1).
    Thanks to you and Andrwe for your comments on my other questions. The clouds are begining to lift ever so slowly.
    <img src=/S/snail.gif border=0 alt=snail width=21 height=17>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some basic questions from a VBA beginner

    The SR for 97 was too long ago for me to remember for sure what they were. However, I don't think there were any additional Recalculate fixes between those two releases.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some basic questions from a VBA beginner

    Hi,

    Check out the archives of Woody's Office Watch (click on thw W*W archives at the bottom on any page in the forum). It was very well covered when it was released. I tried a search in there just now, and I didn't get a response though- you might have to check out the indexes manually.

    I have a feeling SR-1 was too buggy to continue. It was put out far too quickly.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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