More Power Excel

1
More Power Excel
2
Data Validation

• Maximize data integrity
• Simple user interface development
• Build validation rules at cell level
• Rules can be based on values in SAME sheet
• Stop, Warning, Information
• EXAMPLE OBLog_prototype.xls

3
Useful Page Setup Options
Print range
Useful for large print ranges
More useful options
Print comments
4
Conditional Formatting
More advanced technique using logical formulas
instead of simple logical conditions. Must use
relative cell references in this example.
5
Conditional Formatting Enhancements in Excel 2007

• Many types of rules
• Based on values in cells
• Based on a logical formula
• Data bars, icon sets
• ConditionalFormatting.xlsx

6
Database functions(Dfunctions)
7
Dfunction syntax
8
Criteria ranges
Also used for Advanced Filtering see Example
9
Dynamic range definitions

• Sometimes you have a range that grows over time
  as you add data to it
• You might have a graph, pivot table or some other
  calculation that operates on this range
• Ideally youd like the range definition to
  automatically update whenever data is added
• This can be done using a combination of the
  OFFSET() and COUNTA() worksheet functions
• These are worksheet functions, they do NOT
  involve VBA
• Technique is described on Walkenbachs
  Spreadsheet page at http//j-walk.com/ss/excel/use
  rtips/tip053.htm
• DynamicRange-Example.xls

10
Array Functions

• Arrays are just collections of items operated on
  collectively or individually
• 1-d array in Excel can be a row or column
• 2-d array in Excel can be range with multiple
  rows and/or columns
• Arrays do NOT need to exist only in cells
• They can exist in memory as well
• Array formulas can operate on arrays and return
  either a single cell or multiple cells (another
  array)
• Array formulas must be entered with
  CTRL-SHIFT-ENTER
• Cannot edit or delete part of an array returned
  from an array formula its all or nothing
• See the ArrayFunctions.xls workbook

11
A simple array formula example
12
Worksheet Protection

• Prevent users from wrecking your spreadsheet app
• Unlock cells (Format Cells Protection) that
  user can change
• Protect sheet (Tool Protection)
• Can password protect (not super secure)
• Solver cant operate on protected sheets
• What can you do to protect sheets and use Solver?
• Open SchedulingDSS_Protection.xls

13
Custom Toolbars and Menus
Chap 16 of VBA for Modelers and Chap 22-23 of
Walkenbachs book are very good for learning how
to manipulate toolbars and menus with VBA.

• CommandBars collection
• Toolbars
• Menus
• You can
• Customize existing toolbars
• Create new toolbars
• Create new menu bars and menus
• Customize existing menus
• Hide/disable items on menu bars
• Toolbar info stored in XLB file (lets search)
• C\Documents and Settings\user\Application
  Data\Microsoft\Excel\Excel11.xlb
• Toolbars can be attached to XLS, XLA files
• Often better to create/remove custom toolbars on
  fly with VBA

14
Manually Creating Menus
15
Resetting Changes to Menus
16
Custom Menus on the fly
Lets look at Menumakr.xls
17
Creating Your Own Add-Ins(Chap 21 of
Walkenbachs Power Programming in Excel 2003)

• Add-ins are spreadsheet applications that usually
  add functionality to Excel
• Very useful for distributing Excel based
  applications or creating your own library of
  useful functions and subs
• XLA files (add-ins) are like XLS except
• Workbook window is hidden
• can be loaded/unloaded using Tools-Addins
• subs/functions in an add-in do NOT show up in
  Macros
• functions in an add-in DO show up in Paste
  Function
• IsAddIn property of ThisWorkbook object is set to
  True

18
Why create Add-Ins?

• Restrict access to your code
• To avoid confusion of requiring another xls file
  to be open to use the functionality
• Simplify access to custom (1) worksheet functions
  or (2) form based tools
• just do Tools-Addins... and you can use them
• Add-in manager easy to use
• Gain better control over loading of add-in
• the Add-In Manager can be controlled via VBA
• Example NewAddIn.xls contains a few things
• a few miscellaneous user defined functions
• the form based version of TheShader

19
Creating the Add-In

• Develop your application or set of functions in a
  regular xls file
• make sure everything works
• give user a way to access the functionality
  through custom menus, menu items, or toolbars
• Example Use the Walkenbach technique shown in
  menumakr.xls
• Test with another workbook active
• add-ins are never the active workbook
• Make sure you have at least one worksheet in the
  workbook (it can be blank)
• you can use this worksheet as a scratch pad to
  hold various calculations if you need to.
• this worksheet will be hidden and thus
  inaccessible by the user
• Within the VBE, Tools xxx Properties and set
  the name and description for the project

20
Creating the Add-In (continued)

• Within the VBE, Tools xxx Properties and select
  the Protection tab
• check the Lock project for viewing check box
• enter a password (twice) (its isken in
  Utility.xla)
• Close the VBE
• File Properties and fill out the Summary tab
  with a title, author and comments
• File Save As... and save it as an XLA file
• the original file will stay open, you can close
  it
• Pay attention to where Microsoft wants to save
  this XLA file
• Now you can use your add-in by doing a Tools
  Addins... and browse to your add-in
• lets do it
• Now your add-in is ready to use

21
Custom Templates

• Useful for distributing spreadsheet solutions
• Save file as .xlt
• Put in C\os\Profiles\user_name\Application
  Data\Microsoft\Templates
• Will show up in File-New dialog box
• User creates new spreadsheet based on template
• Example File New Spreadsheet Solutions
• Then do a File Save As... an xls file

22
Custom Menu, XLA, GetOpenFilename()