Here is some recommendations on how to start programming (hope its not a really big lecture). This may be useful for other colleagues on this forum as well.
To start of I would recommend a book by Sybex - Mastering Excel 2003 Programming with VBA. Its the book I have used and have not found a need to refer to any other. So I really cant comment on other books.
First and most important point to note is that you dont need to know everthing to work with excel and vba. Also if you know how to do something dont repeat yourself. It becomes very routine.
1) First learn the basics about navigating using excel within the Excel spreadsheet. Explore all options under the file menu to get familiar with using the excel application. Opening, Closing, Saving files. Cut, Copy and Paste. Find and Replace. Inserting, Deleting sheets, Hyperlinks, Charts, Formatting rows columns and Cells, Sort, Filter, Advanced Filter, Conditional Formatting etc.
2) Next update your basics with identifying between different cell formats. i.e. number, text, currency, general, font, colors, allignment, borders, etc. This will give you an idea as to how to present your data.
3) Next start with learning how to use formulas. From the file menu select. Insert Function and select All functions. Go through each formula and start reading about how to use formula in your spreadsheets. There is a link at the bottom which says "Help on this function". It will give you all the information that you need to learn. For most basic users vba is not necessary, they can solve most problems by just using formulas. Few important ones are AVERAGE, CONCATENATE, SUM, COUNT, DATE, FIND, IF, LEFT, LEN, MID, RIGHT, OR, AND, OFFSET, RAND, REPLACE, ROW, COLUMN, SEARCH, SUBSTITUTE, VLOOKUP, VALUE,
TRIM, TRANSPOSE etc.
4) Once you get to this stage. You are ready to start with VBA. VBA gives you the ability to all of the tasks mentioned above and more programatically. To get you started quickly you need to understand about how to declare variables and differentiate between different data types. You should be able to differentiate between properties, methods, collections and objects. You need to know where to run functions or subprocedures from, i.e. either in modules or userforms or as events. This is the base of vba. Once you get this part the rest is easy.
5) Start using Macro recorder to record vba code into Excel. Modify the recorded code according to your requirements.
6) To be quick in writing VBA programs you need to know where to look for information. Here are few important tips.
a) Object browser - most important for syntax and examples. You will find almost everything about excel vba here. If you need the syntax just right click each property or method and click help. It will give you the required info. For all the recorded macros you can look up the properties in Object browser.
b) Learn how to debug - how to use breakpoints, watch window, immediate window, locals window and properties window.
Once you get to this stage you are almost ready to write any program. The easiest way to improve your skills is to logon to some forums and try solving others problems. Read code from other experts on the forum to get an understanding how they approach each problem, you will pick up a lot of useful tips.
Always look at minimizing the amount of code you write. Try solving other problems and post your code on the website, the experts will always advice you on how to improve on your code or where you are going wrong or an easier way to solve the issue. Its all a learning curve. Nobody makes a mistake its all a different way of looking at things.
Hope I have not missed anything important
Any comments from members on this forum are welcome.
To start of I would recommend a book by Sybex - Mastering Excel 2003 Programming with VBA. Its the book I have used and have not found a need to refer to any other. So I really cant comment on other books.
First and most important point to note is that you dont need to know everthing to work with excel and vba. Also if you know how to do something dont repeat yourself. It becomes very routine.
1) First learn the basics about navigating using excel within the Excel spreadsheet. Explore all options under the file menu to get familiar with using the excel application. Opening, Closing, Saving files. Cut, Copy and Paste. Find and Replace. Inserting, Deleting sheets, Hyperlinks, Charts, Formatting rows columns and Cells, Sort, Filter, Advanced Filter, Conditional Formatting etc.
2) Next update your basics with identifying between different cell formats. i.e. number, text, currency, general, font, colors, allignment, borders, etc. This will give you an idea as to how to present your data.
3) Next start with learning how to use formulas. From the file menu select. Insert Function and select All functions. Go through each formula and start reading about how to use formula in your spreadsheets. There is a link at the bottom which says "Help on this function". It will give you all the information that you need to learn. For most basic users vba is not necessary, they can solve most problems by just using formulas. Few important ones are AVERAGE, CONCATENATE, SUM, COUNT, DATE, FIND, IF, LEFT, LEN, MID, RIGHT, OR, AND, OFFSET, RAND, REPLACE, ROW, COLUMN, SEARCH, SUBSTITUTE, VLOOKUP, VALUE,
TRIM, TRANSPOSE etc.
4) Once you get to this stage. You are ready to start with VBA. VBA gives you the ability to all of the tasks mentioned above and more programatically. To get you started quickly you need to understand about how to declare variables and differentiate between different data types. You should be able to differentiate between properties, methods, collections and objects. You need to know where to run functions or subprocedures from, i.e. either in modules or userforms or as events. This is the base of vba. Once you get this part the rest is easy.
5) Start using Macro recorder to record vba code into Excel. Modify the recorded code according to your requirements.
6) To be quick in writing VBA programs you need to know where to look for information. Here are few important tips.
a) Object browser - most important for syntax and examples. You will find almost everything about excel vba here. If you need the syntax just right click each property or method and click help. It will give you the required info. For all the recorded macros you can look up the properties in Object browser.
b) Learn how to debug - how to use breakpoints, watch window, immediate window, locals window and properties window.
Once you get to this stage you are almost ready to write any program. The easiest way to improve your skills is to logon to some forums and try solving others problems. Read code from other experts on the forum to get an understanding how they approach each problem, you will pick up a lot of useful tips.
Always look at minimizing the amount of code you write. Try solving other problems and post your code on the website, the experts will always advice you on how to improve on your code or where you are going wrong or an easier way to solve the issue. Its all a learning curve. Nobody makes a mistake its all a different way of looking at things.
Hope I have not missed anything important
Any comments from members on this forum are welcome.
Last edited:
