Enter your email address:

Delivered by FeedBurner

Home Page








Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker

  Saturday, February 20, 2010 – Permalink –

Selection Address

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Display number of Rows and Columns

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:59 AM

Comments: Post a Comment

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically

Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs

Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

  Wednesday, August 05, 2009 – Permalink –

Large Text Files

Split between worksheets

While this problem is alleviated in Excel 2007 with its 1,048,576 rows by 16,348 columns, The old XL versions are still here.

Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet.

Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets:

Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
ActiveCell.Value = ResultStr
End If

'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
'Close The Open Text File
'Remove Message From Status Bar
Application.StatusBar = False

End Sub

Importing Text Files Larger Than 16,384/65,536 Rows

Notice the code about 17 lines from the bottom of the macro.

'For xl97 and later change 16384 to 65536.

Also, after import, the data must be parsed. Use Data>Text to columns.

If you have not worked with macros before, Dave McRitchie has a tutorial:

Getting Started with Macros and User Defined Functions

See all Topics

Labels: , ,

<Doug Klippert@ 3:13 AM

Comments: Post a Comment

  Friday, May 15, 2009 – Permalink –

Tabs with the Number of the Week

Count to 52

Excel no longer has a theoretical limit on the number of worksheets in a workbook. One common use of this ability is to add a worksheet for each week in the year.

Here's a macro that does the trick:
Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), _
Count:=(52 - Worksheets.Count)
iWeek = 1
For Each sht In Worksheets
sht.Name = "Week " & Format(iWeek, "00")
iWeek = iWeek + 1
Next sht
Application.ScreenUpdating = True
End Sub
Naming tabs for weeks

See all Topics

Labels: , ,

<Doug Klippert@ 3:58 AM

Comments: Post a Comment

  Tuesday, September 23, 2008 – Permalink –

List All Files

All files in a folder

Here is a macro that will produce a list of all the files in a selected folder.

  • The folder name for the listed files
  • The file names of the files found
  • The file sizes of the files found
  • The dates and times of the files found
  • The total number of files listed

Macro to List All Files in a Folder

Here are some other suggestions:

List Word and Excel Files

See all Topics


<Doug Klippert@ 3:20 AM

Comments: Post a Comment

  Thursday, September 04, 2008 – Permalink –

Running Total in Comment

Circular solution

You can't have a worksheet formula that looks like this:


But you can do something similar if you use VBA and store the results in another location.

"In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this by storing a running total in a cell comment."

Microsoft Support:
Create a running total in a cell comment

The macro runs each time the value of a cell changes.
It adds the current value of the cell to the value of the cell comment. Then it stores the new total in the cell comment.

I'm sure someone can come up with other uses for this macro.

Also see:

Repetitive Calculation Features and Add-Ins

Daily Dose of Excel
Dick Kusleika
Circular References - The Good Kind

See all Topics

Labels: , , ,

<Doug Klippert@ 12:06 PM

Comments: Post a Comment

  Wednesday, August 13, 2008 – Permalink –

Value of Cell in Header

or Footer

The header and footer cannot contain a link to a cell. You can create and run a macro that will put the value of a cell into a footer or header. You could run this macro each time the contents of the specified cell changes.

Sub AssignCenterHeaderToValueInA1OnActiveSheet()
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Or use it as an Event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub
Information from a cell in a worksheet

Erlandsen Data Consulting:
Insert headers and footers

Chip Pearson:
Headers and Footers

Jan's Computer literacy 101:
Excel Basics: Setup Header/Footer

University of Wisconsin at Eau Claire:
Modifying Header and Footer Information

See all Topics

Labels: , , ,

<Doug Klippert@ 4:27 AM

Comments: Post a Comment

  Saturday, July 19, 2008 – Permalink –

Run a Macro from Cell

How to do the impossible (almost)

There are times when it might be nice to run a macro from a cell function.
Something like : if a cell has a certain value, a macro will run:


You can not initiate a macro from a worksheet cell function. However, you can use the worksheet's Change event to do something like this:

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

When A1 is changed to a value greater than 10, the macro code will run.

To get to the Worksheet Event code, right-click the sheet tab and choose View Code.

Worksheet code


Also see:
Change Events

Microsoft KnowledgeBase:
How to Run a Macro When Certain Cells Change

After posting this, Ross Mclean of came up with a great work around using a User Defined Function.

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

You can see the simple coding here:

Running a macro from a cell.

Thanks Ross!

Keep in mind that some commands will be ignored. A macro run from the worksheet like this will not change the Excel environment.

