none
excel vba macro tutorial RRS feed

  • Question

  • Can anyone recommend to me a good tutorial on writing VBA macros for Excel?  I find a lot of stuff on line but I haven't found anything I like.  Most of them want to jump right into illustrating a simple macro.  I'd like to start with the basics.  Where are macros kept?  With the xls file or some place else?  Are alll macros available to all xls files?  Can you have macros which are automatically run when an xls file is opened/closed?  Where is the doc for objects which are available to VBA programmers?  (Is that what the Excel Object Model Reference is all about?  If so I am unable to find any objects for general file i/o.) 

    There must be something but the highest ranked Google hits are disappointing.

    Thanks,  Bob

    Saturday, January 28, 2012 5:03 PM

Answers

  • Hi Bob,

     

    First, let me see if I can answer some of your questions.

    VBA macros for Excel are stored can be stored in a couple of different places.  If the code will be used only for a specific workbook, you store the code in the workbook.  If the code is general purpose (for example, adding company logo and page numbers to every workbook) you store the code in a special file called Personal.xls (Excel 2003 and below) or Personal.xlsb (Excel 2007 and beyond). This file is automatically created the first time you record a macro and tell it to store the macro in the personal macro workbook.

    Please note that all macro code in any open workbook on the machine is available to any other open workbook on the machine.

    You are correct, the Object model reference helps you explore all of the different objects that are available to your VBA code.  At the top of the model is the Excel.Application object.  Underneath that is a hierarchy of objects.  For example, each Excel application hosts 0 to many workbooks. Each workbook has worksheets, each worksheet has columns, rows, etc.  You can use the Object Browser (available from the View menu in the VBA development environment) to search through the hierarchy of available objects and learn what you can do with them.

    If you haven't seen the VBA development environment, it is accessible from the Tools menu in Excel 2003 and below, or from the Developer tab in Excel 2007 and beyond.  You might have to "enable" the developer tab in program options to see it.

    One of the other things you should try is to record a simple macro in Excel.  I often have my students record a macro that changes the background color of a column, then changes the font size and color.  Then, go into the VBA editor and take a look at the code you recorded.  Since you know what you recorded, you can often make sense of the code.  VBA is very English like in nature.  I recommend that when you read it, you start from the end of the statement and work your way left.

    For example, consider this code:  ActiveCell.Font.Color = vbRed

    In this line of code, you are taking a contant (a friendly name that represents the number that represents the color red) and assiging it to the color property for the font (as opposed to the background color for the cell itself) of the cell which is currently active.

    Once you get used to reading the code, then the links on Google or other search engines become more helpful. You can use them to learn how to make decisions in code, store values in memory temporarily, loop through all rows automatically to process data, etc.

     

    I am a Microsoft Certified Trainer.  One of my specialties is teaching VBA programming.  You might want to consider an introductory VBA course (many training centers offer 1 or 3 day courses).  If you are comfortable learning by reading and working through exercises, you might want to consider these titles:

     

    http://www.amazon.com/Introduction-VBA-Excel-Steven-Chapra/dp/013239667X/ref=sr_1_4?s=books&ie=UTF8&qid=1327772019&sr=1-4

    http://www.amazon.com/Excel-24-Hour-Trainer-Wrox-Programmer/dp/047089069X/ref=sr_1_20?s=books&ie=UTF8&qid=1327772282&sr=1-20

     

    I have used and recommended these titles to my students. If you take a look at these links and don't find them to suit your needs, please let me know. I can recommend others based upon your learning style.

     

    Good luck Bob!

     

    Please mark this as an answer if you found this post helfpul.

     

    Mike Corkery, MCT, MCPD, MCTS, MCITP, MCSD, MCAD, etc.

    • Marked as answer by eBob.com Monday, January 30, 2012 1:54 PM
    Saturday, January 28, 2012 5:45 PM
  • Hi Bob,

    First, here are some online resources that I often recommend to my students. These first three cover introductory to intermediate topics. Each has a different look and approach.

    http://www.excel-vba.com/vba-prog-1-1-editor.htm

    http://www.excel-vba-easy.com/excel-vba-programming.html

    http://www.functionx.com/vbaexcel/

     

    Here is a link to the Microsoft MVP program page for Excel MVPs. Each of the MVP's has a web site with content.  Some have great tutorials and some have collections of sample code that you can dowload. Many of these sites cover some of the more esoteric subjects.  I point all my students to this site and encourage them to find and bookmark a few that have style and content in a format that they can relate to.

    http://mvps.org/links.html#Excel

     

    With regards to working with the file system, clipboard, etc.  these are not necessarily part of the object model for Excel. If you open up the VBA editor in Excel, go up to the Tools menu and select References.  Even in a project with no code, you will see check marks next to several files. Each of these represent an object library.  For instance, not only do you need to work with the Excel objects in VBA for Excel, you also need a reference to the library that defines the actual VBA language contructs, etc.

    To access the file system, you would need to add a reference to the library that contains the appropriate objects. In this case, you add a reference to the Microsoft Scripting Runtime. This is the library that contains the various objects that will let you do all sorts of cool things with files and folders.

    For things like the clipboard, you do not necessarily need to access it directly. The easiest way to learn about this feature is to record a macro.  Here is a snippet of code where I recorded myself copying and pasting some cells. Selection.Copy copies data to the clipboard while the Paste method of the active sheet gets data from the clipboard.

    Sub Test()
        Range("C11:C13").Select
        Selection.Copy
        Range("G15").Select
        ActiveSheet.Paste
    End Sub

    For many of the more advanced or esoteric features of VBA, you will not find a single source of information that covers everything you need to know. You often start by recording a macro, then editing it to customize the behavior, then searching the web when you get stuck.

     

    Here is one more link that I want to share with you:  http://www.jpsoftwaretech.com/vba-search-engine/

    The owner of this site has created a search engine (using Google custom searches) that specifically searches sites which publish VBA related content.  If you take only one thing away from this conversation, this is the link to bookmark.

    Good luck!  Tag, you're it!

     

    Please mark this as an answer if you found this post helfpul.

     

    Mike Corkery, MCT, MCPD, MCTS, MCITP, MCSD, MCAD, etc

     

     

    • Proposed as answer by Shasur Monday, January 30, 2012 8:47 AM
    • Marked as answer by eBob.com Monday, January 30, 2012 2:02 PM
    Sunday, January 29, 2012 4:21 PM

