My Excel Pages -- David McRitchie


My Excel Pages:  http://dmcritchie.mvps.org/excel/excel.htm
Copyright ©: 1997 - 2008   F.  David McRitchie

Brief Description -- My Excel Pages

Excel macro solutions as building blocks with usage notes to help beginners and fairly advanced users work with Excel spreadsheets. Worksheet Formulas, VBA Macros, Backup & Recovery, Color Palette, Conditional Formatting, Event Macros, Excel Newsgroups, Response Time Considerations, Generating HTML, List of Functions and Subroutines, Shortcuts, Menus/Toolbars.  – David McRitchie (over 200 Excel web pages)

Quick Reference to Other Pages on Site (Mostly Excel)

[INDEX on/off site]  [Search] |GG| [Newsgroups]  [Outlook Express]  [Posting/Netiquette]  -- [backup/recovery]  [Date & Time]  [formula/formats/install macros]  [pathname/headers/footers]  [Proper/Caps/Upper]  [Reformat/join/split]  [Shortcuts]  [Slow Response/Memory problems]  [Toolbars] 
Complete List of  “Articles on this site”, below — newest additions:  [Firefox] 

Quick References to Headings on This Page

[articles], [shorts], [other], [xlhelp], [faq], [whatsnew], [arto], [advtopics], [xltips], [tutorials], [vbatutorials], [mskb], [selfhelp], [forms], [style], [links], [downloads], [xl2html], [moredown], [API], [xlnewsletters], [ext], [desktop], [misc], [trademarks], [questions / contact],
    Have a little fun with frames encompassing this page ...  [View without Frames]
Create an additional vertical split or an additional horizontal split -- ;
    Have a little fun with bookmarklets
 
This Site Was Mentioned in LockerGnome Mar 30, 2001 & Sept 5, 2002, and in PC WORLD, May, 2001

index index index index index index index index index index index index index index index index index
index   to some of the onsite pages and off-site pages I reference
index index index index index index index index index index index index index index index  index  index
David McRitchie[What’s New]David McRitchie    David McRitchie[Search]David McRitchie

Some information I have put together in using Excel spreadsheets.  The Excel that I use is Excel 2000 Excel 2000 (version 9) and it is running under Windows 2000.  .  When I started these pages I was using Excel 7aXL95 (XL ver 7) (also referred to as Excel95 or XL95) on Windows NT 4.0 Workstation.  Except for the use of hyperlinks added in XL97, most of things on my pages should run on XL95, XL97, XL98 (Mac), XL2000, XL2003, and XL2007.  I will try to provide information for earlier/later releases when updating and when aware of differences.  It is through the newsgroups that I have learned the most and it is probably through the newsgroups that you have come to my pages.

Now that search engines have improved and can limit hits from a site, most of "My Excel Pages" are now found by search engines.  At one time only the page you are looking at was indexed because if you don’t want to see this page you certainly wouldn’t want to see two-hundred more like it from a search engine.  However, if you do a site search here then you do want to find most of the hits, so you can use the Google search in the upper right corner. 

strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings
Working with  strings   as text and in cells refers to pages onsite and off-site.
strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings  strings  strings

