Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macros and VBA (Excel 97)

    Need help. I have read Steven Roman's book on excel macros and I have also read VBA for Dummies. And, I have picked-up another book on Excel programming. However, I am still somewhat confused on how to edit the recorded macros and actually create a procedure. That being said, does anybody have any suggestions on how to proceed? I thank you in advance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros and VBA (Excel 97)

    How to enter a macro (=VBA code):

    1. Open the Excel file you want to add the code to.

    2. Choose Tools, Macro, Visual Basic Editor to open the
    Visual Basic Editor (or press Alt+F11). This is the
    design environment that stores the VBA code. If this is
    the first time you have opened the editor, you will
    probably see three windows: the Project window, the
    Properties window, and the Code window.

    3. In the Project window, select the name of the workbook
    you want to add the sub to (one project is listed for
    each open file).

    4. After selecting the project icon, choose Insert, Module.
    This inserts a VBA code module into the project and
    places the insertion point in the Code window of that
    module.

    5. To start writing a macro, simply start typing it's name, preceded by the keyword Sub:

    Sub MyFirstMacro
    press Enter after that and you get:

    Sub MyFirstMacro()
    End Sub

    Automatically

    Type the VBA lines between those two.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros and VBA (Excel 97)

    This is a huge question. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Recording macros to mimic keystrokes is the trivial part. To write code/VBA independently takes much more knowledge since you need to know the Excel (or whatever other program you're working with) model and what functions are made available to you as a programmer. (Of course this is also where the real power of VBA shows up.) By looking at the code generated by recording, you can get a glimpse of what the "model" looks like but there are many more things that can be done that won't show up in a recorded macro.

    Once you're more experienced you'll rarely record macros to get you started (you may do this as a starting point to figure out what function to call for something you haven't done before). You'll have acquired enough background in the Excel model to do this on your own. Knowing what functions to call when and how.

    All basic Excel VBA books (as well as the help file) will show you what the "model" is. By model I mean what the inners of Excel are structured from a programmer's point of view (the API) and how they relate to each other. For example, Excel (the Application) can run 'n' workbooks at a time. Each workbook contains 'n' worksheets (and maybe chart sheets) and each worksheet contains rows, columns and individual cells (all of which may or may not contain data). To read/write and manipulate each of these things (e.g., data in a cell, worksheets in a given workbook, charts on a worksheet) you need to learn the available functions provided by the Excel library.

    I personally learned by reading a few books, reading the help files and using the Immediate window. You can record a simple macro (say to enter 5 values, add them up and color the total cell blue). Now look at this code in the editor (Alt-F11) and try to understand what it's doing. You can put your cursor on an item in the code, press F1 for the help file. For example if you set the cell background to blue, try and set it to red by reading in the help file. Another thing is using the de###### to step through the code. You can run it by clicking your cursor anywhere in the recorded code (between Sub and End Sub) and press F8. This highlights the current line of code, press F8 to step to the next line. If you have both the Excel window and the editor window sitting side by side on your monitor, it'll be easy to watch the code run and do its thing (entering the 5 values, adding them and making the total cell blue).

    I also used the Immediate window a lot (press Ctrl-G to open it) to see what functions are available. I'd enter "Application." and then see what shows up after the "." character. Most of them are pretty obvoius what they do like Application.Version but others are't so obvious. So put your cursor on that term (anywhere in the word .Version, for example), and press F1. I'd go through each of the main parts of the model and press F1 on each field I didn't immediately understand.
    <pre>Application.
    Workbooks.
    Thisworkbook.
    Cells.
    Range.
    Sheets.</pre>

    Just like learning anything new, there is no quick way to fill your brain with this stuff but that's what I suggest.

    Deb <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Macros and VBA (Excel 97)

    In addition to this excellent advice, see my recent <post#=212244>post 212244</post#> to another beginner, and follow those links.

    The best way to learn is go back to those two books you have and start entering the examples, and then tinkering with them to see how it works, and then posting here for help. Don't be alarmed or intimidated that some of the discussion in the Excel Forum is complex and highly technical; we like to help beginners too (I'm not that far along in coding ability .. or others might argue, anything else). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros and VBA (Excel 97)

    The two best Excel books, for learming are:

    1. John Walkenbach's Power Programming book.
    2. The Wrox Press book by Steve Bullen, John Geen, etc.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros and VBA (Excel 97)

    >>>The two best Excel books, for learming are:

    do they do spelling as well?? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macros and VBA (Excel 97)

    THey do not include chapters of typing (not data types), so perhaps Mavis Beacon should be added as a co-author,

Posting Permissions

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