| Document: |
Description: |
Date: |
| Calculate workdays |
Custom functions used to calculate the count of workdays between two dates. |
2008-09-18 |
| Websites |
Links to other websites with useful information. |
2008-09-15 |
| Games and time-wasters |
Games, time-wasters and a SudokuSolver, demonstrates different programming techniques. |
2008-09-02 |
| Return unique items from a cell range |
Return all unique items from a worksheet range. |
2008-08-14 |
| About Erlandsen Data Consulting |
General information about Erlandsen Data Consulting (EDC), available services |
2008-07-04 |
| Useful Tools |
Useful tools, remove password protection, document workbooks, encryption. |
2008-07-04 |
| Copy data from multiple workbooks |
Copy a cell range from one or all worksheets in one or more workbooks to a new workbook. |
2008-04-30 |
| Copy a module from one workbook to another |
This macro can be used to copy a module from one workbook to another |
2008-04-26 |
| Newsgroups |
Newsgroups contains lots of useful information about Excel spreadsheets and macro programming. |
2008-02-25 |
| Autoscaling fonts in charts |
Change the font autoscale behaviour i charts by making a change in the Registry. |
2008-02-22 |
| Get Contact Information from Outlook |
This example function shows how you can retrieve contact information from Outlook |
2007-08-13 |
| CommandBar Examples |
CommandBar examples, custom popup menu, CommandBar tools |
2007-04-17 |
| Import from a delimited textfile |
This macro imports data from a delimited textfile to a worksheet range. |
2006-08-28 |
| Array variables |
How to use static and dynamic array variables. |
2006-07-31 |
| Export to a new workbook/worksheet |
This macro exports the values or formulas from a worksheet range to a new workbook/worksheet. |
2006-07-11 |
| Programming Examples |
Different useful programming examples for developers who create their own solutions. |
2006-05-18 |
| Grant your code access to the VBProjects |
In Excel 2003 or later you have to give your code access to the VBProjects. |
2006-05-16 |
| Chart Examples |
Chart examples, dynamic charts, statistical chart |
2006-05-10 |
| Function Examples |
Function examples, how to use built-in functions and user-defined functions. |
2006-05-07 |
| Return how many characters that will fit in the column width |
This function returns how many characters that will fit inside a column when you are not using the normal font. |
2006-01-27 |
| Basic file and folder examples |
Basic file and folder examples showing how you can select, copy, delete, rename, create files and folders. |
2005-12-28 |
| ADO Connection Strings |
Example ADO connection strings that can be used when connecting to some of the most common database types using ODBC |
2005-10-08 |
| Open and close the CD/DVD tray |
With these macros you are able to activate the coffee cup holder. |
2005-09-26 |
| Split a string and return any individual part |
This function can be useful when you need to return indivual parts of a string, e.g. first or last names |
2005-08-06 |
| Control PowerPoint from Excel |
Create a new PowerPoint presentation with information from Excel. |
2005-07-25 |
| Compare two worksheets |
Compare the content of two worksheets. The result is displayed in a new workbook listing all cell differences. |
2005-06-09 |
| Change the default printer |
Change the default printer with a macro. Print to network printers. |
2005-06-03 |
| Create new folders |
This function can determine if a folder exists, and also create any missing folders |
2005-04-26 |
| Export data from Excel to Access (ADO) |
Export data from an Excel worksheet to an Access table (ADO). |
2005-02-01 |
| List folders and subfolders with Microsoft Scripting Runtime |
Create a list of folders, included sub-folders. |
2005-01-28 |
| Contact EDC |
The easy way to contact EDC |
2005-01-27 |
| Import data from a large text file to multiple worksheets (ADO) |
Read data from a text file and write the contents to multiple worksheets (ADO). |
2004-12-17 |
| Read information from a closed workbook |
Read information from a workbook without opening it. |
2004-10-14 |
| Returning the page range addresses from a worksheet |
If you need to get the range address for each printed page you can use this function |
2004-10-08 |
| Apply a default header/footer using Custom Views |
How to apply a default header/footer to a worksheet before printing |
2004-06-07 |
| 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. |
2004-06-03 |
| How to open a file from VBA |
Use the Shell-command to open files, find the associated executable that will open a file |
2004-05-13 |
| Select filenames |
Let the user select one or multiple files as input to your macros. |
2004-05-05 |
| Erlandsen Data Consulting |
EDC Main Menu |
2004-03-01 |
| Worksheet functions in VBA macros |
Almost all of the built-in worksheet functions can be used in VBA macros. |
2003-04-27 |
| Return random numbers |
General information on how to return random numbers. |
2003-03-18 |
| Workbook Examples |
Useful workbook examples, compare, copy, print and format. |
2002-11-21 |
| How to use the macro examples |
EDC Excel & VBA Tips: How to use the macro examples on these web pages. |
2002-11-14 |
| Row and column background color |
Example macros that can be used to color rows and columns. Conditional formatting of rows and columns. |
2002-07-17 |
| Delete all macros in a workbook/document |
How to delete all macros from an Excel workbook or a Word document. |
2002-07-17 |
| Create a Pivottable based on data from Access |
Pivottables can be created based on data from an Access table or query. |
2002-07-17 |
| Display a message on the statusbar |
It's a good programming practice to inform the user of the macro progress by displaying a message on the statusbar at the bottom of the screen. |
2002-07-17 |
| Userdefined functions from other workbooks |
How to use userdefined worksheet functions from other workbooks. |
2002-07-17 |
| Map Trondheim Norway |
A map that displays where you can find Trondheim, Norway |
2002-07-17 |
| Sum by color |
Add values depending on the cell background colorN |
2002-06-26 |
| UserForm and ActiveX-control Examples |
UserForm examples, how to use different ActiveX controls. |
2002-05-22 |
| List, change or delete external formula references (links) |
List, change or delete external formula references (links) in cells that refers to other workbooks. |
2002-05-20 |
| Import data from a text file (ADO) |
Read data from a text file and write the contents to a worksheet (ADO). |
2002-04-02 |
| Calculate a weeks start date |
A custom function that calculates the start date for any given week. |
2002-01-26 |
| Export data from Excel to Access (DAO) |
Export data from an Excel worksheet to an Access table (DAO). |
2001-11-27 |
| Transfer data from a recordset to a worksheet (ADO) |
Transfer data from a recordset to a worksheet without using the CopyFromRecordset method (ADO). |
2001-11-27 |
| Import data from Access to Excel (ADO) |
Import data from an Access table to an Excel worksheet (ADO). |
2001-11-27 |
| Use a closed workbook as a database (DAO) |
Read data from and write data to workbooks without opening them (DAO). |
2001-11-11 |
| Use a closed workbook as a database (ADO) |
Read data from and write data to workbooks without opening them (ADO). |
2001-11-11 |
| What is an addin? |
Addins are used to distribute macros, userdefined functions or custom solutions to other users. |
2001-04-07 |
| Weeknumbers |
Calculate the correct week number |
2001-03-14 |
| Calculate holidays |
Calculate the correct date for Easter Sunday and other holidays. |
2001-03-14 |
| About Ole P. Erlandsen |
Erlandsen Data Consulting: More information about Ole P. Erlandsen. |
2001-02-16 |
| Basic information about OLE automation |
General information about creating and using objects from other applications. |
2000-11-07 |
| Log files |
Log error-messages and other useful information in a text file that can be read later. |
2000-11-02 |
| Fill a ListBox-control with values from another workbook |
You can fill a ListBox-control with values from another workbook either using the RowSource property or this example macro. |
2000-09-16 |
| Import data from a closed workbook (ADO) |
Import data from a closed Excel workbook without opening it (ADO). |
2000-09-16 |
| Determine which CommandBar button that started a macro |
Let the macros themselves determine which CommandBar button that started them. |
2000-07-31 |
| Mailinglists |
Mailinglists works almost like a newsgroup, all the articles sent to the mailinglist will be sent to you by e-mail. |
2000-05-16 |
| Built-in help |
The built-in help is the primary source of information about most applications. |
2000-05-16 |
| Insert pictures |
How to insert pictures at specific locations in a worksheet. |
2000-04-15 |
| Disable the floating help window |
In Office 2000 the built-in help window has an annoying habit of trying to "help" you by floating back and forth on the screen while you work. To disable this behavior you can make a change to the Registry. |
2000-04-15 |
| Display all installed fonts (Excel) |
This macro creates a list of all installed fonts. |
2000-04-15 |
| Display all installed fonts (Word) |
Create a list of all installed fonts. |
2000-04-15 |
| Calculate with dates |
General information about date calculations in Excel. |
2000-04-14 |
| Date calculation examples |
Useful example formulas for date calculations. |
2000-04-14 |
| Control Outlook from Excel |
Send information to Outlook (e.g. sending an e-mail message) and retrieve information from Outlook (e.g. retrieving a list av all messages in the Inbox). |
2000-04-12 |
| Control Word from Excel |
Send information to Word (e.g. creating a new document) and retrieve information from Word (e.g. reading information from a document). |
2000-04-12 |
| Determine if an application is available |
Use this macro to determine if an application is available and avoid run-tim errors when you are using OLE automation. |
2000-04-12 |
| Control Excel from Word |
Send information to Excel from Word (e.g. creating a new workbook) and retrieve information from Excel (e.g. reading information from a workbook). |
2000-04-12 |
| About the examples and tools available for download |
General information about the available downloads. |
2000-04-09 |
| Private Profile Strings using INI-files |
Private Profile Strings are often used to store user specific information outside the application/document for later use. |
2000-04-07 |
| Private Profile Strings using Words System.PrivateProfileString |
Use Words object library to read and write Private Profile Strings with INI-files and the Registry. |
2000-04-07 |
| Private Profile Strings using the Registry |
Private Profile Strings are often used to store user specific information outside the application/document for later use. |
2000-04-07 |
| Delete a module |
How to delete a module from a workbook project. |
2000-02-05 |
| Add content to a module from a file |
You can add procedures to an existing module, add the content of a text file to an existing module. |
2000-02-05 |
| Create a new module |
How to create a new macro module in a workbook. |
2000-02-05 |
| Delete a procedure from a module |
How to delete an existing procedure from a module. |
2000-02-05 |
| Add a procedure to a module |
You can add code to a module without using a separate text file that contains the code. |
2000-02-05 |
| Insert a new module from a file |
The easy way to insert new modules with contents in a workbook project. |
2000-02-05 |
| Restore the VBE windows to their default positions |
If you have trouble with the docking of the different VBE windows, you can restore their positions by editing a setting in the Registry. |
2000-02-05 |
| Check if a VBProject is protected |
This function lets you check if a VBProject is protected before you try to edit the project. |
2000-02-05 |
| Delete module content |
You can't delete the codemodules for worksheets, charts and ThisWorkbook. In these modules you have to delete the content instead of the module itself. |
2000-02-05 |
| Turn off warning messages |
How to avoid that the application ask the user for confirmations when a macro is running. |
2000-02-05 |
| Use the built-in dialogs in Excel |
It's not always necessary to invent everything on your own when you can use something that already exists. You have access to most of the built-in dialogs in Excel and the other applications in Office. |
2000-02-05 |
| Return a userdefined cell reference from a dialog |
This example shows how you can let a user enter a cell reference in a custom dialog by selecting cells in a worksheet. |
2000-02-05 |
| Change the value/content of several UserForm-controls |
Example macros that shows how you can change the value/content of several UserForm-controls. |
2000-02-05 |
| Prevent a UserForm from closing when the user clicks the x-button |
Use this macro example to prevent a UserForm from closing when the user clicks the x-button in the upper right corner of the UserForm. |
2000-02-05 |
| Fill a ListBox with unique values from a worksheet |
Example macros that shows how you can fill a ListBox-control (and a ComboBox-control) in a UserForm with the unique values from worksheet range. |
2000-02-05 |
| Use listboxes with multiple choices |
How to determine which items are selected in listboxes with multiple choices. |
2000-02-05 |
| Use messageboxes |
How to use the MsgBox-function to interact with the user. |
2000-02-05 |
| Decide what an INPUTBOX is supposed to return |
Restrict the valid information that a user can type into an InputBox. |
2000-02-05 |
| Pass arguments to macros from buttons and menus |
Create CommandBar buttons/menus that passes one or more arguments to a macro. |
2000-02-05 |
| Change the state of a custom CommandBar button |
Change the state of a custom CommandBar button so it displays as depressed or not. |
2000-02-05 |
| Edit the tooltip for toolbarbuttons (Office95 and earlier) |
Change the tooltip text for both built-in controls and custom buttons. |
2000-02-05 |
| Change the availability for the shortcut menus |
Enable or disable shortcutmenus. |
2000-02-05 |
| Custom menus in Excel97 and later |
In Excel97 and later, menus and toolbarbuttons can be manipulated with the CommandBar-object. |
2000-02-05 |
| Custom menus in Excel5/95 |
With macros it's possible to create your own custom menu. In Excel-versions previous to Excel97 it's also possible to create menus with a built-in menu editor, but this option doesn't exist in Excel97. |
2000-02-05 |
| Display a CommandBar centered on the screen |
Display a CommandBar centered on the screen, both horizontally and vertically. |
2000-02-05 |
| Change the availability for a menu item |
Enable or disable menu items. |
2000-02-05 |
| Edit the tooltip for toolbarbuttons (Office97 and later) |
Change the tooltip text for both built-in controls and custom buttons. |
2000-02-05 |
| Change the availability for the CommandBars |
Toggle the availability of CommandBars such as the Workbook Menu Bar, the Standard toolbar and custom CommandBars. |
2000-02-05 |
| Import data from Access to Excel (DAO) |
Import data from an Access table to an Excel worksheet (DAO). |
2000-02-05 |
| Return every n-th item |
Return every n-th item from a worksheet range. |
2000-02-05 |
| Select between several options |
Use the CHOOSE function to select between up to 29 items. |
2000-02-05 |
| Look up values in your own tables |
Excel has several functions that can return information from your own custom tables in many different ways. Here are some of the most common functions, followed by some examples on how to use them. |
2000-02-05 |
| Look up unique values |
Look up unique values or items from a worksheet range |
2000-02-05 |
| 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. |
2000-02-04 |
| 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. |
2000-02-04 |
| 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. |
2000-02-04 |
| Print the pages in reverse order |
With some printers it can be useful to be able to print the pages of a worksheet in reverse order. |
2000-02-04 |
| List files in a folder with Microsoft Scripting Runtime |
Create a list of files in a folder, include files in sub-folders. |
2000-02-04 |
| Select folder names |
Let the user select a folder name as input for your macros. |
2000-02-04 |
| List files in a folder with Office 97 or later |
In Office 97 or later it's easy to get a list of filenames in a folder, included any files in subfolders. |
2000-02-04 |
| List files in a folder with Office 95 or earlier |
In Office 95 or earlier you don't have the same easy approach to getting a list of filenames as in Office 97 or later. By creating 4 helpfunctions it's possible to get almost the same functionality. |
2000-02-04 |
| Determine if a file is in use |
With this function you can determine if a file already is in use by another user or process. |
2000-02-04 |
| Select file or folder names in Word |
Word has not got the method GetOpenFileName that can be used to let a user select a file name or a folder. You can instead use this user defined function. |
2000-02-04 |
| File names and folder names |
This function can be used to return the file name or the folder name from a full file name. |
2000-02-04 |
| Calculate with time |
General information about time calculations in Excel. |
2000-02-04 |
| Negative times |
Calculate with negative time values. |
2000-02-04 |
| Integer times |
Convert an integer time value to a decimal time value |
2000-02-04 |
| Time intervals |
Calculate with time intervals. |
2000-02-04 |
| Return error values from user defined functions |
Make your user defined functions return error values just like the built in functions in Excel do. |
2000-02-04 |
| Use the logical functions |
General information about the use of the logical worksheet functions in Excel. |
2000-02-04 |
| Use text and numbers in the same cell |
By using text and numbers from several different cells in the same cell you will be able to present your worksheet results in a nice way. |
2000-02-04 |
| Square root and cube root |
How to find the square root or the cube root of a value. |
2000-02-04 |
| Create Roman numbers |
Create Roman numbers with the built-in worksheet function. |
2000-02-04 |
| Add path- and filenames |
Display the filename and path in a workbook in a cell by using a built-in worksheet function. |
2000-02-04 |
| Delete sheets without confirmation prompts |
Delete sheets from a workbook without the user being prompted for any confirmations. |
1999-12-20 |
| Delete rows and columns |
Delete all empty rows or columns within a worksheet range. You can also delete every n-th row or column. |
1999-12-20 |
| Copy column widths and row heights |
Copy column widths and row heights from one range to another. |
1999-12-20 |
| Convert negative values treated as text |
When you import numbers from data sources outside Excel it sometimes happens that the negative values is treated as text if they have the minus sign after the value. With this macro you can convert these negative numbers to a valid negative value Ex |
1999-12-20 |
| Convert between column numbers and column references |
Convert a number between 1 and 256 to a column reference between A and IV, convert a column reference (A - IV) to a column number between 1 and 256. |
1999-12-20 |
| Compare two worksheet ranges |
Compare the content of two worksheet ranges. The result is displayed in a new workbook listing all cell differences. |
1999-12-20 |
| Determine if a cell is within a range |
This function can determine if a cell is within a specific range. |
1999-12-20 |
| Set row height and column width in millimeters |
You can use these macros to set the row heights and column widths in millimeters instead of using points and average character count. |
1999-12-20 |
| Determine if a sheet exists in a workbook |
Check if a sheet exists in a workbook. |
1999-12-20 |
| Copy worksheet information to Word |
Copy information from a worksheet to a Word document. |
1999-12-20 |
| Save a workbook backup |
Save a backup of the active workbook as a *.bkp file or to a floppy disk. |
1999-12-20 |
| Determine if a workbook exists |
Determine if a workbook or another file exists. |
1999-12-20 |
| Change the worksheet codemodule names |
Useful macro for changing the worksheet codemodule names in the VBProject. |
1999-12-20 |
| Determine if a workbook is already open |
Useful function to check if you alread have opened a workbook. |
1999-12-20 |
| Close a workbook |
Close a workbook with or without confirmation prompts. |
1999-12-20 |
| Sort the worksheets in a workbook |
Sort the worksheets (and other sheet types) in a workbook. |
1999-12-20 |
| Create new workbook |
Create new workbooks with up to 255 worksheets. |
1999-12-20 |
| Using Microsoft Scripting Runtime |
It is possible to use the Microsoft Scripting Runtime library to manipulate text files. |
1999-12-17 |
| Using binary file access |
With binary access it's possible to store information in any way you want, you are not limitied to a fixed record length. This means that you have to know how the information is stored in a binary file to retrieve it again. |
1999-12-17 |
| Using random access |
A random access file is assumed to contain a series of records of equal length. This makes it easy and quick to locate stored information. Random access files can use less diskspace compared to sequential files. |
1999-12-17 |
| Replace text in a text file |
This example can be used to replace text in a text file, e.g. when you want to change a column separator in a text file before you import it into an Excel worksheet or after you export a worksheet to a text file. |
1999-12-17 |
| File access with VBA |
VBA provides functions for performing file input/output (I/O). This lets your custom solutions create, edit and store large amounts of data. |
1999-12-17 |
| Using sequential access |
Sequential access is used for writing and reading text files, such as error logs and reports, e.g. *.txt files, *.ini files and *.csv files. Sequential access files are easy to create and manipulate with text editors, most applications can read an |
1999-12-17 |
| Import from a workbook/worksheet |
This macro imports data from a workbook/worksheet to a worksheet range. |
1999-10-13 |
| Import from a fixed-width textfile |
This macro imports data from a fixed-width textfile to a worksheet range. |
1999-10-13 |
| Export to a HTML-file |
This macro exports values or formulas from a worksheet range to a HTML-file. |
1999-10-13 |
| Export to a fixed-width textfile |
This macro exports the values or formulas from a worksheet range to a fixed-width textfile. |
1999-10-13 |
| Export to a delimited textfiles |
This macro exports the values or formulas from a worksheet range to a delimited textfile. |
1999-10-13 |
| Start a macro when an event occurs |
It's possible to run macros automatically when different events in Excel takes place. |
1999-08-21 |
| Chart events |
Events for the chart object occurs when the user activates or changes a chart. Events in charts are activated by default, but can be deactivated by a macro. |
1999-08-21 |
| Workbook events |
Events for the Workbook object occurs when the workbook is changed or a sheet in the workbook is changed. Events in workbooks are activated by default, but can be deactivated by a macro. |
1999-08-21 |
| Application events |
Events for the Application object occurs when a workbook is created, opened or when a sheet in any workbook is changed. Event procedures for the Application-object have to be created with a class module. |
1999-08-21 |
| Chart object events |
If you want to write event procedures for an embedded chart, you will have to create a class module to do this. |
1999-08-21 |
| Prevent that an automacro/eventmacro executes |
Prevent auto-macros and event-macros from running. |
1999-08-21 |
| Worksheet events |
Events for the worksheet object occurs when a worksheet is activated or the user changes the content of a cell. Events in worksheets are activated by default, but can be deactivated by a macro. |
1999-08-21 |
| Determine the type of operating system |
This function will return True if the operating system in use is 32 bit. |
1999-08-16 |
| Function for returning the user name |
Return the user name for the logged on user, works in both Windows NT and Windows95/98. |
1999-08-16 |
| Function for returning the computer name |
Return the computer name for the machine running the code, works in both Windows NT and Windows95/98. |
1999-08-16 |
| Array functions |
General information about array functions. |
1999-08-14 |
| Count cells in a range from all worksheets |
Count cells in a range from all worksheets in the workbook |
1999-08-14 |
| Functions for counting |
General information about counting in a worksheet. |
1999-08-14 |
| Count unique values |
Count unique values in a range |
1999-08-14 |
| Count by color |
Count cells depending on the cell background color. |
1999-08-14 |
| Array functions |
General information about array functions. |
1999-08-14 |
| Add unique values |
A custom function that adds the unique values only from a given range |
1999-08-14 |
| Sum function examples |
A collection of sum function examples |
1999-08-14 |
| Conditional sum |
Add values that meets a given condition. |
1999-08-14 |
| Add cells in a range from all worksheets in the workbook |
A custom function for adding values from all worksheets in a workbook. |
1999-08-14 |
| Subtotals |
Add values from a range containing several subtotals. |
1999-08-14 |
| Add values from the previous or next worksheet |
Custom functions for adding values from the previous or next worksheet, add values from any worksheet in the workbook. |
1999-08-14 |
| Playing MIDI-files |
Play MIDI-files from your VBA macros. |
1999-08-13 |
| Playing WAV-files |
Play WAV-files from your VBA macro. |
1999-08-13 |
| Playing sound notes |
Attach sound notes to cell comments, even in Excel97 and later where sound notes no longer are supported by default. |
1999-08-13 |
| Determine the screen size |
Determine the screen height and width with this macro example. |
1999-07-23 |
| Control the screen updating |
By turning off the screen updating your macros will run much faster. It will also prevent the disturbing flickering of screen pictures. |
1999-07-23 |
| Restore the position in the window |
Useful macros for restoring the screen picture for the user after a macro has changed the window view. |
1999-07-23 |
| How secure is the addin source code? |
You can protect your work by creating addins, but how secure are the result? |
1999-04-15 |
| Create an addin in Excel97 |
How to create an addin for Excel97 and later. |
1999-04-15 |
| Create an addin in Excel5/95 |
How to create an addin for Excel5/95. |
1999-04-15 |
| COM addins (Office Developer Edition) |
General information about COM addins and how to create them. |
1999-02-18 |
| Database functions |
General information about the database functions in Excel. |
1998-11-25 |
| Class modules |
General information about class modules. |
1998-07-07 |
| General information about the VBA tips |
General information about the VBA tips on this web site. |
1998-03-07 |
