How to do the impossible (almost)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value > 10 Then
MsgBox "Put your macro code here in place of the MsgBox line"
End If
End If
End Sub

Public Function RMAC(ByVal Macro_Name As String,  _

ByVal Arg1 As Variant)

RMAC = Application.Run(Macro_Name, Arg1)

End Function

Public Function RMAC _
(ByVal Macro_Name As String, _
ByVal Arg1 As Variant)
RMAC = Application.Run _
(Macro_Name, Arg1)
End Function

Sub MyMacro(arg As String)
ActiveCell.Interior.ColorIndex _
= 3
Beep
End Sub

=rmac("MyMacro","yada")

Labels: Customize, Functions, General, Macros, Reference, Tips, Tutorials

Security levels

High:

A computer user can open without a prompt a digitally signed project from a trusted publisher. Otherwise, the application blocks opening signed projects from untrusted publishers as well as unsigned projects.

Medium:

A computer user can open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.

Low:

A computer user can open an unsigned project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.

"If you've used Access 2003, you've probably seen several security warning messages - Access 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.

A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped."

Labels: General, Macros, Tips, Tutorials, VBA

VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

1. Choose a blank worksheet
   
2. In cells A1 and A2, enter the values 1 and 2.
   
3. In B1 and B2, enter Jan and Feb.
   
4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B. In cell D1 enter the formula =VLOOKUP(C1,A1:B12,2). Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name. To demonstrate the problem, right-click on the heading of column B and choose Insert. The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0. Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number. Press Ctrl-Z to undo the column insertion. The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references. You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns: =VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1). A more esthetically pleasing, or sophisticated, function might be: COLUMNS(array) This returns the number of columns in an array or reference. The modified lookup function looks like this: =VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)). Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

Labels: Customize, Functions, General, Tips, Tutorials

Display properties

Sub CommentThem()
Dim cell As Range
On Error Resume Next
Selection.ClearComments
On Error GoTo 0
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Formula <> "" Then
cell.AddComment
cell.Comment.Visible = False
On Error Resume Next 'fails on invalid formula
cell.Comment.Text Text:=" Address:  " & cell.Address(0, 0) & Chr(10) & _
" Value:  " & cell.Value & Chr(10) & _
" Format:  " & cell.NumberFormat & Chr(10) & _
" Formula: " & cell.Formula
On Error GoTo 0
End If
Next cell

End Sub

Labels: Customize, Formulas, General, Macros, Reference, Tips, VBA

Formatting

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
Next
End Sub

Labels: Customize, Formats, Tips, VBA

Only what you want

"Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."

Labels: Customize, General, Reference, Tips

Macro control

Change the default printer

Change the default printer with a macro.

Insert headers and footers

This example macro inserts a header/footer in every worksheet in the active workbook. It also inserts the complete path to the workbook.

Print all workbooks in a folder

With these macros you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer.

Print multiple selections on one sheet

If selected multiple cell ranges is printed out on different sheets, you can use this macro example to print all the selected areas on one sheet.

Select a printer tray before printing

In Excel you don't have the opportunity to set the properties FirstPageTray or OtherPagesTray like you can in Word. It's possible to create a simple solution by using SendKeys.

Labels: Customize, Functions, General, Reference, Tips, VBA

List maker

1. Open Word.
   
2. Use Alt+F11 to open the Visual Basic editor.
   
3. Choose Insert>Module from the Menu.
   
4. Copy and Paste this code in the module.
   
5. Return to Word and go to: Tools>Macro>Macros.
6. Select and run "InstalledFonts".

Sub InstalledFonts()

Dim F As Integer
Dim InstalledFonts As Table

 'Open a fresh document

Set FreshDoc = Documents.Add

'Create a table and define the header

Set InstalledFonts = FreshDoc.Tables.Add(Selection.Range, FontNames.Count + 1, 2)
With InstalledFonts
.Borders.Enable = False
.Cell(1, 1).Range.Font.Name = "Arial"
.Cell(1, 1).Range.Font.Bold = 1
.Cell(1, 1).Range.InsertAfter "Font Name"
.Cell(1, 2).Range.Font.Bold = 1
.Cell(1, 2).Range.InsertAfter "Example"
End With

'Loop through all the fonts and add them to the table

For F = 1 To FontNames.Count
With InstalledFonts
.Cell(F + 1, 1).Range.Font.Name = "Arial"
.Cell(F + 1, 1).Range.Font.Size = 10
.Cell(F + 1, 1).Range.InsertAfter FontNames(F)
.Cell(F + 1, 2).Range.Font.Name = FontNames(F)
.Cell(F + 1, 2).Range.Font.Size = 10
.Cell(F + 1, 2).Range.InsertAfter "ABCDEFG abcdefg 1234567890 &$@"
End With
Next F
'Sort the names of the fonts

InstalledFonts.Sort SortOrder:=wdSortOrderAscending

End Sub

Labels: Fonts, Tips

Project Charts

Labels: Charts, Reference, Tips, Tutorials

Double click the handle

F. David McRitchie:
Fill Handle

Microsoft Office Online:
Fill data in worksheet cells

