Excel VBA Wikibooks, open books for an open world. Microsoft Excel is a deep program rich in features and functionality. One of the most powerful features of Excel is the ability to write programs in Visual Basic for Applications that run behind your worksheets to turn Excel into a calculation oriented development platform for creating special purpose spreadsheets which can function as applications in their own right. Service PackeditA service pack in short SP is a collection of updates, fixes or enhancements to a software program delivered in the form of a single installable package. A service pack can be incremental, which means it only contains the updates that were not present in the previous service packs or, it can be cumulative, which means it includes the contents of all its predecessors. In the case of Microsofts products, an incremental update was called a service release. For example, Office 2. How-to-learn-Excel-VBA-Part2.png' alt='Vba Code Examples For Excel 2010 Pdf' title='Vba Code Examples For Excel 2010 Pdf' />Excel macros and usage notes to help beginners and fairly advanced users work with Excel spreadsheets. David McRitchie over 200 Excel web pages. QUESTION Using VBA, how do I export a report to PDF in Microsoft Access ANSWER Access 2010 option of opening report and outputting to PDF. SR 1 before one can install SP2. Macro SecurityeditMacro security settings are located in the Trust Center. However, if you work in an organization, your system administrator might have changed the default settings to prevent anyone from changing any settings and execute macros. Macro RecordingeditA great way of learning about Excel VBA is using its macro recording function. With the function, you tell Excel to start recording, then perform various steps as if you were working without a macro recorder, and finally, tell Excel to stop recording. VBA code corresponding to what you did using Excel GUI has been recorded by Excel. While the code often cannot be meaningfully used without a modification, by starting from it and modifying it you can save a lot of time that would otherwise be spent reading the VBA documentation. Menu paths Excel 2. Tools Macro Record New Macro. Excel 2. 00. 7 View tab Macros group down pointing triangle below Macros button Record Macro. Excel 2. 00. 7 Developer tab Code group Record Macro. Links Enabling Developer tabeditDeveloper tab allows you to insert various user interface controls such as buttons. In order to use it, you first have to enable it. Menu paths to enable the tab Excel 2. Office button Excel Options button at bottom Popular Show Developer tab in the Ribbon check boxExcel 2. File tab Options button Customize Ribbon button Developer check boxLinks Making an XLAeditAn XLA is one way to make a VBA code library. Its basically just a normal spreadsheet. Heres how to create a new one New Workbook. Save As. Name it whatever. Hit Alt F1. 1In the Project tree, select VBAProjectwhatever. This. Workbook. Hit F4 to get Properties View. Find Property Is. Addin and set to True. Hit Save. Close Excel. Rename whatever. xls to whatever. Alternatively, you can use Save As Excel Add. In. Accessing the RegistryeditWorks on Microsoft Excel 2. SP 2. This recipe is for readingwriting keys local to your applicationthis is to provide persistent settings for your VBA app. It does not cover arbitrary access to the Registry i. The VBA subfunctions are Save. Setting and Get. Setting. You can type the following into the Immediate window to get a feel for how these work Save. SettingMy. Application. Name,My. Config. Section,My. Key. Name,Hello WorldMsg. Box. Get. SettingMy. Application. Name,My. Config. Section,My. Key. NameIf you want to iterate over all keys in a given section you can do so as follows Sub. Show. All. KeysDimmy. Settings. As. Variantmy. SettingsGet. All. SettingsMy. Application. Name,My. Config. SectionIf. Not. Is. EmptyMy. SettingsThen. Dimcounter. As. Integer. ForcounterLBoundmy. SettingsTo. UBoundmy. SettingsDimkeyname. As. String keynamemy. Settingscounter,0Dimkeyval. As. String keyvalmy. Settingscounter,1Msg. Boxkeyname keyval. Next. End. If. End. Cat Girl Alliance. Sub. You can also delete a registry key as follows. Delete. Setting My. Application. Name, My. Config. Section, My. Key. Name. FYI ExcelVBA sticks this in the following registry location. My. ComputerHKEYCURRENTUSERSoftwareVB and VBA Program SettingsMy. Application. NameMy. Config. Section. My. Application and My. Config. Section are whatever you specified in your Save. Settings call. They end up in HKEYCURRENTUSERSoftwareVB and VBA Program SettingsMy. Application. NameMy. Config. Section. Works on Microsoft Excel 2. SP 2. Make the following call from VBA Application. Display. AlertsFalse. Making Cells Read OnlyeditWorks on Microsoft Excel 2. SP 2. Sub. Protect. MeRangeA1 IV6. LockedFalse. RangeA1. LockedTrue. Active. Sheet. Protect. Password TestEnd. Sub. Finding Non Empty Portion of WorksheeteditA Worksheet has a maximum size of 6. However if you want to iterate over all cells you probably dont want to visit all of the empty ones. For this purpose the Worksheet provides the Used. Range property. For example. Active. Sheet. Used. Range. Rows. Count. Empty rows which are inbetween the first and last used row are counted as well. Example If a given Worksheet has entries in cells A7 and B1. A7 B1. 6, which counts for a total of 1. Using EventseditWorks on Microsoft Excel 2. SP 2. Consider the following class definitionAssume its a class called CMy. Class Option. Explicit. Dim. With. Eventsmy. Sheet. As. Worksheet. Public. Sub. Inita. WSas. WorksheetSet. My. Sheeta. WSEnd. Sub. Private. Submy. SheetSelection. ChangeBy. Val. Target. As. RangeDim. My. Range. As. Range. For. Each. My. Rangein. Target. Debug. Print. CStrMy. RangeNext. End. Sub. The main ideas here are By declaring my. Sheet With. Events, youre saying that CMy. Class is listening to my. Sheets events. By declaring the member sub my. SheetSelection. Change youre indicating how an instance of CMy. Class should react if my. Sheet experiences a selection change i. The general pattern for events is sub member. Var. NameEvent. Nameparams. You can disconnect the eventing between the given worksheet and CMy. Class by setting my. Sheet nothing You can create classes that throw events of your design using. You would declare at the top of the class Public Event Some. Nice. Event. NameYour. Favorite. Param. 1 as Whatever. Type, etc. ,You could then raise that event i. Raise. Event Some. Nice. EventSome nice event happened. VBA in Excel doesnt like the letters r or c used as variables. Those letters mean row and column elsewhere. A little more detail is given here 1Caveat Uncaught ExceptionseditCaveat Uncaught exceptions in event handlers cause VBE to mysteriously reset. If you are causing an uncaught exception in an event handler, you probably wont get an error popup. Instead, VBE will just reset. For this reason you should make sure youre catching exceptions in all of your event handlers. Caveat Online Help TyposeditSome versions of Excel can have typos in the F1 help. Heres an example of a Click handler with the correct parameters Private. SubclicksrcClickBy. Val. Ctrl. As. Office. Command. Bar. Button,Cancel. Default. As. BooleanMsg. BoxI got a clickEnd. Sub. Iterating Over Multi. Selected CellseditWorks on Microsoft Excel 2. SP 2. The following code snippet writes YAY in each cell selected by the user For. Each. Cellin. Selection. Cell. ValueYAYNext. Exporting VBA CodeeditWorks on Microsoft Excel 2. SP 2. The following code provides a very primitive routine to write serializes the VBA code in your modules to files Option. Explicit. Private.