Articles on site (#articles)

  *  Important information.  EXCEL newsgroups.  Problem Solving.
  o  Displays, critical information with macro solutions
  o  Displays, How To ... Solutions
  o  Worksheet Solutions
  x  Problem Solving Subroutines (Macro and Function Solutions)
 
*  Posting to Excel Newsgroups
Hints And Tips For New Users by Chip Pearson.  [formerly as newposte.htm]
Posting to Excel Newsgroups (on my site) can be used to further explain some of the earlier items in Hints to New Posters how to turn off MIME.  Also locate some commonly used acronyms:  BTW, FWIW, HTH (Hope This Helps),  TIA and another acronym you will see in these newsgroups:  MVP.
*  Excel Newsgroups and Searching Newsgroups
Information on searching newsgroup archives at Google News to obtain solutions posted in previous Excel Newsgroup postings, and some information on searching the MS KB (Microsoft Knowledge Database).
 
The following documents describe Subroutines and Functions that I have found useful and hope you will also find them useful.  (keywords: SUB and FUNCTION )
 
*  A1ONLY   (macro only, no web page)
A macro to copy the current sheet and retain only rows that have a perceived value in Column A. 
*  Backup your Files ★   Backup your files, always take backups
Backing up your files is critical both at home and for business.  Backup and Recovery sections.
Table of Contents, Sort Sheet Tabs, hyperlinks  Build Table of Contents and similar listings, and working with Hyperlinks ★, 
Create a Table of Contents with hyperlinks to the other sheets in your workbook using the BuildTOC() macro.  The BuildTOC macro will sort the names for you.  (Hyperlinks were introduced in XL97).  $$TOC is the suggested sheetname for this content.  Additional macros relating to documentation and organization can also be found here including listing addins, functions and macros.  Also have included information on creating and viewing hyperlinks individually and how to remove (delete) them singly or from a range.  Includes subroutines:  BuildTOC, SortAllSheets (sort worksheet tabs), hyperlink: (DelHyperLinks [delete hyperlinks], ConvertHyperlinks, MakeHyperLinks, RunSubFromActiveCell, LinkFix, LinkFix_GoTo, MakeHTML_Link) and Functions:  (URL;); listings( Enumerate Addins (add-ins));
GoTo and Navigations:  Previous Sheet Next Sheet  GoToCell, GotoSheet, GoToHyperlink, GoToNextSheet, GoToPrevSheet, GoToSub, GoToSubroutine, ShowTopLeft and ShowTopLeft5.  Navigating to the Previous/Next Sheet Tab, makes looking through 100 sheets a lot faster (alternative is Ctrl+PageUP and Ctrl+PageDN). 
An auxiliary page on how to Build a Summary Sheet describes how to create a summary sheet if the sheets in a workbook have a consistent format.
*  Cell Comments   Cell Comment ★
Create a file showing cell comments in a book, and view results with your web browser.
*  Clear Constants will clear constants in the selection area.
This page is an example of creating a macro, and creating a toolbar icon to be used with the macro.  Information here has been described in much more detail on other pages but this page is sufficient to accomplish these tasks for this macro.  In fact you might want to first look at this page as an overview before looking at Proper, Formula, and Insert Row.
*  Collections, a few collections
Excel colors palette, colours*  Color Palette -- 56 Excel Colors ★      [additional keywords:  colour, colours]
Attempts to equate Excel ColorIndex values to RGB colors used in HTML.  Includes formatting colors: [BLACK] [BLUE] [CYAN] [GREEN] [MAGENTA] [RED] [WHITE] [YELLOW] [COLOR1]..[Color56] and other color information.  Color Sorting (on another page).  Also see of coloring based on cell type.
*  Color Stripes, Range, Conditional Formatting
An example of coloring lines when the value in Column D (4th column) is negative.  Some information on ranges.  A quick look at conditional formatting available with XL97 and later. 
*  CSV (Comma Separated Values .CSV) files
*  Conditional Formatting ★
can be used for highlighting up to three ranges of numbers with color (text/background/borders/patterns) based on specified conditions.  C.F. can be used for “color banding” to simulate the old green bar computer paper.  You can use C.F. to highlight consecutive rows with same values; to highlight the row with the highest or lowest valued cell in a column.
datetime, date and time  Date & Time ★
Date and Time entries are stored as Date Serials.  Use of =NOW() obtains the system date and time and is updated during recalculation.  Additional date calculation examples including WORKDAY.  Some Date formatting, windowing and Y2K.
*  Delete Empty Cells, delete rows with cells of specific value,
Del95HTMLempty macro is designed to clean up the mess that Excel 95 creates when pasting from a web page into Excel
Delete Cells/Rows in Range, based on empty cells Delete Empty Cells but don’t disturb rows,    (also macros to Delete Empty Rows) DelCellsUp() will delete empty cells and cell with only spaces within range and move cells up from below even if not in range.  DelEmpty is similar but deletes only blank (empty) cells.  RemoveEmptyRows will remove rows that are entirely empty.
*  Duplicates, aspects of duplicates
in rows or cells from identification, suppression, removal or deletion.
*  Elapsed Time Counting Techniques
for Date Ranges:  COUNT, COUNTIF, SUM, SUMIF, counting
*  Email from Excel and from HTML, Start an Email for the recipients included
within a selection of email addresses,  additional examples include from a HYPERLINK Worksheet Function, and from HTML coding.
*  Enumerate Addins listing of addins
*  Errors in Worksheet Functions
(#...!)
*  Equal Length Cells per Column, generate text for posting from
a selection of cells, (HTML related) David McRitchie
also has an example of a Pivot Table.
worksheet change events  Event, Change_Cell Events
Worksheet events provide a means of automatically triggering a macro when something happens on the worksheet. (change, calculation, double-click, right-click, selection change, worksheet become active or is deactivated, and follow hyperlink).
Excel Window  Excel Window, Workbook Window, Worksheet Window David McRitchie 
Minimizing, Maximizing and Restoring Excel Window frames and help with Loss of Blue Title-Bar
*  Extraction of a Group of Digits and Dashes, posted by Harlan Grove.  Examples
use “Regular Expressions” in the LIKE operator. i.e. If Mid(s, i, 1) Like "[!-0-9]"
Also see case sensitivity within “Proper and Other Text Changes”.
*  FillEmpty, Fill in empty cells
Fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.
*  Fill-Handle, Filling in an area, Replication and use of the Mouse ★ (fillhandle)
The fill-handle is one of the most powerful tools in Excel, and normally alleviates the need to type in formulas similar to the previous lines.  Additional techniques using the mouse to move rows or columns; and insert blank rows or columns all using the cursor in combination with the ctrl, shift, or alt keys.  (related: shortcut keys, Insert Rows)
*  Fit Print to Page, and Adjustments to Layout
*  Font Information
User Defined Functions:  ISBOLD(cell), FontList, changing the Font used in cells from Courier typefaces to Times New Roman.  Also see...
*  Font Tables as Rendered by your browser for symbol, webdings, wingdings,
wingdings 2, wingdings 3. (Also see Symbols and Unicode.htm).
formulas, formats, macro, macros   FORMULA ★, display, in use in another cell
Initially describes how to install a macro, and a function, and how to invoke a macro and a function.  Describes how to display the formula used in another cell, and how to display the formatting used.  This information is particularly useful for debugging and for preparing printed documentation of formula, format, and font information used directly on the spreadsheet.  Includes how to tell if another cell is a formula or an entry.  Quick method of viewing both formula and format utilizing a msgbox without going to the Format menu.  formulas, formats, macro, macros   Functions include:  GetFormula, GetFormulaD, GetFormat, HasFormula, BoldSum, FontStyle, FontInfo, UseFormula, plus subroutines including FormulaBox to show cell information, and sheet statistics.  Includes some worksheet functions (Large/Small).  Includes instructions to install a subroutine (SUB) or function and how to add descriptions to the [fx] Function Wizard. Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128.  Format example includes formatting for India/Thai currency -- Rupees, Rs., Paise, lakhs, crores, Bhat.
*  Fractions and Formatting of Fractions
This is basically a subset of the Formula page.
*  Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
Bernie Deitrick expanded an initial contribution concerning Carpentry Measurements on the Formula page.
*  Freeze Panes and Rows/Columns to repeat.
When viewing a sheet we usually want to see the descriptive column and row headings anywhere on the sheet whether display or on the printed page.  Also see Headings for Columns, and Page Numbering page.
*  ghosting.txt  “sticky keys” like problem where cells don’t unselect properly. 
Actually what is seen are cells from other sheets ghosting through.  Not to be confused with “Sticky keys” which is valid disability keyboard option that allows use of sequential use of single key instead of combinations for shortcuts (i.e. Alt+Enter is a combination.
*  Gridlines, and Borders covers loss of gridlines or borders.
*  Headings for Columns, and Page Numbering page.
*  Highlight Changes Change and identify author of change. 
//See warning about not being able to access macros//
*  HTML, conversion from Excel, and Browser Bookmarks (Favorites).
*  IF THEN in worksheet functions and in VBA,
can be little tricky to those just starting Excel or VBA.
*  InputBox and MsgBox
*  INDEX  to my onsite pages and off-site pages I reference.  INDEX *
Plus a few of particular interest even if not referenced on my pages.
*  INDIRECT  Returns a reference indicated by a text value  
Used to find the value of a cell pointed to by the address in another cell.
*  Insert Row using a Macro  [Insert Row]
The macro described will insert row(s) below a selected row or cell.  The advantage of using a macro to insert rows is that the formulas will be copied, but not the data; providing a more reliable method of inserting lines than simply inserting a row and then dragging a row with formulas and data into an inserted empty row.  Also included is a fix for right minus sign (right negative sign) that you are likely to encounter on bank statements.
*  LASTCELL, Reset Last Cell Used
Attempts to provide additional information concerning eliminating unused rows at end and unused columns to right of sheet beyond what can be seen in Q134617 (dead MS Link).
Macros:  CleanUpLastCells attempts to fixup last cells on all sheets, MakeLastCell to force the active cell to become the lastcell, and QueryLastCells to identify sheets with large used cell area.
*  Logical Expressions - AND(), OR(), NOT()
*  Logo or graphic into a header or footer
Headers and footers do not support placing a .BMP graphic directly into a header or footer, but there are some alternatives such as using the placing the logo on the first row of the spreadsheet and incorporating spreadsheet rows as part of or as a replacement for the header.  Something that works in both headers and footers is to use a typographical font symbol.  You will probably find a technique that you can use.  Also of interest in how to incorporate column headings on printed pages.
*  Mail Merge, Printing Labels using Mail Merge with data from Excel Word
Excel cannot print postal bar codes but MS Word can print postal bar codes and can obtain data from an Excel spreadsheet.  One is expected to use Mail Merge even for just printing labels.  Includes a macro, RepeatRowsOnColumnA, to set up for printing multiple labels with same content. (Mailto: in HTML)
*  Make hundreds of HTML files *
from a list of filenames in Column A and HTML skeletal code from column C.  Example uses Basic commands to Open/Close and Write/overwrite to file.  View sample in Notepad and in your web browser(s).  Much of the basic writing to a file is similar to the more complex   Menus
My page on documentation of workbooks includes links on creating Menus, see Related.
*  Merge and Unmerge Cells
MergeRxR will merge a large group of cells one row at a time so that rows remain distinct but the columns are merged -- useful for forms designed on a spreadsheet.  Macros include:  MergeRxR, MergeRxR_join, MergeCxC, UnMerge, UnMerge_Selected.
*  Miscellaneous Worksheet examples
Mixed worksheet function code segments not otherwise categorized.
*  Mouse Operations, Use of Mouse and keys to move data, and insert rows or columns,
and to jump around
*  Multiplication Table Quiz, for 12 x 12.
Done entirely with Worksheet Event macros.
*  Name and Address labels (macro code) to
convert 1-up labels to Spreadsheet format on a new sheet.  Description can be found on my Snake Columns page.  (also see Mail Merge)
*  Names, name labels in the workbook, addresses problems with creating names.
*  Navigation,  Navigating within a Sheet and Around a Workbook (onsite material).
*  Newsgroups and Searching Newsgroups, Excel
Some information on searching for information using Google Usenet Advanced Search to search Excel Newsgroups, and some information on searching the Microsoft Knowledge Database (KB).
*  Number conversions. 
Conversion of numbers which appear correct but are not validly interpreted in Excel.  Numbers with a right minus appear correct but are treated as Text.  US numbers use period as decimal fraction separator, and commas as thousands list separator, which is the reverse of many other countries.  ReenterAsNumber converts text to numbers or date.
*  OFFSET, use of to maintain formulas -- Specifics on use of OFFSET
(continuation of InsertRowsAndFillFormulas macro)
*  Paste Rows from one sheet to another and Paste / Pasting
*  Pathname in heading, footers, cell ★,   includes formatting a date in the footer
Excel does not provide the same level of support for headings and footers in Excel as in MS Word.  Included here are some things that can be done using Visual Basic (VBA).  The main support offered is in putting the fullname in a footer Insert Footer , but material also is included to tell how to put this information into a cell and onto the titlebar.
*  PDF with Excel
Transferring data from PDF files with Adobe or Adobe Reader into Excel.
*  Posting to Excel Newsgroups, Usenet Netiquette,
particularly in the Excel Newsgroups, how to turn off MIME/HTML posting, and helps to explain some of the Hints to New Posters (Chip Pearson). 
*  Previous, Return to Previously Selected Sheet like a hyperlink not as Previous Sheet -- Ctrl+PageUP  Next Sheet -- Ctrl+PageDN 
*  Printing with macros
Printing in Reverse order, printing specific range per sheet.
*  Proper, and other Text changes ★★
PROPER, LOWER (LCase), and UPPER (UCase) functions.  TrimSUB subroutine to TRIM cells in a selection.  Stresses efficient coding, cell selection and restricting processing to specific kinds of data with SpecialCells by types(text, numbers, formula, constants).  Proper Subroutine includes additional code to correct certain surnames beyond builtin features of Proper Worksheet Function, and because these are macros, the changes are done in place.  [capitalization, lettercase, lower case, lowercase, lower-case, proper case, title case, upper case, upper-case, uppercase]
*  Properties, Title/Author/... Property, UserID, etc.
Excel Maintains: Title, Subject, Author, Keywords, Comments, Last Author, Application Name, Last Print Date, Creation Date, Last Save Time, Security, Category, Manager, Company
*  Push down values in Column if not lowest in row (pushdown.htm)
*  Reformat or Rearrange Data in Columns (join.htm) ★
Macros especially useful for reformatting name and address listings, and for creating test data.  Rearrange columns by splitting, joining, or reversing columns of data.  Join() can be used as a reversal of a correctly executed text to columns.  Lastname() can be used as a put lastname first is not already done.  FixUSzip5() corrects US 5-digit zipcodes by conversion to 5 digit numbers as text.  ReEnter() equivalent to F2 then Enter, includes some specialized versions (also see Number Conversions page).  SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from beginning of text.  TrimALL() is used to TRIM leading and trailing spaces from selected range.  ReversI() is used to reverse the order of items in a row, column, or range.  RotateCW() is used to rotate a range of cells 90 degrees preserving formulas and formats.  Selection area must include cell A1.  MarkCells() is used to create test data within the invoked range.  MarkSepAreas() includes cell address and area number for creating test data across multiple ranges.  i.e. A1-1, B1-1, B2-2,C2-2,D2-2.  Additional material includes FillSequence() numbers cells 1,2,3, etc. for selected range(s), a note on use of fill handle for creating sequence, and creating a sheet with short cut keys for reference (see results at Shortcut Keys in Excel 2000 through 2007).   ReproduceActive() Reproduce Formula in ActiveCell to selected ranges(s).  Also see Swap rows/columns.
*  Region, Simulating manual usage
Macro Record does not generate range codes, so coding needs cannot simply match what is done with keystrokes, such as the arrow keys.
*  Replace Worksheet Function and Replace in VBA
*  Replicate cells with INDIRECT formulas,
referring to same range on another sheet, so that cells will always refer to a specific source cell location even if some rows/cells/columns are deleted on the source sheet.
worksheet change events  Resize Row Height - problems with wrapped cells
*  Right-Click Context Menus in Excel
additions include copying and pasting formulas, autosum, column total, clear constants, and get formula.
-- also have a pages for Internet Explorer Right-Click Menus, and for Firefox.
*  SAVE problems in Excel
Problems related to LAN.  Some solutions to space problems can be seen in Slow Response and Memory Problems in Excel which follows.
*  SAVEAS, Save each worksheet as a separate workbook
Saving each worksheet separately to multiple workbooks.  You can use this subroutine to see how large each worksheet really is.
*  Shapes, listing of all shapes in a workbook, and working with shapes,
including list buttons and shapes on the worksheets (GetShapeProc); deleting all shapes (delShapesOnSht), selectively (delShapesSel); Selecting shapes (selShapesOnSht), Identify (IDthisShape), and how to redimension a shape.
*  Sheets (copying, new sheets, renaming, selection) and a similar page for Cells
related coding.  Some VBA coding for sheets with a few worksheet coding examples, also see BuildTOC, and Pathname.  Cells related coding.
*  SHELL invoked from VBA, i.e. invoke another application like Internet Explorer
*  Shortcut keys in Excel 2000 - 2007 ★     (Excel Keyboard Shortcuts shortx2k.htm)
An HTML page reference for keyboard shortcut keys that you can use your browser’s FIND command to help find the shortcut you want/need.  Ctrl_A macro is a fix for Excel 2003 problem with Ctrl+A and contains an example of Application.RecordMacro for use in embedded macro to generate code otherwise missing.  (related: Fillhandle and Mouse) 
*  Slow Response and Memory Problems in Excel
Many things can slow down Excel, and affect performance, one of the main reasons is Journaling (logging) of file opens and closes that began in XL97 across all of Office 98.  [code, efficient, fast, memory, slow, speed, response, sub]
snake columns  SnakeCols,   Snaking columns allows you to print several columns on one page and is a frequent request
on the Excel Newsgroups.  The feature is not supported in Excel, but is easily done by pasting your Excel columns into MS Word.  I have described how do this using Word retaining your column headings as well as describing my own incomplete macro should you want to do this completely in Excel.
*  Sorting, notes on,   The difference between text cells and number cells is a frequently misunderstood aspect. 
Excel does not sort in a strictly ASCII sequence, it has it’s own sequence, but the biggest shock even to those aware of both ASCII and EBCDIC will be the treatment of cells with numbers as opposed to text cells.
*  Sort TCP/IP addresses and Chemical Names.  Extract Domain Name from a URL for sorting major to url.
*  Status Bar, parts of status bar, and parts of Excel Screen ★
*  Stocks, A lot of postings on Stock quotations. 
My own advice is to purchase a financial package, but there are some rather interesting things you can do with Excel accessing online quotes.
*  Strings ★, Manipulating, rearranging, and extracting from strings. 
topics include searching within strings, case-sensitivity, generating check protection wording from numbers.  Many of my pages tend to deal with strings and I have tried to gather the important points concerning strings from my site and from links to other sites. 
*  SUMIF
Primary example uses SUMIF to provide subtotals by date.  Syntax and additional examples are also included.
*  Summarizing Data, and Auto Filter (an Overview)
Summarizing Data Examples: Auto Filter with Hotel Room Availability Example, Pivot Table, Sub Totals, Counting/Counts (COUNT, COUNTIF, SUM, SUMIF, SUMPRODUCT), Conditional Formatting, Sum of Visible Cells.  Filter Test, AutoFilter and Hidden Rows/Columns
*  Swap Rows, Swap Columns, based on a selection with exactly two areas.
*  Templates, book.xlt, sheet.xlt, and beyond, customized defaults for future
workbooks and sheets
*  Toolbars, Custom Buttons and Menus  xl7 (Excel 95) xl8 (Excel 97) xl9 (Excel 2000)
After messing up my toolbars more than once, I would recommend printing a copy of your current toolbars as seen on your Excel window.  Refer to the article for more information of changing and restoring your toolbars.  Also for creating a Customized Button.  Have added information on menus in Excel 2000 xl9 (Excel 2000) which has changed from methods used in XL95.
Insert Row, Formulas  Top of Column  Bottom of Column  Insert Footer  #1 Reassign for testing  #2 Reassign for testing  #3 Reassign for testing  #4 Reassign for testing  List Subs and Functions  (sour face) Reassign for testing  Macros [Alt+F8]  GoTo Sub or Function  Backup By Date  Previous Sheet -- Ctrl+PageUP  Next Sheet -- Ctrl+PageDN  MakeHTML_Link  Euro  IE5 - Goto HTML  CharMap  notepad    (These like toolbuttons are 16x16 bit images).  Restoring an .XLB file from a backup may require reentering the assigned macros, see BarHopper macro to recursively reassign the macro names, seems to reset something internally to get them functional.
*  Validate / Validation * 2001-04-29
Some validation examples for use with the Data, validation menu.  Worksheets can be protected against incidental damage to the integrity of the worksheet by limiting access to some cells.
*  Visual Basic Editor (VBE) Window, fixing docking back to vertical. * 2001-03-29
*  VLOOKUP ★
Example for the VLOOKUP Worksheet Function using a lookup in an Estate Tax Table.
Paint Coverage Example, Paint
*  Worksheets in VBA Coding and in Worksheet Formulas.  Examples of cell references to another sheet or another workbook.  HYPERLINK worksheet formulas.
*  WordCount
Code for WordCount() was derived from John's tip page look for something related to count or counting (pages are different used to be extract element.
*  XL2GIF
Some coding mostly from Harold Staff to convert a group of cells, or a picture to a .GIF file.
*  XL2HTML and XL2HTMLX, Excel to HTML conversion * 2000-07-02
Create HTML code from a group of Excel cells.  Also see my HTML section on my Excel to HTML web page for more HTML things.  There are two macros here of importance:  XL2HTML to convert selection to HTML with text color, and XL2HTMLx to convert selection to HTML with shaded row and column headers.  Shading from Conditional Formatting is covered in these macros. 

Short Topics and Examples may or may not be related to Excel (#shorts)

Some Other pages on my Site   (#other)

*  AOL, Experience with AOL 4.0 (16-bit) using a Windows NT 4.0 system
*  Office 2000, my experience in installing - Progress report
*  Bookmarklets for Internet Explorer, Netscape, Mozilla regain control
over unwanted webpage features like background images, bad CSS coloring schemes, and do neat things like sort HTML Tables (Mozilla), Combining  “highlight links”,  and “int/ext links”   really makes links show up.  Click on both links on the left to see the change and differentiation between internal or external links.  (Use F5 to reset page)
*  Firefox, notes on installing, customizing, and use of the Firefox web browser with blocking ads, use of all of the fonts.
*  Free Web Access with Free Email
for those who aren’t online much.  Also includes information on setting up Outlook Express, and some “must have software”. 
* INDEX  to my pages,    index index index index index index index index index
plus additional links to articles in postings, on other sites, and some short descriptions not necessarily mentioned or related to others on my site. 
index index index index index index index index index index index index  INDEX
*  Internet Explorer and Outlook Express, IE6 keyboard and mouse shortcuts, IE6 right click shortcuts, bookmarklets, OE6 shortcuts
*  Outlook Express 5.0 through 6.0, some techniques in using.
Includes information on combating computer viruses and spyware. (virus)
*  Running Tasks list found in Microsoft System Information (MSInfo32.exe)
*  Symbols for HTML use Euro(€),wingdings, webdings, and installing CharMap macro and toolbar icon
also a page on Fonts showing your default font, webdings, wingdings, wingdings 2, and wingdings 3

[Site Search -- Excel] 
My Macros are Above   (UP to articles) .

HELP within Excel   (#xlhelp)

Worksheet Functions and VBA, HELP  (onsite)
Where to find help for Worksheet Functions in HELP, now that we are essentially working without printed manuals.  Also provides some help on finding the VBA material in Excel Help.

Frequently Asked Questions   (#faq)

Also see my page on Links to Frequently Asked Questions relating to Windows 98/NT etc., and some additional EXCEL FAQ sites as well. 

Capacities and Limits in XL95 (or for your own Excel)

HELP --> answer Wizard --> length and columns and rows --> Tell Me About ...

Microsoft Excel for Windows specifications (Check your own HELP file for "specifications")

The maximum rows was increased to 65,536 in XL97 from 16,384 in XL95.  The number of columns 256 remained unchanged.  VBA coding should not refer to a specific number of rows or columns instead use cells.rows.count and cells.columns.count – The Excel 2007 (Excel 12) grid is 1,048,576 rows by 16,384 columns (2006-03-09 (broken), David Gainer's blog (broken)).  I have been using cells.rows.count in VBA since mid 2000 once I found out how to avoid the constants.
Excel FAQ The FAQ list for Newsgroup: comp.apps.spreadsheets can be found on the Internet: Some Links to Frequently Asked Questions relating to Windows 98/NT etc., and some additional EXCEL FAQ sites as well. 

What’s New in Excel  (#whatsnew)

In the future this page may be split up into two sheets at this point.  This will limit the usefulness of using FIND here, but will make the top part look like a more normal “home page”.

Articles on Other Sites (#arto)

If a site is pointed out for a specific topic, it is assumed you will also look at the rest of the site.  Also check out the other topics especially those above where you think something would be.
John Walkenbach's site has moved Excel material from j-walk.com to a "php" formatted site, where you will find "VBA Functions" at the bottom of his tips page, now at. (also check the sidebar on his site)
The Spreadsheet Page - Excel Tips
http://spreadsheetpage.com/index.php/site/tips

You should be able to find old articles at archive.org and then search Google on long phrases in quotes and by including site:spreadsheetpage.com in your Google Search.

Microsoft is notorious for changing url names, such that the half life of most MSDN and other articles. The good news is that the half life has probably been raised to six months. Since the articles are created when referenced, such articles are not archived at archive.org so it makes finding the articles under their new urls very difficult. All Microsoft links were checked during Summer of 2007 and most of them are broken a year later.

I will try to update the links on my page for the MSDN articles but since Microsoft and ISPs intercept 404 errors, they don't even show up as errors if you run a link check scan. Often the best I can do in a short time is to mark it as (deadlink) especially if I can't get a title match that looks like it would be the article.  If I spent 10 hours a week fixing Microsoft links, I'd still not be able to keep up.

Also see Missing Webpages, Missing sites on Referenced pages

Advanced Topics   (#advtopics)

Excel Tips   (#xltips)

Function References and Tables (#references) ★

Items here are references to functions, also see the download area.
Also see downloads area, for references such as Peter Noneley's Function Dictionary.  I had several of the above listings incorrectly places in that area because of their relationships.

Excel Lessons & Tutorials (#tutorials) ★

There are many excellent Excel tutorials available on the internet, which accounts for the somewhat excessive number of red (★primary) and blue (★secondary) stars shown below in this area.  You should be able to find one that suits your own preferences.  Going through one or two tutorials should get you past the introductory materials so that the book you purchase later will be a book you will use as a reference book rather than one simply oriented to beginners.

and some Visual Basic for Applications ( VBA ) materials   (#vbatutorials)

Microsoft Knowledge Data Base (MS KB)   (#mskb)

The ultimate in Excel self-help and finding solutions are the Excel HELP files, and the Microsoft Knowledge Base (MS KB).  The following search will provide you with a wealth of various Excel examples.

Microsoft Knowledge Base Search ★
   http://search.support.microsoft.com/kb/c.asp (invalid MS link)
   My Search is about: Excel for Windows
   Keyword Search using: All Words
   Keywords: kbprogramming kbdta kbdtacode KbVBA kbhowto
      [x] Full text
      [x] Titles and excerpts

Newsgroups ★
Another valuable source of information are the peer-to-peer newsgroups where users post and answer questions, and the Google newsgroup archives where practically every questions and answers to almost any question can be found more information on Newsgroups and the Google archives. When you use text only newsgroups

Forums, most so called forums are recognized by their advertising banners and other revenue generating advertising in a parasitic relationship to the newsgroups where the questions are really answered.  The worst part is that such forums (well over 60 Excel forums) is that they interfere with web searches by pulling in copies of newsgroup threads that they touch.  Many forums also don't really maintain correct threading leading to confusion.  There are a few good forums (perhaps 2 to 4) and you will not find questions or answers posted through them on a web search or on a groups search at least not through Google.

Web based interface to Excel newsgroups, the only one that I would recommend if you must use a web interface due to a corporate firewall is Google Groups.  Since Google has web based searches (Google) and Google Groups searches, Google is very careful to exclude their newsgroup database from a web search.  [that is no longer true, newsgroups are appearing in web searches, be careful, try to stop the overlap]  Another web based interface that does not interfere (in itself) with web searches is Microsoft Communities hosted on Microsoft.

additional self-help with VBA macros in Excel (#selfhelp)

The newsgroups are a great source of help.  Most of the macro solutions posted are done in microsoft.public.excel.programming so include that in the newsgroups you look at.

You could pick up books by John Green or John Walkenbach (books), both are frequent posters in the newsgroups and their books are informative.  I would go for the later XL2000 books even for XL97, since most of the information will be the same.  Other than XL2000 having more web stuff in it XL97 and XL2000 macro language and usage are essentially the same.

A lot of your self help will come from looking at the HELP files for VBA, which you will not find from the help at the Excel spreadsheet, but rather from where you code your macros. In XL95 while on a macro sheet, and in XL97 and above when your are in the VBE (alt + F11). The other place is by recording a macro and looking at the code.

The recorded macros do not do loops and do not combine things together.  When you modify recorded macros you want to look to reduce dependency on the activecell.  Most things can be done without changing the activecell.  You can generally combine statements and eliminate activecell.  Look at other macros to see how this might be done. Also look at macros to see how to reduce their focus to a limited selection area by checking for lastcell or by processing only non-empty cells.  If you watch for these things in your code and other peoples code that would be a good start.

Forms     (#forms)

Forms, some links posted by Harald Staff, misc 2001-04-05

Style       (#style)

John Raffensperger:  (no Excel updates since 17 Dec 2002, completely archived)  10 Tips to Improve Your Spreadsheet Style (archive) by John F. Raffensperger (archive), be sure to see newsgroup discussion. Additional information toolbar addin (archive) for auditing (archive)(precedents/dependents) Also see John’s educational site:  The New Guidelines for Writing Spreadsheets (archive) and his links (archive) page with comments where he has Spreadsheet Style as one of the topics, and has given ratings on webpage content of all of the referenced links -- one of the best link pages you will find even though now archived.  All these archived links can get to you, so here is some Walkenbach humor found in links page Spreadsheet Jokes (John Walkenbach, his stuff is also archived)
The following sites will provide links to a lot of Excel information.  The first has links to other sites belonging to other MVPs for Excel, well above average; and the second is a links page on John Walkenbach’s site that is as complete as you will find.  (What is the MVP programf (broken MS link) at microsoft.com) That’s it.  I don’t expect to see a more complete list than John Walkenbach's, you can email him and be added to the list.  Though there may be some specialized (noncommercial) lists that relate to a specific profession -- accountants, engineering special topics, statisticians, teachers.  For a list of Excel sites that has been evaluated by John Raffenspurger for content, and style see style (don’t ask to be added if your site is “under construction” or does not have content to help Excel users).

These Links are frequently cited in newsgroups for places to find Excel code.  These were basically pulled from one such posting.  Many of these people have been awarded as Microsoft Excel MVP.

Excel Downloads   (#downloads)

Most of the sites above also have downloads so always check out the rest of the site.

Excel to HTML (#xl2html) -- information & downloads

HTML material particularly involving conversion of Excel sheets to HTML has been moved to it’s own page.  [new]
    http://dmcritchie.mvps.org/excel/xl2html.htm

More Downloads for Excel -- downloads (#moredown)

API Code   (#api)

API calls allow you to find such things as Username, Computer name, Operating System, and to such things as Bubble Sorts, etc.  Many of Excel sites also have such code and downloads.  (also see Properties.  I have no API code on my site).

Excel Newsletters   (#xlnewsletters)

Extensions (#ext)

File Extensions in Excel

See Excel HELP for more extensions related to Excel and additional information.
    Help --> file formats --> converting --> File format converters supplied with Microsoft Excel
 
 
 XLA  MS EXCEL add-in file written in VBA
 XLB  MS EXCEL toolbar file
 XLC  Excel version 4 chart file
 XLK  MS EXCEL backup file
 XLL  MS EXCEL add-in file written in C or C++ (.DLL related) 
 XLM  MS EXCEL macro
 XLS  Excel for Windows spreadsheet file
 XLT  MS EXCEL template
 XLW  MS EXCEL saved workspace file

Some others you may encounter and possibly backup.
ACL, Automatic Correction List (in your windows directory)
WAB, Windows Address Book

Be sure that your folder options (My Computer) show file extensions for your own safety.  Most people should also make sure they see hidden files as well, in fact you may not see your *.xlb (toolbars) file unless you do show hidden files.  System Files may be hidden so people don’t see them, don’t know about them, don’t copy them, or can’t delete them.

How to configure Internet Explorer to open Office documents in the appropriate Office program instead of in Internet Explorer.  You may find IE is opening Excel (.xls) files from the internet instead of letting Excel open them.

More File Extensions:

Some tables of file extensions for further interest, including one in German.

DLL help (#dll)

Microsoft < Knowledge database [MS KB]   (#kb)

MS KB articles have a half line of six months before being renamed. (keeping a webpage referring to such links is a losing battle)

There is another MS KB topic on this page.

The following happen to be VBA -- MS KB articles are mentioned on at the bottom of other articles on this site where appropriated. 

Other material on MS site

Links Unrelated to Excel (#desktop)

This/These looked just too good to ignore.

Miscellaneous Things unrelated to Excel   (#misc)

A lot of my material is REXX macros as you might discover if you leave the Excel area.  About the only path back is the default entry for my site, the back key on your browser, or a bookmark.

 One thing that bothers me is the black background for the DOS window under Windows NT.  I made some changes for myself, I don't know if it would work for you or under Windows 95, but it works well for me.  Refer only to the section of the document marked Properties for DOS Session under Windows NT, my own usage and use you browser's BACK key to return here. Tried to do this on a Win98 system and properties did not have a Color tab, so it is probably only available on WinNT

If you don’t like your web browser’s default colors (gray background) for viewing .TXT documents you might take a look at another document of mine.  Be sure to record your original settings, changed settings, and the date so you can put them back if you don’t like your changes. 

Microsoft Windows Vista Home Basic Edition, some notes on conversion from Windows 2000, see Vista (2007-08-10).

Web Pages – Table of Contents for Web Pages   (#webpage_toc)
Firefox users can see a Table of Contents at anytime for pages with H1-Hn headings by installing an extension for sidebar viewing, or a bookmarklet to create a secondary statusbar dropdown list. (see Document Map)

Trademarks (#trademarks)

Excel®
Registered to Microsoft Corporation.
Open Directory Project: http://dmoz.org/Computers/Software/Spreadsheets/Excel/

Questions / Contact   (#questions / #contact)

Important Note;  This site has been moved to mvps.org where several current and former Microsoft MVPs have pages and many at least have links, you should now be reading this page at http://dmcritchie.mvps.org/excel/excel.htm without ads and about four times faster than was available formerly at the Geocities site (Geocities shut down free websites Oct 26, 2009) where traffic was not only slow but the site was frequently unavailable.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced  Search Excel newsgroups  (or search any newsgroup).

Non Excel web search sites and information, basically what one might need away from their own computer when travelling -- but it does include a  Search of  “My Excel Pages” at the top, and some quick links at the very top.

New material specifically for Excel 2007 and for Vista, and another for Windows 7 have been added to my website.  Most other updates are simply minor changes to existing Excel pages.
This page was introduced on January 1, 1998 when My Excel Pages was first put online. 

 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Excel Newsgroups]    [Go Back]    [Return to TOP] 

Missing/incorrect links for pages of other people and Microsoft, see Missing Webpages, Missing sites on Referenced pages.  After spending a little over one year trying to work with the horrible Excel 2007 ribbon interface, I basically threw in the towel on Oct 1st, 2008 after not being renewed in the MVP Program.  I spend my free time now playing with the Firefox web browser.  Fortunately my Excel web pages apply to Excel regardless of version, and it is so nice to hear that they still help a lot of users.

Each of my web pages has it's own email introduction, so I can tell what page you were looking at when you email me, so please use the email link on the page you are writing about.

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2016,  F. David McRitchie,  All Rights Reserved