If you don't see the Fill handle:
Click Options on the Tools menu and click the Edit tab.

(Use the Office button in the upper left corner in 2007)
Select or clear the Allow cell drag and drop check box.


See all Topics excel

Labels: Formulas, General, Reference, Shortcuts, Tips, Tutorials

Group sheets

Labels: General, Shortcuts, Tips

Stick it in Word

Labels: Customize, General, Graphics, Reference, Tips

If it's Tuesday, it must be mauve

Labels: Formats, Formulas, Reference, Tips

Excel the way you've wanted it

Chart Data Labeler: IMPROVED

Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel. Enhancement: Now ignores data that is hidden as a result of autofiltering.

Resize Charts: NEW

Makes it easy to make a chart an exact size, or make all charts the same size.

Convert Chart To Picture: NEW

Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.

Chart Report: NEW

Produces a detailed report that documents a chart, or produces a report that documents all charts.

Object Align, Size & Space: NEW

Back by popular demand. This utility was removed from PUP v5, but now it's back. It provides an easy way to precisely align, size, and space a group of objects or embedded charts.

Object Hide / Unhide: NEW

An interactive way to hide or unhide charts and other objects on a worksheet.

Labels: Addins, General

Formatting slip

Labels: General, Reference, Shortcuts, Tips, Tutorials

Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic


See all Topics excel

Labels: Formulas, Functions, General, Reference, Tips

Who's counting?

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

Labels: Functions, Reference, Tips, VBA

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Customize, General, Reference, Tips

Free money (tracker)

Your income

Just your take-home pay, which is what you control and spend.

Cash reserve

A reserve equal to 3-6 months of take-home pay.

Savings

A portion of your income set aside to fund your retirement.

Fixed expenses

Expenses that you must pay over the course of a year.

Replacements

You also must pay to replace things that eventually wear out.

Weekly allowance

Partly discretionary, partly not; paid by cash from your pocket.

Periodic purchases

Discretionary purchases paid by check periodically.

Future purchases

Big-ticket discretionary purchases that you must save for.

Summary

A summary of your income and where it goes—and where you are.

QuickForm

You can switch to this format once you're familiar with the method.

Tips

Ideas to help pare your expenses.

Labels: General, Templates, Tips, Tutorials

Shake hands with a worksheet

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Labels: General, Reference, Tips

Smile!

1. Edit>Copy the Cell or Range
   
2. Choose the target cell
   
3. Hold down the Shift key and choose Edit>Paste Picture Link

1. Select the Cell/Range
   
2. Click the Camera tool
   
3. Move the cursor to the target position and click to insert the linked picture

1. Clear cell gridlines if you do not want them displayed in your picture.
   (Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")
   
   
2. On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
   
3. Hold down SHIFT and click Copy Picture on the Edit menu
   (For the best picture quality, make sure As shown on screen and Picture are selected)
   
4. Click OK
   
5. Select the worksheet or other document where you want to paste the picture
   
6. Edit>Paste.

Labels: Customize, General, Tips, Tutorials

More dimensions than Star trek

Labels: General, Reference, Tips, Tutorials

Go back

"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."

Labels: Reference

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

MsgBox "Hello World!", , "My Message Box"

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

Labels: VBA

Zero is nothing

• Display or hide all zero values on a worksheet
  
  
• Use a number format to hide zero values in selected cells
  
  
• Use a conditional format to hide zero values returned by a formula
  
  
• Use a formula to display zeros as a blanks or dashes
  
  
• Hide zero values in a PivotTable report

Labels: Formats, Functions, General, Tips

Different can be good

• Improved ribbon
  
  
• Access workbooks in new ways
  
  
• Make fast, effective comparisons from lists of data
  
  
• Sparklines
  
  
• Create workbooks with more visual impact
  
  
• Collaborate on workbooks in new ways

Labels: General, Reference

Split the sheets (?)

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

Labels: General, Reference, Templates, Tips

Formatting bloat

For more information, check out:

How to reset the last cell in Excel 2007

Microsoft Knowledge Base article 211478

Also see:

Spreadsheet Diet

Beyond Technology:
Identifying the Real Last Cell

Microsoft:
Formatting Cleaner Add-in


See all Topics excel

Labels: Customize, Formats, Gadgets, General, Reference, Tips, Troubleshooting, Tutorials, VBA

Game with Excel

• Excel fun
  
  
• 3D Viewer
  
  
• Battleship
  
  
• Blackjack
  
  
• Breakout
  
  
• Golf Stats
  
  
• Mastermind
  
  
• Maze
  
  
• Minesweeper
  
  
• Reversi
  
  
• Rubix
  
  
• Slots
  
  
• Sudoku Solver
  
  
• Tetris
  
  
• Video Poker

Labels: General, Link, Reference, Tips

The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates

Also:

John Walenbach:
Extended Date Functions Add-In

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

Labels: Formulas, Functions, Reference, Tips, Tutorials

Context menu redo

Sub ResetShortcutMenu()
Application.CommandBars("Cell").Reset
End Sub

Labels: Customize, Macros, Reference, Tips