Andrew's Excel Tips. Andrew's Excel Tips is moving Andrew's Excel Tips is moving! The new blog can be found here. Posted by andrewe at 10:07 │Other Excel Power Analyst Boot Camp Mike Alexander of DataPig Technologies is holding a seminar at Alexandria, VA (near Washington the city, not the state), from September 19, 2011 (Monday) to September 20, 2011 (Tuesday). Topics include AnalysisReportingSourcing External DataBacon Recipes (one can only hope)Click the pig to check it out! Oink! Posted by andrewe at 18:52 │Other VBA Code Indenter 2 A slightly "improved" version of my VBA Code Indenter is available. This version starts indenting after Sub and Function names. The download link is here. Posted by andrewe at 18:46 │VBA VBA Code Indenter There are 2 VBA code indenters that I know of, and unfortunately, I am not able to install either of them where I need them most to due to admin rights. My own version is rather basic.
VBA Code Indenter Pre-2007 Go to the Tools Menu at the top of Excel. Posted by andrewe at 11:09 │VBA. Free Excel Downloads. Excel Functions and VBA (Visual Basic for Applications) AdvancedFilter.zip This is a zipped Excel Workbook that has six working examples using Excels Advanced Filter. It has three that use no formulas and three that do use formulas. DFunctionsWithValidation.zip This is a zipped Excel Workbook that shows how you can use Data Validation and the Dfunctions to extract data from a Table. FormulaExamples.zip This is a zipped Excel Workbook that shows how you can use various formulas to extract information from a Table using LookUp formulas. FormsControls.zip Think you need to know VBA to make things happen automatically, think again.
DynamicSelections.zip Way to spice up your spreadsheet using a 3D effect on selected cell and the Office Assistant to guide you. SheetsToPrint.zip Use of a UserForm and ListBox to display a list of non empty sheets for printing. AutoUpdateValidationList.zip This is a zipped Excel Workbook that shows how you can use VBA housed in the Worksheet_Change Event to automatically add data to a cells Data Validation list. SumEveryNth.zip. Ron's Excel Tips. The Spreadsheet Page Excel Downloads: Excel 2007 Function List.
Category: General Files | [Item URL] Description: An Excel 2007 workbook that contains a list of all worksheet functions, in a table. Each function has a brief description and a hyperlink to its help topic at Microsoft's site. This file was accidentally omitted on the CD-ROM that accompanies my Excel 2007 Formulas book.Has Macros? NoDownload: worksheet functions.xlsx Excel Downloads This section contains free Excel workbooks and add-ins. The files are grouped into three categories: General Interest, For Developers, and Add-Ins. All Downloads Browse Downloads by Category Also of Interest How to download Most of the download links point directly to Excel files, but a few are compressed ZIP files.
Search for Downloads. Forms Controls and ActiveX Controls in Excel. There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls. The Forms controls aren’t very complicated, and they have been part of Excel for longer (I’m thinking Excel 4, and they were used in Excel 5/95′s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behavior to the ActiveX controls from the Controls Toolbox. I’ve recently had problems with a buggy slider from the Forms toolbar, but I suspect the parent workbook was corrupt. There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls.
The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms. Forms Controls ActiveX Controls VBA Controls Formatting Controls. VBA. Within VBA you can also write 'Event code' and UDFs, (User Defined Functions) that act like Excel's built-in functions (SUM, VLOOKUP, etc), these have to be manually written, they cannot be recorded. VBA is an object-orientated language where you manipulate particular 'objects' using their properties, methods and events. The objects you can manipulate are specific to the 'Application' in which you are writing them, Hence VBA. (This does not mean you cannot control other objects from an application however). An object is an entity within the program, for example Excel has the following objects (and many. many more!)
, all of which can be manipulated in code. So, if I wanted to refer to the third character in the cell A1 on Sheet1 in a file called Book1.xls in Excel and make it bold, you could 'drill down' the object model like so. Sub Change3rdChar() Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1").Characters(3, 1).Font.Bold = True End Sub Setting all cells just for fun!
Top. Quick reference card - Get in the loop with Excel macros - Microsoft Office.