Time intervals

If you need to calculate the difference between two time values within a certain limit (e.g. when calculating elapsed work time), the worksheet function below can be useful since it makes it simple to perform the time interval calculation.

Function TimeInterval(StartTime As Double, EndTime As Double, _

LowerLimit As Double, UpperLimit As Double) As Double

' returns EndTime-StartTime limited by LowerLimit and UpperLimit

TimeInterval = 0

If StartTime > EndTime Then Exit Function

If StartTime > UpperLimit Then Exit Function

If EndTime < LowerLimit Then Exit Function

If StartTime < LowerLimit Then StartTime = LowerLimit

If EndTime > UpperLimit Then EndTime = UpperLimit

TimeInterval = EndTime - StartTime

End Function

Create an addin in Excel97

Addins in Excel97 are saved differently from the previous versions of Excel:



Start the Visual Basic Editor by pressing Alt+F11.

Select Debug, Compile VBA projectname. In previous versions of Excel the VBA-code was automatically compiled when you saved the addin. This is no longer true, you have to do it yourself before you save the addin.

If you want to lock the project from viewing you can select Tools, Properties for VBA projectname. In this dialog you activate Protection and check the option Lock project for viewing. Fill in a password and click the OK-button.

Activate Excel by pressing Alt+F11.

Select File, Properties..., Summary and fill inn information for the fields Title and Comments. The title will be the name that appears in the Add-Ins dialog (the dialog displaying available add-ins), the comment will be the description that appears when you select the addin in the Add-Ins dialog. Click the OK-button to close the Properties dialog.

Select File, Save as….

Change the option Filetype: to Microsoft Excel addin (*.xla) (the last choice in the dropdown). The add-in must contain at least one worksheet if this option is to be displayed.

Click the Save-button to save the workbook as an addin.

The locking of the project will not take effect until you close and re-open the workbook.

You can convert a workbook to an addin by changing the property IsAddin to True for the ThisWorkbook-object. This must be done from the Visual Basic Editor. When the property is changed you can save the workbook by clicking on the Save-toolbarbutton.

What can a COM Add-In do?

All objectmodels are available for you as a developer.

Supports events, included application level events, and can perform a task according to these events.

You can add buttons to a CommandBar and display different user interfaces.

The position and size of the Office applications main windows can be controlled.

Any Office application can be controlled from another Office application.

You can control other applications that have an objectmodel that is accessible for you as a developer, included other 3rd party applications that licenses VBA version 6.

COM addins?

Earlier versions of Office had 9 different add-in models. All of these models had different attributes for startup, shutdownd, registering, behaviour, language and communication between applications. This is now history, a COM Add-In has full access to the objectmodels in Office.

What is an addin?

An addin is a workbook with custom commands and functions that can be used with Excel. Addin's are used to distribute macros, userdefined functions or custom solutions to other users. If the addin contains VBA code that refers to the workbook running the code you have to use the object ThisWorkbook instead of ActiveWorkbook. An addin will never be the active workbook.

When you save a workbook as an addin, the content is "compiled" and protected so that the source code is (usually) not visible and editable by other people. You can find tools on the Internet that can "crack" this protection from addin's made for Excel 5/95 and Excel97, but ordinary users will usually not be able to see or edit the contents of an addin.

When you create an addin in Excel5/95 you have to save your original workbook if you want to be able to edit the contents of your addin. Excel5/95 can not open and edit the addin, you will have to edit the original workbook and create a new addin every time you need to make a change to the addin.

Excel97 can open and edit addins, so it's not necessary to keep a copy of the original workbook you created the addin from.

It's also possible to password protect the contents of the VBA project in the workbook so that the source code is (usually) not visible and editable by other people.

Addin's can be installed by opening the file as you open an ordinary workbook. You can also install the addin by saving it to the Library folder and installing it from the Addin Manager with the menuchoice Tools, Addins.... When you install an addin the custom functions are available to the user, and any userdefined menus in the addin will also be added to the existing menus in Excel.

Addin's installed by the Addin Manager can be uninstalled by removing the checkmark in the list of installed addin's in the Addin Manager.

Addin's can also be installed and uninstalled by using VBA-code.