|
|||
|
|
|
|
|
|
Tutorial on Excel Macros (VBA)The tutorial includes a set of 33 lessons that you can surf and search easily on your own computer and that you can print. It also includes 25 Excel spreadsheets with useful examples and templates. You can use the spreadsheets or copy/paste everything from them into your own spreadsheets. Nothing is hidden, nothing is protected. You will find many macros that you need already built for you. You will learn at your own pace at home, at work or even while travelling. Make copies of the download for all your personal computers. Table of contents Introduction to VBA for Excel Excel Macros Vocabulary Building macros in ExcelVisual Basic Editor (VBE) in Excel The most user friendly programming environment. You will create your procedures (macros) and userforms in the VBE. You will modify and test these components and even make changes while you are testing your procedures step by step. The Project Window in the Visual Basic Editor of Excel Where you see all the open workbooks, where you add modules and userforms The Properties Window in the Visual Basic Editor of Excel The properties of the workbooks, the names of the modules and the properties of all the components of the userforms. The Code Window in the Visual Basic Editor of Excel Where you write and test all your code You are becoming so good that you don't need the macro recorder sometimes. Here is how you create a new VBA procedure from scratch. Split your screen, use the F8* key and see what you procedure does in Excel step by step. Back up, correct and re-test. You can use the recorder to develop macros rapidly. If you want to do more the Macro Recorder is the best teacher and will be a great assistant even when you become an expert at programming in VBA. It WRITES the code for you. In this chapter you will learn about and try the Macro Recorder. You will run the macro that you have recorded. The macro recorder is a great helper and teacher but it sometimes makes it complicated. Here is a recorded macro simplified. Protect you code, your sheets and your computer. For a procedure to start it must be triggered, you click on a button, you close a workbook, the value of a cell changes. Find the right event. Words and Sentences to Build Macros in ExcelA few tips to make things easier when you start coding. VBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer. The Application is EXCEL ITSELF. Master 15 new VBA words: Application, Calculation, Cursor, CutCopyMode, Dialogs, DisplayAlerts, EnableEvents, GoTo, Quit, ScreenUpdating, Timer, ChDir, ChDrive Working with one workbook, with THISWORKBOOK, with many workbooks and even with all the workbooks in a directory. Moving from one to the other, copy/paste from one to the other, adding and deleting worksheets and even doing something on all of them. Moving aroud the worksheet Improve your VBA vocabulary with 30 more words to work within the worksheet: Range or Cells? Working with Message and Input Boxes Interacting with the user with minimal effort. Working with Excel Database and Filters Most of your data in the form of a database. Learn how to sort and filter your data. They are the KILL, the IF_THEN_ELSE_END, the SELECT-CASE, the DO_LOOP, the FOR_NEXT.... Creating new Excel functions, using the Excel functions in VBA and a few VBA functions. When you don't want to hard code things and when the value of something changes put it in a variable. Discover the VARIANT type variable that allows you to execute 1,000,000 calculations in less than 5 seconds. Working with external data and SQL When you connect to outside sources of data (large databases, text files, other Excel workbooks, Access, etc.) the computer is using SQL (Structured Query Language) a specialized language to work with data. You can use Excel's functionalities to connect and extract data but you can also use directly the SQL language. It is much faster. Working with Windows and other Microsoft Programs FROM Excel With VBA for Excel you can develop VBA procedures (macros) to work within in Excel while calling other Microsoft programs like Access, Notepad, Word, Project and even Windows UserForms and Controls (Command Buttons, Combo Boxes...) in ExcelForms (Userforms) in VBA for Excel To require information from the user or tell him something you have used message boxes and input boxes. When these can't do the job anymore you need to develop useforms. Userforms Properties and VBA Code Then you set the properties including the important ones "On Activate" and "On Close" Excel VBA Controls' Properties and VBA Code They are the command buttons, the list boxes, the combo boxes, the text boxes...and they all have properties. Here they are. What must happen when you click on a command button, when you change the value in a combo box? Here is the code to tell Excel about it. Properties and VBA code for Command Buttons The "CLICK " control where most of the code resides in a userform Properties and VBA code for Labels The user just sees it and cannot submit any information with it. YOU are talking to the user Properties and VBA code for Text Boxes The user is now talking to you. I have rarely seen a userform without text boxes. The format is the challenge. Properties and VBA code for Combo Boxes The BIG control. It is a drop-Down list. Properties and VBA code for List Boxes When multiple choice is a necessity Properties and VBA code for Check Boxes, Option Buttons and Frames The "True/False" controls to be used as a group with a frame Properties and VBA code for Spin Buttons They are sexier than text boxes and sometimes make a difference in the clients' opinion of your talent. |
|
|
|
|