All replies

  • Hi Bob,

     

    First, let me see if I can answer some of your questions.

    VBA macros for Excel are stored can be stored in a couple of different places.  If the code will be used only for a specific workbook, you store the code in the workbook.  If the code is general purpose (for example, adding company logo and page numbers to every workbook) you store the code in a special file called Personal.xls (Excel 2003 and below) or Personal.xlsb (Excel 2007 and beyond). This file is automatically created the first time you record a macro and tell it to store the macro in the personal macro workbook.

    Please note that all macro code in any open workbook on the machine is available to any other open workbook on the machine.

    You are correct, the Object model reference helps you explore all of the different objects that are available to your VBA code.  At the top of the model is the Excel.Application object.  Underneath that is a hierarchy of objects.  For example, each Excel application hosts 0 to many workbooks. Each workbook has worksheets, each worksheet has columns, rows, etc.  You can use the Object Browser (available from the View menu in the VBA development environment) to search through the hierarchy of available objects and learn what you can do with them.

    If you haven't seen the VBA development environment, it is accessible from the Tools menu in Excel 2003 and below, or from the Developer tab in Excel 2007 and beyond.  You might have to "enable" the developer tab in program options to see it.

    One of the other things you should try is to record a simple macro in Excel.  I often have my students record a macro that changes the background color of a column, then changes the font size and color.  Then, go into the VBA editor and take a look at the code you recorded.  Since you know what you recorded, you can often make sense of the code.  VBA is very English like in nature.  I recommend that when you read it, you start from the end of the statement and work your way left.

    For example, consider this code:  ActiveCell.Font.Color = vbRed

    In this line of code, you are taking a contant (a friendly name that represents the number that represents the color red) and assiging it to the color property for the font (as opposed to the background color for the cell itself) of the cell which is currently active.

    Once you get used to reading the code, then the links on Google or other search engines become more helpful. You can use them to learn how to make decisions in code, store values in memory temporarily, loop through all rows automatically to process data, etc.

     

    I am a Microsoft Certified Trainer.  One of my specialties is teaching VBA programming.  You might want to consider an introductory VBA course (many training centers offer 1 or 3 day courses).  If you are comfortable learning by reading and working through exercises, you might want to consider these titles:

     

    http://www.amazon.com/Introduction-VBA-Excel-Steven-Chapra/dp/013239667X/ref=sr_1_4?s=books&ie=UTF8&qid=1327772019&sr=1-4

    http://www.amazon.com/Excel-24-Hour-Trainer-Wrox-Programmer/dp/047089069X/ref=sr_1_20?s=books&ie=UTF8&qid=1327772282&sr=1-20

     

    I have used and recommended these titles to my students. If you take a look at these links and don't find them to suit your needs, please let me know. I can recommend others based upon your learning style.

     

    Good luck Bob!

     

    Please mark this as an answer if you found this post helfpul.

     

    Mike Corkery, MCT, MCPD, MCTS, MCITP, MCSD, MCAD, etc.

    • Marked as answer by eBob.com Monday, January 30, 2012 1:54 PM
    Saturday, January 28, 2012 5:45 PM
  • Thanks for a great reply Mike.  Unfortunately I am out of the country for several weeks and books are not an option.  What's the best online material you can recommend?

    Also, the object reference I've found is the Excel Object Model Reference.  It doesn't seem to contain objects for accessing things like files, the file system, clipboard, etc..  Where are those objects documented? 

    Thanks again,  Bob

    Sunday, January 29, 2012 12:56 PM
  • Hi Bob,

    First, here are some online resources that I often recommend to my students. These first three cover introductory to intermediate topics. Each has a different look and approach.

    http://www.excel-vba.com/vba-prog-1-1-editor.htm

    http://www.excel-vba-easy.com/excel-vba-programming.html

    http://www.functionx.com/vbaexcel/

     

    Here is a link to the Microsoft MVP program page for Excel MVPs. Each of the MVP's has a web site with content.  Some have great tutorials and some have collections of sample code that you can dowload. Many of these sites cover some of the more esoteric subjects.  I point all my students to this site and encourage them to find and bookmark a few that have style and content in a format that they can relate to.

    http://mvps.org/links.html#Excel

     

    With regards to working with the file system, clipboard, etc.  these are not necessarily part of the object model for Excel. If you open up the VBA editor in Excel, go up to the Tools menu and select References.  Even in a project with no code, you will see check marks next to several files. Each of these represent an object library.  For instance, not only do you need to work with the Excel objects in VBA for Excel, you also need a reference to the library that defines the actual VBA language contructs, etc.

    To access the file system, you would need to add a reference to the library that contains the appropriate objects. In this case, you add a reference to the Microsoft Scripting Runtime. This is the library that contains the various objects that will let you do all sorts of cool things with files and folders.

    For things like the clipboard, you do not necessarily need to access it directly. The easiest way to learn about this feature is to record a macro.  Here is a snippet of code where I recorded myself copying and pasting some cells. Selection.Copy copies data to the clipboard while the Paste method of the active sheet gets data from the clipboard.

    Sub Test()
        Range("C11:C13").Select
        Selection.Copy
        Range("G15").Select
        ActiveSheet.Paste
    End Sub

    For many of the more advanced or esoteric features of VBA, you will not find a single source of information that covers everything you need to know. You often start by recording a macro, then editing it to customize the behavior, then searching the web when you get stuck.

     

    Here is one more link that I want to share with you:  http://www.jpsoftwaretech.com/vba-search-engine/

    The owner of this site has created a search engine (using Google custom searches) that specifically searches sites which publish VBA related content.  If you take only one thing away from this conversation, this is the link to bookmark.

    Good luck!  Tag, you're it!

     

    Please mark this as an answer if you found this post helfpul.

     

    Mike Corkery, MCT, MCPD, MCTS, MCITP, MCSD, MCAD, etc

     

     

    • Proposed as answer by Shasur Monday, January 30, 2012 8:47 AM
    • Marked as answer by eBob.com Monday, January 30, 2012 2:02 PM
    Sunday, January 29, 2012 4:21 PM
  • I tried Chandoo's course in VBA & found it awesome, & great value for money too.

    http://chandoo.org/wp/vba-classes/

    • Proposed as answer by Shasur Monday, January 30, 2012 8:47 AM
    Monday, January 30, 2012 12:38 AM
  • I tried Chandoo's course in VBA & found it awesome, & great value for money too.

    http://chandoo.org/wp/vba-classes/


    Thanks xbuffer.  I appreciate the info but much too expensive for me.

    Bob

     

    Monday, January 30, 2012 2:05 PM