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. SR 1 before one can install SP2. Macro SecurityeditMacro security settings are located in the Trust Center. FVS/QXRE/HQAWYGWR/FVSQXREHQAWYGWR.MEDIUM.jpg' alt='Excel File Locked For Editing Macros' title='Excel File Locked For Editing Macros' />Make an Office file readonly by using password protection, marking as final, or applying a digital signature. 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 File Locked For Editing Macros' title='Excel File Locked For Editing Macros' />Excel 2. 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. Frequently asked questions for Excel application and file problems limits, passwords, error messages and more. About Chandoo. org Excel and Charting Tips site blog. About the author Purna Duggirala Chandoo. When you download a file or edit it in Excel, and then reopen in the online version, all hyperlinks to Sharepoint locationsfiles no longer work. Microsoft Excel 2010 was subject to perhaps the least radical makeover of any app in Office 2010, but thats not to say that the worlds most popular spreadsheet. Hi everyone, first time authoring here and looking to pass on one of the neat, but less intuitive aspects of data management in Excel. Often I find myself with data. 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. Install Geany On Centos 6 Download. 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. Autoexpand Excel Tables on Protected Sheets An important feature of tables, the autoexpanding functionality, is lost when the worksheet is protected. This free new tool allows you to create and manage invoices for a small business. The custom Excel ribbon and macros streamline the invoicing process. IT Project ROI Calculator and Business Case Toolkit This popular free Web or Excelbased tool helps organizations to assess, quantify, and communicate the costs. 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. 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. IT Project ROI and Business Case Toolkit. IT Project ROI and Business Case Toolkit. Enables rapid development of comprehensive business cases for most IT projects. The IT Project ROI and Business Case Toolkit enables rapid development of comprehensive business cases investment justifications for IT projects. It helps organizations to assess, quantify, and communicate the costs, benefits, business value, and ROI of most types of enterprise scale technology based initiatives. It also helps to assess the organizations current as is and expected to be IT spending TCO levels and the solutions impact on a variety of key performance indicators KPIs. It produces high quality, customized, and editable business case reports that can be presented to decision makers and customers. The tool estimates the costs required to implement the new capabilitiessolution, including hardware, software, IT labor, services, and user labor. It simulates benefits user productivity, IT TCO savings, business cost savings, revenue growth, and KPI improvements enabled by the solutions selected. It supports both rapid 3. The tool uses robust industry best practice methods and extensive research to assist the organization in rapidly assessing the costs and benefits of investing in the IT initiative. However, this tool cannot accuratelycredibly estimate costsbenefits without review, validation, and adjustment of key data inputs. These inputs should be customized to each particular organization and scenario. The toolkit is powered by hosted on the Analysis. Place. com platform and was developed by Hall Consulting Research LLC http hallcr. The tool supports key Analysis. Place capabilities such as currency and language switching, collaborationsharing, scenario analysis, and change tracking. This toolkit can be used as a standard corporate business case template. It can also be used by consultants and technology vendors to articulate the business value of their products and solutions. Basic registration ID, e mail, password at Analysis. Place. com is required to use the tool. Registration credentials are required to log into the tool after being downloaded. Supported Analysis. Place Platform Features. The table below summarizes key capabilities of the Analysis. Place platform and support for the capabilities in this tool. Analysis. Place Platform Feature. Description. Web Based Version. Excel Based Version. Compatibility. Compatibility with most systems. Any modern browser, including tablets. Microsoft Excel 2. Not compatible with Apple Mac devices. Data sync. Data entered into the tool will be uploaded to a secure remote server enabling features such as collaboration with other users. Yes. Yes data syncing automatically occurs during saves and prior to report creation. Strong Privacy protections. Information and data entered into the tool will not be shared without the users explicit permission. Tools are designed to minimize entrycollection of personal information. Yes. Yes. Security. Synchronizeduploaded data entered by the user is stored in a secure database. Data transmission web services are encrypted. Yes. Yes. Sharing and collaboration. Users can invite others to collaborate on a tool assessment. For example, a sales professional can invite a prospect to view and modify an assessment. This feature can also be used to provide user support. Yes. Yes users must be registered. Reports. Tools can produce Microsoft Word and Power. Point reports. The reports are customized based on tool results, can be edited, and can be saved as a pdf to restrict editing by the recipient. Power. Point report. Word reportor view reports online. Power. Point report. Word report. Multi currency. Currency can be changed in the contol panel. Includes integrated currency converter exchange rates can be updated each time the workbook is opened. No. Yes 3. 0 common currencies. Multi lingual Language switchingTool language can easily be switched from the control panel. No. Partial support Languages available English, Spanish, French, German, Japanese. Non English language content is based on machine translation and is for demonstration purposes only. Online or offline flexibility. Users must be online connected to the internet during first login to verify credentials, but can then work offline. No online only. Yes. Control Panel. User controls many tool features via the Analysis. Place. com Control Panel  load assessments, create reports, change the languagecurrency, manage scenarios, restore default values, share with others, etc. No, basic menu. Yes. Assessment flexibility. Assessment data data users enter into the tools can be synchronized with the server. The user can conduct multiple assessments with each tool may depend on licensingAssessments can be made from other assessments. For example, a sales rep could start with the Banking Industry template when preparing a business case for a prospect in the banking industry. Yes. Yes Each Excel xlsm file is typically a separate assessment. Users can load and save assessments from and to their hard drives andor from the Analysis. Place. com servers. Scenario analysis. Each assessment can contain up to 1. Switching between scenarios is easy. No. No, not in this version. Change Tracking. Indicates which cells have been modified displays username and dateTool tracks user changes and allows the user to restore prior entries. User can reset default values and formulas. No. Yes. Savingloadingsharing flexibility. Assessments can be saved to and opened from your harddrive or from Analysis. Place. com storage. Assessments can be shared with colleagues by emailing the Excel file or via the collaboration features. Yes. Yes. Single Microsoft Excel XLSM file. No software to install simply download an Excel file macros must be enabledExtends Excels featurescapabilities. Microsoft Word and Power. Point reports are embedded in the Excel file. Web based. Yes. Easy to upgrade to new version releases. User assessments will almost always be compatible with new tool versions and will automatically be upgraded when the user downloads the upgraded tool. Tool indicates if a new version is available. Yes. Yes. Summary of Key Tool Features. Supports most types of enterprise scale technology initiatives. Includes sample cost and benefit data for 1. InfrastructurePlatform Technology Initiatives. Business Technology Initiatives. PC Hardware Operating System Upgrades. Server Hardware Software Upgrades. Infrastructure Management Networking. Storage. Security. Application Development Architecture. Compliance, Governance, Risk. Outsourcing. Wireless Mobile. Office Productivity Software. Messaging Collaboration. Content Management. E commerce Internet. Business Process Mgmt Integration. Business Intelligence Data Mgmt. Business Applications Vertical, Line of BusinessCRMERP Supply Chain. Comprehensive assessment of initiative costs and benefits. Costs  hardware software IT labor, training, and services and user labor and training. All direct and indirect, one time and on going costs needed to implement and support the initiative. Benefits  IT labor TCO savings, other direct cost savings, user productivity benefits, revenue growth, and non financial key performance indicators. Calculates annual on going IT spending TCO total cost of ownership, including hardware, software, internal IT personnel, external service providers, and telecommunications.