For example (watch line wrap), this VBA code:

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
End Sub

when invoked by this worksheet formula:


runs the sub MyMacro with some modification. The Beep is executed, the cell color change is not.

- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions

See all Topics

Labels: ,

<Doug Klippert@ 2:09 AM

Comments: Post a Comment

  Friday, July 18, 2008 – Permalink –

Signing Macros

Security levels

There are three levels of Macro security:

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.
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.
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.
Securing Access Databases
"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."

Signing Access 2003 Projects

Other links:

How to make sure that your Office document has a valid digital signature in 2007 Office products and in Office 2003

HAL-PC MS Office & Excel SIG in Houston, Texas:
Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security

See all Topics

Labels: ,

<Doug Klippert@ 5:00 AM

Comments: Post a Comment

  Tuesday, June 17, 2008 – Permalink –


Macro control

Here are some useful macros concerning Excel and printing.
They were written by Ole P. Erlandsen of:


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.

See all Topics

Labels: ,

<Doug Klippert@ 5:25 AM

Comments: Post a Comment

  Wednesday, September 19, 2007 – Permalink –

Clippy's Revenge pre-2007

Roll your own OA

The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.

If you want to play with it, see John Walkenbach's Create A Fake Clippy

Here is a creature you could use to replace Clippy:
Nerd Bird

There are other articles in the MS library about Agents and Assistants:
Animating Office Applications with Microsoft Agent

Using the Office Assistant to Display Help

Programming the Office Assistant

The VBA help file give a list of motions for the assistants. Look for Assistants>Animation.
The code is simple:

With Assistant
.On = True
.Visible = True
.Move xLeft:= 400, yTop:= 300
.MoveWhenInTheWay = True
.TipOfDay = True
.Animation = msoAnimationGreeting
End With

Here are a few of the actions:

  • msoAnimationBeginSpeaking
  • msoAnimationCheckingSomething
  • msoAnimationGetArtsy
  • msoAnimationGetTechy
  • msoAnimationGetWizardy
  • msoAnimationRestPose
  • msoAnimationSendingMail
  • msoAnimationThinking
  • msoAnimationWorkingAtSomething
  • msoAnimationWritingNotingSomething
  • msoAnimationCheckingSomething
  • msoAnimationGetTechy
  • msoAnimationListensToComputer

See all Topics

Labels: ,

<Doug Klippert@ 7:45 AM

Comments: Post a Comment

  Friday, August 03, 2007 – Permalink –

Undo Excel

Level talk

In Excel 2007. the number of levels of the "undo stack" was increased from 16 levels to 100.

Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversable.

And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

If you think the number of undos should be changed, here's how:

  1. Close any programs that are running.

  2. Click Start, click Run, type regedit in the Open box, and then click OK.

  3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2007

    Microsoft Office Excel 2003 uses Office\11.0\
    Microsoft Excel 2002 uses Office\10.0\
    Microsoft Excel 2000 uses Office\9.0\

  4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.

  5. On the Edit menu, click Modify.

  6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.

  7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Modify the number of undo levels

If you want to clear the undo stack, just run a macro such as:
Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

Allen Wyatt:
Clearing the Undo stack

See all Topics

Labels: , , ,

<Doug Klippert@ 6:36 AM

Comments: Post a Comment

  Saturday, March 31, 2007 – Permalink –

Comment Code

Edit toolbar

You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.

To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

Ross, suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.

See all Topics

Labels: ,

<Doug Klippert@ 7:06 AM

Comments: Post a Comment

  Saturday, March 24, 2007 – Permalink –

Splash Screens

A nice sparkle

After working hours to perfect an Excel project, the last little piece that adds a touch of class, is a splash screen.

Here are the instructions to construct this type of user form including a workbook that you can download:
Userform - Splash screens

Ivan F Moala administers this interesting site called The Xcel Files

See all Topics

Labels: , ,

<Doug Klippert@ 5:26 AM

Comments: Post a Comment

  Friday, February 16, 2007 – Permalink –

Progress Gauge

Don't go away

If your macro is going to take a bit of time to complete, it is good manners to inform your users what is going on. If screen updating is turned off, they may think their machine has frozen.

Andy Pope has some great charting examples and also demonstrates a number of
Progress meters

Here's a static example:

Progress display

John Walkenbach, also, has tips about how to create a Progress indicator

also see:
Chip Pearson

Dick Kusleika

See all Topics

Labels: ,

<Doug Klippert@ 6:35 AM

Comments: Post a Comment