Dutch Gemini's Weblog Pages

January 4, 2012

Install (and Uninstall) an Excel Add-In with InnoSetup

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 9:00 am
Tags: , , , ,

I have written a script for InnoSetup 5.3, now updated to 5.4.2 (and soon to 5.4.3 made available during the 2011 Xmas holidays), that allows me to install and uninstall an Excel Add-In (XLA or XLAM). Before InnoSetup I was using a VBS script to perform this same task.

This InnoSetup script removes old copies, updates Excel, activates or disables the Add-In, clears the Registry, and more. It also checks and optionally allows setting the “Trust Access To Visual Basic Project” flag under the Macro Security settings (in the Trust Center if on Excel 2007 and 2010).

The nice thing is that now I also have an entry in the Control Panel with which I can uninstall the Add-In (gives a much more professional look).

I have dissected the script I use and have commented the various parts. On the last page I have put the biggest part. If you assemble them you end up with quite a big script , somewhere around 1700 lines of Pascal Script (roughly 1500 lines only for the Code section), considering that only a few files are installed but the script takes care of a large number of issues. In addition, over the years I developed a tendency to comment each instruction carefully, helps when you need to review the code after long periods of times doing something really else.

Here’s the full story. I’ve divided it in pages for easier reading (all links should work now):

Page 1 | Page 2 | Page 3 | Page 4 | Page 5

Small update pending: I have made some changes to the script parts for dealing better with 64-bit versions of Windows and Excel. Expect these changes to be posted within a few days. Thanks for waiting.

Dutch.

January 2, 2012

Install (and Uninstall) an Excel Add-In with InnoSetup – Page 5

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 4:08 pm
Tags: , , , ,

The Excel Script for the Add-In

This last page describes the part of the “generic” script that takes care of the installation, update and removal of the Add-In on the target PC. It is also the longest page.

Starting here is indeed the Code section that was not described earlier. This section contain a header with the variable declarations and the constants, the Inno event routines (list 1) and my own custom functions and procedures (list 2). The only ones not put on this page are the support routines which are exposed also on Page 2.

As indicated on Page 2, I have used the following event routines for my customisation:

This is the list of User Defined Routines (UDR) that I have created:

Code Header
[Code]
var
	ActivateProgressPage: TOutputProgressWizardPage;
	DeActivateProgressPage: TOutputProgressWizardPage;
	UninstallProgressForm: TUninstallProgressForm;
	ForMeForAllPage: TInputOptionWizardPage;
	SamplesPathPage: TInputDirWizardPage;
	HTMLHelpPathPage: TInputDirWizardPage;
	LexiconPathPage: TInputDirWizardPage;
	SamplesPath: String;
	HTMLHelpPath: String;
	LexiconPath: String;
	WantsAddIn: Boolean;
	WantsSamples: Boolean;
	WantsHTMLHelp: Boolean;
	WantsLexicon: Boolean;
	InstallMode: Integer;
	ExcelApp: Variant;
	ExcelLibraryPath: String;
	ExcelUserLibraryPath: String;
	ExcelAddInInstalledFailure: Boolean;	// Used for tracing whether Excel was unable to turn
					// the Add-In's '.Installed' property 'True'.
					// This seems to happen installing XLA's on Excel 2007.
					// In that case we try via the Registry.
const
	// These constants are from the XLA and are not known to the install script
	//
	XLA_ADDIN_NAME   = '{#TheAppAddInName}';
	XLAM_FILE_NAME   = '{#TheAppRoot}.XLAM';  // For Excel 2007+
	XLA_FILE_NAME    = '{#TheAppRoot}.XLA';   // For any version of Excel, but in this setup for Excel 2003-
	XLA_HELP_NAME    = '{#TheAppRoot}.CHM';
	XLA_LEXICON_NAME = '{#TheAppRoot}.LEX';
	XLA_FRIENDLYNAME = '{#TheAppShortName}';
InitializeSetup

This routine runs very early so we use it to detect whether Excel is in 32- or in 64-bit (important to know if your Add-In uses components that are not 64-bit, for instance Microsoft Common Controls). It can also used to detect compatibility with third-party applications that the Add-In interfaces to. Failure to comply will abort the installation.

For update installs, the function loads the previous install choices into the global variables.

function InitializeSetup(): Boolean;
(*
	Called during Setup's initialization. Return False to abort Setup, True otherwise.
*)
begin
	// Default.
	//
	Result := true;
	// The Add-In does not (yet) work with 64-bit Excel. We warn and Exit immediately.
	//
	If IsExcel64Bit() then begin
		MsgBox('Oops! Unsupported release of Microsoft Excel (64-bit).' + #13#10 + #13#10 +
			'The {#TheAppName} Add-In is designed to work Microsoft Excel 32-bit only and will' + GetMsgBoxCrLfCharacter +
			'therefore not install. Run this Setup again on a PC with Microsoft Excel 32-bit.' + #13#10 + #13#10 +
			'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
		Result := false;
		Exit;
	end;
	// Load previous choices
	//
	InstallMode := StrToIntDef(GetPreviousData('InstallMode', '0'), 0);
	SamplesPath := GetPreviousData('SamplesPath', GetExpandedConstant('{userdocs}', '{commondocs}', '') + '\My {#TheAppVeryShortName} Projects\Samples');
	HTMLHelpPath := GetPreviousData('HTMLHelpPath', GetExpandedConstant('{sd}', '', '') + '\{#TheAppVeryShortName} HTML Help');
	LexiconPath := GetPreviousData('LexiconPath', GetExpandedConstant('{userdocs}', '{commondocs}', '') + '\My {#TheAppVeryShortName} Lexicons');
end;
InitializeWizard

All custom dialogs are created here, like the “Only for me” and “All Users” selection, the dialogs for selecting a folder for the help and samples and for the Add-In installation progress.

The current script features the «this user or all users» input option page. This makes sense if your application really allows to install per user, but if you plan to bring along also files that are installed in folders accessible to other users –for instance a shared DLL– then you should consider installing for all users only.

procedure InitializeWizard;
begin
	// Create the "For Me/For everyone" page
	//
	ForMeForAllPage := CreateInputOptionPage(wpSelectComponents,
		'Add-In Installation Mode', 'How would you like to install the Add-In?',
		'Please indicate how the Add-In should be installed into Excel, then click Next.',
		true, false);
	// Add item (with an empty caption)
	//
	ForMeForAllPage.Add('Only for me (' + GetUserNameString + ')');
	ForMeForAllPage.Add('For all users of this computer');
	// Set initial value. InstallMode = [0|1]
	//
	ForMeForAllPage.Values[InstallMode] := true;
	// Create the Samples page
	//
	SamplesPathPage := CreateInputDirPage(ForMeForAllPage.ID,
		'Select Samples Data Location', 'Where should the sample data files be stored?',
		'Sample data files will be stored in the following folder.' + #13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'Samples Folder');
	// Add item (with an empty caption)
	//
	SamplesPathPage.Add('');
	// Set initial value (optional)
	//
	SamplesPathPage.Values[0] := RemoveBackslash(SamplesPath);
	// Do the same for the HTML Help page
	//
	HTMLHelpPathPage := CreateInputDirPage(SamplesPathPage.ID,
		'Select HTML Help Location', 'Where should the HTML Help data files be stored?',
		'HTML Help data files will be stored in the following folder.' + #13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'HTML Help Folder');
	HTMLHelpPathPage.Add('');
	HTMLHelpPathPage.Values[0] := RemoveBackslash(HTMLHelpPath);
	// And the Lexicon page
	//
	LexiconPathPage := CreateInputDirPage(HTMLHelpPathPage.ID,
		'Select Lexicon Data Location', 'Where should the lexicon data files be stored?',
		'Lexicon data files will be stored in the following folder.' +#13#10 + #13#10 +
		'To continue, click Next. If you would like to select a different folder, click Browse.',
		false, 'Lexicon Folder');
	LexiconPathPage.Add('');
	LexiconPathPage.Values[0] := RemoveBackslash(LexiconPath);
	// Create progress pages.
	//
	ActivateProgressPage := CreateOutputProgressPage('Activating Add-In',
		'Activating the {#TheAppShortName} Add-In in Microsoft Excel.');
	DeActivateProgressPage := CreateOutputProgressPage('DeActivating Add-In',
		'DeActivating the {#TheAppShortName} Add-In in Microsoft Excel.');
end;
CurStepChanged

Used as a header for calling sub-routines dealing with Pre-Install, Post-Install and Success-Install (all custom routines).

procedure CurStepChanged(CurStep: TSetupStep);
begin
	case CurStep of
	ssInstall:
		DoPreInstall();
	ssPostInstall:
		DoPostInstall();
	ssDone:
		DoSuccessInstall();
	end;
end;
NextButtonClick

This function is used to mark the selected components and will trap whether Excel is running. The script will not allow going beyong the ‘Ready to install’ page if Excel is running.

Also used to memorise the values of the custom dialogs (see also above).

function NextButtonClick(CurPageID: Integer): Boolean;
(*
	Called when the user clicks the Next button. If you return True, the wizard will move to the next page.
	If you return False, it will remain on the current page (specified by CurPageID).
*)
var
	MsgBoxResult: Integer;
begin
	// In principle always continue
	//
	Result := true;
	case CurPageID of
	wpSelectComponents:
		begin
			WantsAddIn    := IsComponentSelected('full\addin');
			WantsSamples  := IsComponentSelected('full\samples');
			WantsHTMLHelp := IsComponentSelected('full\htmlhelp');
			WantsLexicon  := IsComponentSelected('full\lexicon');
		end;
	wpReady:
		begin
			// We are about to start the installation of our components.
			// The installation cannot continue if Excel is running.
			// Return 'False' i.e. 'stop' when Excel is running and we aborted.
			//
			While IsExcelRunning(mbInformation, MB_RETRYCANCEL, IDCANCEL, MsgBoxResult) do begin
				if (MsgBoxResult = IDCANCEL) then begin
					Result := false;
					Exit;
				end;
			end;
		end;
	ForMeForAllPage.ID:
		InstallMode := ForMeForAllPage.SelectedValueIndex;
	SamplesPathPage.ID:
		SamplesPath := SamplesPathPage.Values[0];
	HTMLHelpPathPage.ID:
		HTMLHelpPath := HTMLHelpPathPage.Values[0] ;
	LexiconPathPage.ID:
		LexiconPath := LexiconPathPage.Values[0] ;
	end;
end;
ShouldSkipPage

Detects whether or not to show certain custom or standard dialogs. For instance, if the user does not want to install samples, the page asking for the samples destination folder will be skipped.

function ShouldSkipPage(PageID: Integer): Boolean;
begin
	// In principle always continue (i.e. never skip).
	//
	Result := false;
	case PageID of
	ForMeForAllPage.ID:
		Result := Not WantsAddIn;
	SamplesPathPage.ID:
		Result := Not WantsSamples;
	HTMLHelpPathPage.ID:
		Result := Not WantsHTMLHelp;
	LexiconPathPage.ID:
		Result := Not WantsLexicon;
	end;
end;
UpdateReadyMemo

Prepares the report of what the installer is about to do, including settings from our custom dialogs.

function UpdateReadyMemo(Space, NewLine, MemoUserInfoInfo, MemoDirInfo, MemoTypeInfo, MemoComponentsInfo, MemoGroupInfo, MemoTasksInfo: String): String;
var
	MemoString: String;
begin
	// Standard stuff.
	//
	if (MemoUserInfoInfo <> '')   then MemoString := MemoUserInfoInfo;
	if (MemoDirInfo <> '')        then MemoString := MemoString + NewLine + NewLine + MemoDirInfo;
	if (MemoTypeInfo <> '')       then MemoString := MemoString + NewLine + NewLine + MemoTypeInfo;
	if (MemoComponentsInfo <> '') then MemoString := MemoString + NewLine + NewLine + MemoComponentsInfo;
	if (MemoGroupInfo <> '')      then MemoString := MemoString + NewLine + NewLine + MemoGroupInfo;
	if (MemoTasksInfo <> '')      then MemoString := MemoString + NewLine + NewLine + MemoTasksInfo;
	// Specific stuff.
	//
	if WantsAddIn then begin
		if (InstallMode = 0) then begin
			MemoString := MemoString + NewLine + NewLine + 'Add-In installation mode:' + NewLine + Space + 'Only for me (' + GetUserNameString + ')';
		end else begin
			MemoString := MemoString + NewLine + NewLine + 'Add-In installation mode:' + NewLine + Space + 'For all users of this computer';
		end;
	end;
	if WantsSamples  then MemoString := MemoString + NewLine + NewLine + 'Samples folder:' + NewLine + Space + SamplesPath;
	if WantsHTMLHelp then MemoString := MemoString + NewLine + NewLine + 'HTML Help folder:' + NewLine + Space + HTMLHelpPath;
	if WantsLexicon  then MemoString := MemoString + NewLine + NewLine + 'Lexicon folder:' + NewLine + Space + LexiconPath;
	Result := MemoString;
end;
RegisterPreviousData

Stores the user’s selection of the custom dialogs.

procedure RegisterPreviousData(PreviousDataKey: Integer);
begin
	// Store the settings so we can restore them next time
	//
	SetPreviousData(PreviousDataKey, 'InstallMode', IntToStr(InstallMode));
	SetPreviousData(PreviousDataKey, 'SamplesPath',  SamplesPath);
	SetPreviousData(PreviousDataKey, 'HTMLHelpPath', HTMLHelpPath);
	SetPreviousData(PreviousDataKey, 'LexiconPath',  LexiconPath);
end;
InitializeUninstall

This routine runs very early in the unistallation process. Here only used to trap whether Excel is running or not and abort the uninstallation if the user decides to keep Excel alive.

function InitializeUninstall(): Boolean;
(*
	Called during Uninstall. Return false to abort Setup, True otherwise.
*)
var
	MsgBoxResult: Integer;
begin
	// Default.
	//
	Result := true;
	// We are about to start the uninstallation of our components.
	// The uninstallation cannot continue if Excel is running.
	// Return 'False' i.e. 'stop' when Excel is running and we abort.
	//
	While IsExcelRunning(mbInformation, MB_RETRYCANCEL, IDCANCEL, MsgBoxResult) do begin
		if (MsgBoxResult = IDCANCEL) then begin
			Result := false;
			Exit;
		end;
	end;
end;
InitializeUninstallProgressForm

This particular event routine is used to create a “custom” uninstall progress form, as InnoSetup does not provide access to normal custom progress forms which are available only during installation.

procedure InitializeUninstallProgressForm;
begin
	// We are about to uninstall. To be able to use the UninstallProgressForm during our Uninstall run we need to
	// instantiate it although according to InnoSetup Help (and some Internet sources) it should already exist.
	//
	UninstallProgressForm := TUninstallProgressForm.Create(NIL);
	UninstallProgressForm.Caption := '{#TheAppName} Uninstall';
end;
CurUninstallStepChanged

Like CurStepChanged, used as a header for calling sub-routines dealing with Pre-UnInstall and Post-UnInstall (all custom routines).

procedure CurUninstallStepChanged(CurUninstallStep: TUninstallStep);
begin
	case CurUninstallStep of
	usUninstall:
		DoPreUnInstall();
	usPostUninstall:
		DoPostUnInstall();
	end;
end;
GetSamplesPath

Returns the name of the folder for the samples, properly formatted.

function GetSamplesPath(S: String): String;
(*
	Return the selected 'Samples' directory
*)
begin
	Result := AddBackslash(SamplesPath);
end;
GetHTMLHelpPath

Returns the name of the folder for the HTML Help files, properly formatted.

function GetHTMLHelpPath(S: String): String;
(*
	Return the selected HTML Help folder
*)
begin
	Result := AddBackslash(HTMLHelpPath);
end;
GetLexiconPath

Returns the name of the folder for the Lexicons, properly formatted. In my projects a Lexicon is an ASCII text file containing localised versions of the strings and captions used by the Add-In, which is designed to be language independent. In this way I can easily translate the Add-In.

function GetLexiconPath(S: String): String;
(*
	Return the selected Lexicon folder
*)
begin
	Result := AddBackslash(LexiconPath);
end;
CreateExcelApp

This routine actually starts Excel as an automation server. When it succeeds, it initialises the global variables (declared in the Code Header) containing the folder names where the Add-In can be copied to. Returns ‘true’ when Excel can be initialised as automation server.

function CreateExcelApp: Boolean;
(*
	Create an Excel COM Automation object.
	This COM object is needed for accessing the Add-Ins components of Excel.
*)
begin
	// Default.
	//
	Result := true;

	// Create an Excel COM Automation object.
	//
	try
		ExcelApp := CreateOleObject('Excel.Application');
	except
		if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('Could not initialise Excel.Application.');
	end;

	if VarIsEmpty(ExcelApp) then begin
		if IsRunningSilent then begin
			Log('Could not start Microsoft Excel. Contact a System Administrator.');
		end else begin
			MsgBox('Oops! Could not activate Microsoft Excel.' + #13#10 + #13#10 +
				'The {#TheAppName} is a Microsoft Excel Add-In and requires'+ GetMsgBoxCrLfCharacter +
				'Microsoft Excel. This procedure could not find Microsoft Excel. The Add-In'+ GetMsgBoxCrLfCharacter +
				'will therefore not install.' + #13#10 + #13#10 +
				'If this notification is unexpected, contact a System Administrator.', mbInformation, mb_Ok);
		end;
		Result := false;
	end else begin
		// Excel started. On systems with dual monitors, Excel sometimes starts with a visible frame so hide.
		//
		ExcelApp.Visible := false;

		// Load the default Add-Ins folder (this is a Global Variable).
		//
		ExcelLibraryPath := ExcelApp.LibraryPath;
		ExcelUserLibraryPath := ExcelApp.UserLibraryPath;

		// According to M$, we must add an empty workbook in order to access the Add-In Manager using OLE Automation.
		// Excel start without a workbook in this situation. The workbook will be dropped anyway (see below). With
		// Excel 2007+ you can access the Add-Ins Manager also without an open workbook, but who cares...
		//
		try
			ExcelApp.Workbooks.Add
		except
		end;
	end;
end;
KillExcelApp

This routine stops Excel as an automation server. Does not provide feedback in case of trouble.

procedure KillExcelApp();
(*
	Shut down the Excel COM Automation object.
	This COM object was needed for accessing the Add-Ins components of Excel.
*)
var
	ExceptionRaised: Boolean;
begin
	if not VarIsEmpty(ExcelApp) then begin
		// All statements needed by Excel to close down without leaving garbage.
		// Suppress whatever warning Excel may cough up.
		//
		ExcelApp.DisplayAlerts := false;

		// Loop all open workbooks. If for whatever reason we run into
		// trouble, we use the ExceptionRaised to stop closing workbooks.
		//
		ExceptionRaised := false;
		while (ExcelApp.Workbooks.Count > 0) and not ExceptionRaised do begin
			// Close the workbook. Turn the workbook 'clean' by
			// setting the '.Saved' property to 'true'.
			//
			try
				ExcelApp.Workbooks(1).Saved := true;
			except
			end;

			// Now close the workbook. On Excel 2000 (others not verified) the '.Close'
			// statement may fail as you are not allowed to close the last open workbook.
			//
			try
				ExcelApp.Workbooks(1).Close;
			except
				ExceptionRaised := true;
			end;
		end;

		// Kill Excel.
		//
		try
			ExcelApp.Quit;
		except
		end;
	end;

	// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
	//
	ExcelApp := Unassigned;
end;
HookAddinToExcel

This function hooks the Add-In into Excel. To make the activation visually more appealing we use a custom progress page with 4 steps:

  1. start of Excel
  2. loading of the Add-In in the Add-In space
  3. activation of the Add-In
  4. stopping of Excel

A fifth position is used for obtaining the 100% progress bar. You can uncomment the Sleep(200) statement if you want to add a small delay between the various steps and give the impression that each step is actually taking some CPU (otherwise the dialog may just flash by).

On Windows Vista, and this is even more visible on Windows 7, the progress bar on the custom page ‘runs behind’ the actual progress position, something which does not occur with progress bar on Inno’s standard pages (or on XP). The effect is that the various steps are properly displayed but the progress bar remains visually stuck at 0%. The progress bar does not have a ‘refresh’ method to force a refresh via code before continuing.

 I tried to use the Sleep(200) statement with various delays to avoid this effect but without success. Apparently the control used to paint the progress bar runs in a separate thread and Windows is too busy adding fancy effects that it can’t even finish painting the bar before the Inno code kills the dialog.

The function returns a String containing the full name of the Add-In.

function HookAddinToExcel(): String;
(*
	Hooks the Add-In to Excel. Hooking the Add-In means loading the Add-In
	in Excel's Add-Ins space and setting the '.Installed' property to 'True'.
*)
var
	ExcelAddIn: Variant;
	OpenCounter: Integer;
	RegistryKey: String;
	RegistryValue: String;
	RegistryData: String;
begin
	// The return value is the full name of the Add-In.
	//
	Result := '';

	try
		begin
			Log('Starting Microsoft Excel COM Automation.');

			if IsInstaller then begin
				ActivateProgressPage.Show;
				ActivateProgressPage.SetText('Starting Microsoft Excel COM Automation to enable Add-In...', '');
				ActivateProgressPage.SetProgress(0,4);
				// Sleep(200);
			end;

			// Get, or create which is much more likely, an Excel COM Automation object
			//
			if CreateExcelApp then begin
				Log('Loading the Add-In to enable.');

				if IsInstaller then begin
					ActivateProgressPage.SetText('Loading the Add-In...', '');
					ActivateProgressPage.SetProgress(1,4);
					// Sleep(200);
				end;

				// Get the Add-In. If it's not there, add. The file was already copied to the correct folder. Excel seems
				// to do that anyway when the drive where the source file is located is a network/removable drive.
				//
				try
					ExcelAddIn := ExcelApp.AddIns(XLA_ADDIN_NAME);
				except
					if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('No matching Add-In found, adding Add-In.');
					ExcelAddIn := ExcelApp.AddIns.Add(XLA_FILE_NAME, true);
				end;

				if VarIsEmpty(ExcelAddIn) then begin
					if IsRunningSilent then begin
						Log('Could not enable Add-In. Add-In not loaded. If this error is unexpected, contact a System Administrator.');
					end else begin
						MsgBox('Error: Could not enable Add-In. Add-In not loaded.' + #13#10 + #13#10 +
							'If this error is unexpected, contact a System Administrator.', mbError, mb_Ok);
					end;
				end else begin
					Log('Enabling the Add-In.');

					if IsInstaller then begin
						ActivateProgressPage.SetText('Enabling the Add-In...', '');
						ActivateProgressPage.SetProgress(2,4);
						// Sleep(200);
					end;

					// Propagate the name of the Add-In to the Caller.
					//
					Result := ExcelAddIn.FullName;

					// Try to enable the Add-In via Excel.
					//
					ExcelAddInInstalledFailure := false;
					try
						ExcelAddIn.Installed := true;
					except
						// Here we are, failure loading the Add-In.
						//
						ExcelAddInInstalledFailure := true;
					end;

					// On some combinations of OS & Excel (especially on Vista and Windows 7), working
					// with the '.Installed' property makes Excel re-appear again which is rather annoying.
					//
					ExcelApp.Visible := false;

					if ExcelAddInInstalledFailure then begin
						// We had a failure installing the Add-In via Excel.
						//
						if IsRunningSilent then begin
							Log('Could not enable Add-In. Now enabling via Registry. If this error is unexpected, contact a System Administrator.');
						end;

						// We now try to do this via the Registry, but must be sure that Excel is not running.
						// first we read the name of the Add-In. 'InstallMode = 0' means 'For me only'. Only the
						// root name of the Add-In if installed as 'For me only' and the full path if installed
						// as 'For all users'. Must be enclosed in double quotes, otherwise Excel doesn't like it.
						//
						if (InstallMode = 0) then begin
							RegistryData := '"' + ExcelAddIn.Name + '"';
						end else begin
							RegistryData := '"' + ExcelAddIn.FullName + '"';
						end;

						// Close Excel. Must do so otherwise the next settings are voided.
						//
						KillExcelApp;

						// To install an Add-In via the Registry, write a REG_SZ value called "OPENx"
						// to the "HKCU\Software\Microsoft\Office\\Excel\Options" key.
						//
						RegistryKey := 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Options';
						OpenCounter := 0;
						while (OpenCounter >= 0) do begin
							// The "OPENx" value is "OPEN" for the first Add-In
							// and "OPEN1", "OPEN2", "OPEN..." for the next ones.
							//
							if (OpenCounter = 0) then begin
								RegistryValue := 'OPEN';
							end else begin
								RegistryValue := 'OPEN' + IntToStr(OpenCounter);
							end;

							// If the value exists then it pertains to another Add-In.
							// If missing, we add the value because this is our baby.
							//
							if not RegValueExists(HKEY_CURRENT_USER, RegistryKey, RegistryValue) then begin
								Log('Writing Registry entry: ' + RegistryKey + ', ' + RegistryValue + ', ' + RegistryData);

								RegWriteStringValue(HKEY_CURRENT_USER, RegistryKey, RegistryValue, RegistryData);

								// Stop here.
								//
								OpenCounter := -1;
							end else begin
								// Check next.
								//
								OpenCounter := OpenCounter + 1;
							end;
						end;
					end;
				end;
			end;

			// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
			//
			Log('Stopping Microsoft Excel COM Automation after enabling Add-In.');

			if IsInstaller then begin
				ActivateProgressPage.SetText('Stopping Microsoft Excel COM Automation...', '');
				ActivateProgressPage.SetProgress(3,4);
				// Sleep(200);
			end;

			KillExcelApp;

			if IsInstaller then begin
				ActivateProgressPage.SetProgress(4,4);
				// Sleep(500);
			end;
		end;
	finally
		ActivateProgressPage.Hide;
	end;

	// Don't need this instance anymore.
	//
	ExcelAddIn := Unassigned;
end;
UnhookAddinFromExcel

This function unhooks the Add-In from Excel. To make the deactivation visually more appealing we use a progress page with 4 steps:

  1. start of Excel
  2. connecting to the Add-In in the Add-In space
  3. deactivation of the Add-In
  4. stopping of Excel.

A fifth position is used for obtaining the 100% progress bar. See HookAddinToExcel for additional information on the UI issues.

During uninstallation we cannot use custom progress pages as they cannot be created (the atoms are not available in the uninstall stub). That why we use the internal uninstall progress form and code accordingly since it has a slightly different interface.

The function returns a String containing the full name of the Add-In which can be used later on to physically remove the file from the hard disk.

function UnhookAddinFromExcel(): String;
(*
	UnHooks the Add-In from Excel. UnHooking the Add-In means setting the '.Installed'
	property to 'False'. No need to remove the Add-In from Excel's Add-Ins space.
*)
var
	ExcelAddIn: Variant;
begin
	// The return value is the full name of the Add-In.
	//
	Result := '';

	try
		begin
			Log('Starting Microsoft Excel COM Automation to disable Add-In.');

			if IsInstaller then begin
				// Setup (i.e. install) can use custom forms because these can be [and are] initialised.
				//
				DeActivateProgressPage.Show;
				DeActivateProgressPage.SetText('Starting Microsoft Excel COM Automation...', '');
				DeActivateProgressPage.SetProgress(0,4);
			end else begin
				// Uninstall cannot use custom forms because they cannot be initialised. For this we shall be
				// able to use the native UninstallProgressForm of type TUninstallProgressForm.
				//
				UninstallProgressForm.Show
				UninstallProgressForm.PageNameLabel.Caption := 'DeActivating Add-In';
				UninstallProgressForm.PageDescriptionLabel.Caption := 'DeActivating the {#TheAppShortName} Add-In in Microsoft Excel.';
				UninstallProgressForm.StatusLabel.Caption := 'Starting Microsoft Excel COM Automation...';
				UninstallProgressForm.ProgressBar.Min := 0;
				UninstallProgressForm.ProgressBar.Max := 4;
				UninstallProgressForm.ProgressBar.Position := 0;
			end;
			// Sleep(200);

			// Create an Excel COM Automation object.
			//
			if CreateExcelApp then begin
				Log('Retrieving the Add-In to disable.');

				if IsInstaller then begin
					DeActivateProgressPage.SetText('Retrieving the Add-In...', '');
					DeActivateProgressPage.SetProgress(1,4);
				end else begin
					UninstallProgressForm.StatusLabel.Caption := 'Retrieving the Add-In...';
					UninstallProgressForm.ProgressBar.Position := 1;
				end;
				// Sleep(200);

				// Get the Add-In.
				//
				try
					ExcelAddIn := ExcelApp.AddIns(XLA_ADDIN_NAME);
				except
					if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('No matching Add-In found.');
				end;

				if not VarIsEmpty(ExcelAddIn) then begin
					Log('Deactivating the Add-In.');

					if IsInstaller then begin
						DeActivateProgressPage.SetText('Deactivating the Add-In...', '');
						DeActivateProgressPage.SetProgress(2,4);
					end else begin
						UninstallProgressForm.StatusLabel.Caption := 'Deactivating the Add-In...';
						UninstallProgressForm.ProgressBar.Position := 2;
					end;
					// Sleep(200);

					Result := ExcelAddIn.FullName;
					try
						ExcelAddIn.Installed := false;
					except
					end;
					ExcelApp.Visible := false;
				end;
			end;

			// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
			//
			Log('Stopping Microsoft Excel COM Automation after disabling Add-In.');

			if IsInstaller then begin
				DeActivateProgressPage.SetText('Stopping Microsoft Excel COM Automation...', '');
				DeActivateProgressPage.SetProgress(3,4);
			end else begin
				UninstallProgressForm.StatusLabel.Caption := 'Stopping Microsoft Excel COM Automation...';
				UninstallProgressForm.ProgressBar.Position := 3;
			end;
			// Sleep(200);

			KillExcelApp;

			if IsInstaller then begin
				DeActivateProgressPage.SetProgress(4,4);
			end else begin
				UninstallProgressForm.ProgressBar.Position := 4;
			end;
			// Sleep(500);
		end;
	finally
		if IsInstaller then begin
			DeActivateProgressPage.Hide;
		end else begin
			UninstallProgressForm.Hide;
		end;
	end;
end;
CopyTheAddIn

This functions extracts the XLA and XLAM if available to a temporary folder and then copies the appropriate one to the correct Excel Add-In folder. The script also copies a .CHM file with the on-line help to the same folder as the Add-In.

A string variable called FullErrorString defined as a local variable will contain errors that this function may encounter during execution. This string is logged to screen or to file.

The function returns a String containing the full name of the Add-In.

function CopyTheAddIn(): String;
(*
	Copies the Add-In and its accessory files to the destination folder.
*)
var
	SourcePath: String;
	ExcelAddInPath: String;
	CopySuccess: Boolean;
	FullErrorString: String;
begin
	// Default.
	//
	Result := '';

	// The Add-In is not copied by the Installer (Flags: dontcopy;) so it need to be
	// extracted from the package before we can copy it to the destination folder.
	// The 'ExtractTemporaryFile' may hickup an exception which is ignored here.
	//
	// Extract the new macro-enabled 'Zipped-XML' XLAM from the package if available.
	//
	try
		ExtractTemporaryFile(XLAM_FILE_NAME);
	except
	end;
	//
	// Extract the old-style XLA from the package.
	//
	try
		ExtractTemporaryFile(XLA_FILE_NAME);
	except
	end;

	// We copy the Add-In files(s) to the selected Add-Ins folder. Fetch the
	// appropriate folder. If the variable is still uninitialised, create it.
	//
	if (InstallMode = 0) then begin
		if (ExcelUserLibraryPath = '') then CreateExcelApp;
		ExcelAddInPath := ExcelUserLibraryPath;
	end else begin
		if (ExcelLibraryPath = '') then CreateExcelApp;
		ExcelAddInPath := ExcelLibraryPath;
	end;
	ExcelAddInPath := AddBackslash(ExcelAddInPath);

	if (ExcelAddInPath <> '') then begin
		// Look if the directory exists and if not, create the entire path to this directory.
		//
		if not DirExists(ExcelAddInPath) then ForceDirectories(ExcelAddInPath);

		// Copy the file(s). The first set of files is located in the temporary folder.
		//
		SourcePath := AddBackslash(GetExpandedConstant('{tmp}', '', ''));
		CopySuccess := false;
		FullErrorString := '';
		if IsExcel2007OrHigher and FileExists(SourcePath + XLAM_FILE_NAME) then begin
			// Excel 2007+ with XLAM
			//
			if FileExists(SourcePath + XLAM_FILE_NAME) then begin
				CopySuccess := FileCopy(SourcePath + XLAM_FILE_NAME, ExcelAddInPath + XLAM_FILE_NAME, false);
				if not CopySuccess then begin
					// See if there are any errors.
					//
					if (DLLGetLastError <> 0) then begin
						FullErrorString := AddPeriod(SysErrorMessage(DLLGetLastError));
					end else begin
						FullErrorString := 'Unknown error during copy of ''' +
										   SourcePath + XLAM_FILE_NAME +
										   ''' to ''' +
										   ExcelAddInPath + XLAM_FILE_NAME + '''.';
					end;
				end;
			end;
		end else begin
			// Excel 2003- or XLA only.
			//
			if FileExists(SourcePath + XLA_FILE_NAME) then begin
				CopySuccess := FileCopy(SourcePath + XLA_FILE_NAME, ExcelAddInPath + XLA_FILE_NAME, false);
				if not CopySuccess then begin
					// See if there are any errors.
					//
					if (DLLGetLastError <> 0) then begin
						FullErrorString := AddPeriod(SysErrorMessage(DLLGetLastError));
					end else begin
						FullErrorString := 'Unknown error during copy of ''' +
										   SourcePath + XLA_FILE_NAME +
										   ''' to ''' +
										   ExcelAddInPath + XLA_FILE_NAME + '''.';
					end;
				end;
			end;
		end;

		// The next set of files is located in the '{app}' folder.
		//
		SourcePath := AddBackslash(GetExpandedConstant('{app}', '', ''));
		if FileExists(SourcePath + XLA_HELP_NAME) then begin
			CopySuccess := FileCopy(SourcePath + XLA_HELP_NAME, ExcelAddInPath + XLA_HELP_NAME, false) and CopySuccess;
			if not CopySuccess then begin
				// See if there are any errors.
				//
				if (Length(Trim(FullErrorString)) > 1) then FullErrorString := FullErrorString + #13#10;
				if (DLLGetLastError <> 0) then begin
					FullErrorString := FullErrorString +
									   AddPeriod(SysErrorMessage(DLLGetLastError));
				end else begin
					FullErrorString := FullErrorString +
									   'Unknown error during copy of ''' +
									   SourcePath + XLA_HELP_NAME +
									   ''' to ''' +
									   ExcelAddInPath + XLA_HELP_NAME + '''.';
				end;
			end;
		end;

		// Return where we dropped the file(s).
		//
		if CopySuccess then begin
			if IsExcel2007OrHigher and FileExists(ExcelAddInPath + XLAM_FILE_NAME) then begin
				// Excel 2007+ with XLAM
				//
				Result := (ExcelAddInPath + XLAM_FILE_NAME);
			end else begin
				// Excel 2003- or XLA only
				//
				Result := (ExcelAddInPath + XLA_FILE_NAME);
			end;
		end else begin
			if IsRunningSilent then begin
				Log('Could not copy Add-In or one of its support files to ''' + ExcelAddInPath + '''. If this error is unexpected, contact a System Administrator.');
			end else begin
				if (Length(Trim(FullErrorString)) > 1) then FullErrorString := FullErrorString + #13#10 + #13#10;
				MsgBox('Error: could not copy Add-In or one of its support files to ''' + ExcelAddInPath + '''.' + #13#10 + #13#10 +
					FullErrorString + 'If this error is unexpected, contact a System Administrator for assistance.', mbError, mb_Ok);
			end;
		end;
	end else begin
		// Oops, empty folder? This should not happen but if it does, then warn.
		//
		if IsRunningSilent then begin
			Log('Could not establish Add-In path. If this error is unexpected, contact a System Administrator.');
		end else begin
			MsgBox('Error: could not establish Add-In path.' + #13#10 + #13#10 +
				'If this error is unexpected, contact a System Administrator for assistance.', mbError, mb_Ok);
		end;
	end;

	// If we started Excel then we don't need this instance anymore.
	// It will also forcibly terminate the 'EXCEL.EXE' process.
	//
	KillExcelApp;
end;
RemoveTheAddIn

This function physically removes the Add-In from Excel’s folders and will return True if successful. It receives the full name of the Add-In from the function caller. This is the name returned by for instance UnhookAddinFromExcel. A localised version of the lexicon file found in the Excel folder is also removed (this one would have been copied by the Add-In).

function RemoveTheAddIn(ExcelAddInFullName: String): Boolean;
(*
	Remove the Add-In and its accessory files from the destination folder
*)
var
	ExcelAddInPath: String;
begin
	Result := true;

	// We remove the files(s) from the same folder as the Add-In.
	//
	if (ExcelAddInFullName <> '') then begin
		// Main Add-In.
		//
		if FileExists(ExcelAddInFullName) then Result := DeleteFile(ExcelAddInFullName) and Result;

		// Other related files.
		//
		ExcelAddInPath := AddBackslash(ExtractFileDir(ExcelAddInFullName));
		if FileExists(ExcelAddInPath + XLA_HELP_NAME)    then Result := DeleteFile(ExcelAddInPath + XLA_HELP_NAME)    and Result;
		if FileExists(ExcelAddInPath + XLA_LEXICON_NAME) then Result := DeleteFile(ExcelAddInPath + XLA_LEXICON_NAME) and Result;

		// Registry. If the key is located under "Add-in Manager" then this is a non-active Add-In in a non-standard folder on
		// the same drive as where Excel is installed. Non-active Add-Ins in standard folders do not appear in the registry but
		// are read by Excel directly from such folders.
		//
		if RegValueExists(HKEY_CURRENT_USER, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Add-in Manager', ExcelAddInFullName) then begin
			RegDeleteValue(HKEY_CURRENT_USER, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\Add-in Manager', ExcelAddInFullName)
		end;
	end;
end;
DoPreInstall

This routine runs before perfoming any installation activity. This is the right place to perform a test on the environment and this is exactly what we will do here.

At the moment our test is limited to checking the correct version of VBE6.DLL, since I’ve found that below a particular version of VBE6.DLL Excel crashes using my Add-In (you can read this post on the subject). You can add code to quit the installer when the check returns False but I chose not to.

procedure DoPreInstall();
(*
	Pre-Installation procedure. Runs before performing any action.
*)
begin
	// We are about to install. Check if VBE6.DLL can cause any problem.
	//
	CheckVBE6DLLVersion();
end;
DoPostInstall

This procedure runs after having finished installing all of the regular stuff, which excludes copying the XLA/XLAM and related to the Add-Ins folder of Excel in reason of the dontcopy flag.

At this point we can proceed and remove the Add-In currently installed and place the one in this package into the correct folder. Should this be the first time that the setup is executed, then the unhook function will return an empty string and the file removal will be skipped. I have deliberately chosen not to use Boolean functions because using String functions allows me to carry around the full name of the Add-In without using global variables.

procedure DoPostInstall();
(*
	Post-Installation procedure. Runs after performing all installation actions.
*)
var
	ExcelAddInFullName: String;
begin
	// We have finished installing so we need to hook the Add-In into Excel and activate/enable it.
	// We first remove the old Add-In from Excel and the Registry and from the installation folder also
	// otherwise Excel continues to 'see' it in its Add-Ins dialog.
	//
	ExcelAddInFullName := UnhookAddinFromExcel();

	// Remove the Add-In if found between the Excel Add-Ins.
	//
	if (ExcelAddInFullName <> '') then RemoveTheAddIn(ExcelAddInFullName);

	// Copy the new Add-In.
	//
	ExcelAddInFullName := CopyTheAddIn();

	// Hook the new Add-In into Excel. Error messages trapped above.
	//
	if (ExcelAddInFullName <> '') then HookAddinToExcel();
end;
DoSuccessInstall

This procedure runs after the installation was successful. I use this routine to check the Trust Settings on the target PC. The active part in this code only warns or logs if the settings are not properly set, while the commented part actually would set them as required for the Add-In to run smoothly.

The Trust Settings are only tested at this stage and not earlier because I found it more logical to do it here.

procedure DoSuccessInstall();
(*
	Successful Installation procedure. Runs after performing all actions.
*)
var
	TrustAddInsAndTemplates: Boolean;
	TrustVBProject: Boolean;
	MsgBoxResult: Integer;
begin
	// Check Trust to Add-Ins and Templates (for all versions)
	//
	TrustAddInsAndTemplates := IsTrustAddInsAndTemplatesChecked;

	// Check in Access To Visual Basic Project is enabled in Excel.
	// Note: Excel 2000 does not have this setting.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		TrustVBProject := IsTrustVBProjectChecked;
	end else begin
		TrustVBProject := true;
	end;

	// If one of the 2 is not set, then issue a warning message.
	//
	if not TrustAddInsAndTemplates or not TrustVBProject then begin
		if IsRunningSilent then begin
			Log('Warning: Macro Security Trust settings not properly set. {#TheAppName} may not work properly.');
		end else begin
			// Just a message.
			// Comment this statement and uncomment next statement if you want to set.
			//
			MsgBoxResult := MsgBox('Information: Macro Security Trust settings not properly set.' + #13#10 + #13#10 +
				'To operate with {#TheAppName}, the Trust settings in the Macro Security must be set differently.' + #13#10 + #13#10 +
				'Contact a System Administrator for assistance.', mbInformation, mb_Ok);

//			// A question whether this shall be turned on right now?
//			// Uncomment this statement and comment previous statement if you want to set.
//			//
//			MsgBoxResult := MsgBox('Information: Macro Security Trust settings not properly set.' + #13#10 + #13#10 +
//				'To operate with {#TheAppName}, the Trust settings in the Macro Security must be set differently.' + #13#10 + #13#10 +
//				'Would you like to do this right now?', mbConfirmation, mb_YesNo);
//			//
//			if (MsgBoxResult = IDYES) then begin
//				if not SetTrustAddInsAndTemplates and not SetTrustVBProject then begin
//					MsgBox('Warning: could not set required Macro Security Trust settings.' + #13#10 + #13#10 +
//						'You may not have sufficient privileges to perform this action. Before you can operate with' + #13#10 +
//						'{#TheAppName}, remember to check and/or set these setting in Microsoft Excel.' + #13#10 + #13#10 +
//						'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
//				end;
//			end;
		end;
	end;
end;
DoPreUnInstall

This procedure runs before the uninstallation begins. Currently it does nothing and is included for symmetry.

procedure DoPreUnInstall();
(*
	Pre-UnInstallation procedure. Runs before performing any action.
*)
begin
	// Here we do nothing at all.
	//
end;
DoPostUnInstall

This procedure runs after the uninstallation has succeeded, and is designed to perform ‘garbage collection’ and remove files not managed by Inno.

procedure DoPostUnInstall();
(*
	Post-UnInstallation procedure. Runs after performing all uninstallation actions.
*)
var
	ExcelAddInFullName: String;
begin
	// We have finished uninstalling so we need to remove the Add-In from the Registry and the
	// installation folder also otherwise Excel continues to 'see' it in its Add-Ins dialog.
	//
	ExcelAddInFullName := UnhookAddinFromExcel();

	// Remove the Add-In if found between the Excel Add-Ins.
	//
	if (ExcelAddInFullName <> '') then RemoveTheAddIn(ExcelAddInFullName);
end;

Assembling the bits and pieces of these pages you will be able to create your own Installer for Excel Add-Ins.

I hope you like what you’ve read and hope it can help you with your work. Since this contribution, and the other ones on this blog, have cost me time and money, you can show your appreciation by making a donation.

Previous Page

Install (and Uninstall) an Excel Add-In with InnoSetup – Page 4

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 12:45 pm
Tags: , , , ,

The Script specific for the Add-In

This is the main script that deals with the Add-In specific data and that sets some variable data used in the “Excel” generic script called with an #include statement (see at the end of this page).

Definitions:

; Definitions
;
; NOTE: The value of AppId uniquely identifies this application.
; Do not use the same AppId value in installers for other applications.
; (To generate a new GUID, click Tools | Generate GUID inside the IDE.)
;
#define TheAppId "{11111111-2222-3333-4444-555555555555}"
#define TheAppName "The Dutch Gemini Add-In for Excel"
#define TheAppShortName "Dutch Gemini"
#define TheAppVeryShortName "DGXLAddIn"
#define TheAppVersion "1.2.3"
#define TheAppAddInName "DUTCH ADD-IN"
#define TheAppRoot "DGXLAI"
#define TheSetupRoot "DG_AI"
#define TheSetupVersion StringChange(TheAppVersion, ".", "_")
;
; Increase the number at the end for every new SETUP of the same {#TheAppVersion}.
; Restart the number with "1" for every new RELEASE of {#TheAppVersion}.
; This is version '1' of the setup, based on the same component.
;
#define TheSetupVersionBuild "1"
#define TheCopyright "Copyright © 2011-" + GetDateTimeString("yyyy", "", "")
#define TheCompany "Excel Development by Dutch Gemini"
#define TheContact "Dutch Gemini"
#define TheURL <a href="https://dutchgemini.wordpress.com">https://dutchgemini.wordpress.com</a>

I have added the following instruction for conditional compilation of items under Components and Files. I have no clue about the specific syntax of this statement, but it works so I do not bother either.

;
; For conditional compilation of [Components] and [Files]
;
#define WildcardExists(str Filename) \
  ((Local[0] = FindFirst(Filename, faAnyFile)) ? \
   (FindClose(Local[0]), True) : False)
Setup section

Here begins the Setup section. Most of the items use definitions declared above.

[Setup]
;
; Put the date on December 31, 2011 at 1.2.0
;
TouchDate=2011-12-31
TouchTime=01:02:00
;
; Establish install logging. If set to "true" then always log. Override at run-time
; with setup command line option "/LOG" (to %TEMP%) or "/LOG=filename".
;
SetupLogging=false
MinVersion=0,5.0.2195sp3
;
AppId={{#TheAppId}
;
AppComments={#TheAppName}
AppContact={#TheContact}
AppCopyright={#TheCopyright} {#TheCompany}
AppName={#TheAppName}
AppPublisher={#TheCompany}
AppVerName={#TheAppName} {#TheAppVersion}
AppVersion={#TheAppVersion}
;
AppPublisherURL={#TheURL}
AppSupportURL={#TheURL}
AppUpdatesURL={#TheURL}
;
VersionInfoCompany={#TheCompany}
VersionInfoCopyright={#TheCopyright} {#TheCompany}
VersionInfoDescription={#TheAppName}
VersionInfoProductName={#TheAppName}
VersionInfoProductVersion={#TheAppVersion}
VersionInfoTextVersion={#TheAppVersion}
VersionInfoVersion={#TheAppVersion}.{#TheSetupVersionBuild}
;
AlwaysShowDirOnReadyPage=true
AlwaysShowGroupOnReadyPage=true
AlwaysUsePersonalGroup=false
DefaultDirName={pf}\{#TheAppVeryShortName}
DefaultGroupName={#TheAppName}
DisableDirPage=false
DisableProgramGroupPage=false
PrivilegesRequired=admin
UsePreviousAppDir=true
UsePreviousGroup=true
UserInfoPage=true
UpdateUninstallLogAppName=false
;
LicenseFile=License.rtf
InfoBeforeFile=Intro.rtf
InfoAfterFile=Extro.rtf
;
Compression=lzma
InternalCompressLevel=max
SolidCompression=true
;
FlatComponentsList=false
SetupIconFile={#TheAppVeryShortName}.ico
ShowLanguageDialog=auto
Uninstallable=true
UninstallDisplayIcon={uninstallexe}
WizardImageFile=DGAIinstaller.bmp
; compiler:wizmodernimage-is.bmp
WizardSmallImageFile=DGAIinstallersmall.bmp
; compiler:wizmodernsmallimage-is.bmp
WizardImageStretch=false
;
SourceDir=D:\Development\DGXLAI\Setup
OutputDir=D:\Development\DGXLAI\Setup\Installer
OutputBaseFilename={#TheSetupRoot}_{#TheSetupVersion}_{#TheSetupVersionBuild}_Setup

I have added the following instructions for making sure the setup has access to 32-bit registry sections on 32-bit systems and 64-bit and 32-bit registry sections on 64-bit systems

; Specifically permit running 64bit mode, since we need this to read from the
; correct node in the Registry.
; "ArchitecturesAllowed=x64" specifies that Setup cannot run on anything but x64.
; "ArchitecturesInstallIn64BitMode=x64" requests that the install be done
; in "64-bit mode" on x64, meaning it should use the native 64-bit Program Files
; directory and the 64-bit view of the registry.
;
ArchitecturesAllowed=x86 x64
ArchitecturesInstallIn64BitMode=x64

The next items deal with the UI (I’m a Tahoma fan) and the languages. see InnoSetup’s help for additional info.

[LangOptions]
DialogFontName=Tahoma
TitleFontName=Tahoma
WelcomeFontName=Tahoma
CopyrightFontName=Tahoma
[Languages]
Name: english; MessagesFile: compiler:Default.isl
Tasks, Types and Components section

The Tasks, Types and Components section. I’ve found this to be particular difficult to understand and the on-line help of Inno does not really assist you, each section is basically described but what I really miss is the explanation between the various parameters and the sections where you can use these. For instance, entries under Components link to entries under Types but also under Tasks and then also under Files and Icons, and … enfin, probably I’ve not even covered all the possibilities. Nevertheless, after some genuine trial-and-error I’m relatively satified with the result.

[Tasks]
;
; Assign "Tasks: desktopicon" as a parameter to those elements that need to be processed when this task is selected.
;
Name: desktopiconhtml; Description: {cm:CreateDesktopIcon} for HTML Help; GroupDescription: {cm:AdditionalIcons}; Components: full\htmlhelp
Name: desktopiconpdf; Description: {cm:CreateDesktopIcon} for PDF Help; GroupDescription: {cm:AdditionalIcons}; Components: full\htmlhelp

[Types]
Name: full; Description: Complete installation
Name: addin; Description: {#TheAppName} Add-In only
Name: custom; Description: Custom installation; Flags: iscustom

[Components]
Name: full; Description: {#TheAppName}; Types: full addin
Name: full\addin; Description: {#TheAppShortName} Add-In for Microsoft Excel; Types: full addin
#if WildcardExists('..\Help\Output\' + TheAppRoot + '.CHM') 							/* Conditional compilation */
Name: full\htmlhelp; Description: {#TheAppShortName} HTML and PDF Help; Types: full
#endif
#if WildcardExists('Lexicons\*.LEX') 													/* Conditional compilation */
Name: full\lexicon; Description: {#TheAppShortName} Language Lexicons; Types: full
#endif
#if WildcardExists('Sample Projects\*.XL*') 											/* Conditional compilation */
Name: full\samples; Description: Sample Projects; Types: full
#endif
Files section

The Files section is where all the files from my system are embedded in the setup package. The script copies the UnInstall.ico only for creating an iconised “uninstall application” Start Menu entry. Such menu entry, still pretty common, is nevertheless deprecated by Microsoft that recommends creating uninstaller entries exclusively under the Control Panel’s Programs and Features (Vista or 7) or Add or Remove Programs (with XP) and in the future I will follow this recommendation.

[Files]
Source: UnInstall.ico; DestDir: {app}; Flags: ignoreversion replacesameversion overwritereadonly; Components: full
;
; NOTE: Don't use "Flags: ignoreversion" on any shared system files
;
; Microsoft Excel Add-Ins need to be installed in a specific folder so that the Add-Ins Manager (uses a registry entry called
; "HKCU\Software\Microsoft\Office\<version>\Excel\Options") will treat them natively via a link stored as "OPENx" key ("x" can
; be blank or "1", "2", ...) instead of under the registry key "HKCU\Software\Microsoft\Office\<version>\Excel\Add-in Manager".
; This folder is usually '{userappdata}\Microsoft\Add-Ins' but Excel may have another storage locations.
;
; These components have 'dontcopy' flag, have no destination and will be copied to the final folder via [Code].
; Source: ..\{#TheAppRoot}.XLA; DestDir: {app}; Flags: ignoreversion replacesameversion overwritereadonly; Components: full\addin
;
Source: ..\{#TheAppRoot}.XLA; Flags: dontcopy touch; Components: full\addin
Source: ..\{#TheAppRoot}.XLAM; Flags: dontcopy skipifsourcedoesntexist touch; Components: full\addin
;
Source: ..\Documentation\{#TheAppShortName} Release, Installation and Quick Reference.pdf; DestDir: {app}; Flags: overwritereadonly ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\addin
Source: ..\Documentation\readme.txt; DestDir: {app}; Flags: isreadme overwritereadonly skipifsourcedoesntexist ignoreversion replacesameversion touch; Components: full\addin
Source: ..\Documentation\WhatsNew.txt; DestDir: {app}; Flags: overwritereadonly skipifsourcedoesntexist ignoreversion replacesameversion touch; Components: full\addin
;
#if WildcardExists('..\Help\Output\' + TheAppRoot + '.CHM')								/* Conditional compilation */
Source: ..\Help\Output\{#TheAppRoot}.CHM; DestDir: {app}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\addin
Source: ..\Help\Output\{#TheAppRoot} (v{#TheAppVersion}).PDF; DestDir: {code:GetHTMLHelpPath}; DestName: {#TheAppRoot}.PDF; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\htmlhelp
Source: ..\Help\Output\{#TheAppRoot}.HTM; DestDir: {code:GetHTMLHelpPath}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion touch; Components: full\htmlhelp
Source: ..\Help\Output\files\*; DestDir: {code:GetHTMLHelpPath}files; Flags: ignoreversion recursesubdirs createallsubdirs skipifsourcedoesntexist replacesameversion sortfilesbyextension touch; Components: full\htmlhelp
#endif
#if WildcardExists('Lexicons\*.LEX') 													/* Conditional compilation */
Source: Lexicons\*.LEX; DestDir: {code:GetLexiconPath}; Flags: ignoreversion skipifsourcedoesntexist replacesameversion overwritereadonly sortfilesbyextension touch; Components: full\lexicon
#endif
#if WildcardExists('Sample Projects\*.XL*') 											/* Conditional compilation */
Source: Sample Projects\*.XL*; DestDir: {code:GetSamplesPath}; Flags: ignoreversion recursesubdirs createallsubdirs skipifsourcedoesntexist replacesameversion overwritereadonly sortfilesbyextension touch; Components: full\samples
#endif

My Add-In uses components from the VB6 Runtime Modules. These modules are normally not installed but are vital to the correct functioning of the Add-In. Hence, I’ve included a small script obtained from Inno’s knowledge base, that includes these files for me. I’ve also added a few components that I have found “missing” on Windows 7 but are being used by my Add-In.

#include "D:\Development\Common\SetupScripts\VBRunTimes.iss"

Click below to expand this script:

[Files]
; ===========================================================
; Begin VB system files (from VBRun60SP6.EXE) - 32bit
;
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\asycfilt.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\msvbvm60.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\oleaut32.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\olepro32.dll"; DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regserver noregerror; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\comcat.dll";   DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regserver noregerror; Components: full
Source: "D:\Development\VBRunTime\VB6RunTimeFiles\stdole2.tlb";  DestDir: {sys}; Flags: confirmoverwrite onlyifdoesntexist restartreplace uninsneveruninstall sharedfile 32bit regtypelib noregerror; Components: full
;
; End VB system files
; ===========================================================
; Specific controls included in the project but found missing on Windows 7 - 32bit.
; Always use the following parameters for an OCX:
; ...; DestDir: "{sys}"; Flags: restartreplace sharedfile regserver
;
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\comdlg32.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\mscomct2.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
Source: "D:\Development\Common\VBRunTime\VB6CommonControls\mscomctl.ocx"; DestDir: {sys}; Flags: skipifsourcedoesntexist onlyifdoesntexist restartreplace sharedfile regserver 32bit; Components: full
; ===========================================================
Icons section

The Icons section. The UnInstall entry, mentioned above, is also present (as is the reference to the icon file) .

Some entries use a User Defined Function retrieving the name of a custom folder on the destination PC where I commonly install samples, help files, and more.

[Icons]
;
; Start menu icons.
;
Name: {group}\Release Notes, Installation Guide and Quick Reference; Filename: {app}\{#TheAppShortName} Release, Installation and Quick Reference.pdf; WorkingDir: {app}; Flags: createonlyiffileexists; Components: full\addin
Name: {group}\Read Me; Filename: {app}\readme.txt; WorkingDir: {app}; Components: full\addin
Name: {group}\What Is New; Filename: {app}\WhatsNew.txt; WorkingDir: {app}; Components: full\addin
Name: {group}\{cm:UninstallProgram,{#TheAppName}}; Filename: {uninstallexe}; IconFilename: {app}\UnInstall.ico; IconIndex: 0
Name: {group}\{#TheAppName} Documentation (PDF); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.PDF; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp
Name: {group}\{#TheAppName} Documentation (HTML); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.HTM; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp
;
; Desktop icons. See "[Tasks]" for further details
;
Name: {code:GetDesktop}\{#TheAppName} Documentation (PDF); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.PDF; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp; Tasks: desktopiconpdf
Name: {code:GetDesktop}\{#TheAppName} Documentation (HTML); Filename: {code:GetHTMLHelpPath}{#TheAppRoot}.HTM; WorkingDir: {code:GetHTMLHelpPath}; Flags: createonlyiffileexists; Components: full\htmlhelp; Tasks: desktopiconhtml
Run Section

In the Run section I allow Excel to start at the end of the installation process. It uses shellexec so that it does not need an exact path to excel.exe.

[Run]
; Without 'shellexec' this command requires exact path to the excel.exe file which is soooo version dependent
Filename: Excel.exe; Description: {cm:LaunchProgram,Microsoft Excel}; Flags: shellexec nowait postinstall skipifsilent unchecked
Registry section

In the Registry section I prepare the registry for my application.

[Registry]
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}; Flags: uninsdeletekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\QueryLibrary; Flags: uninsdeletekey dontcreatekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\ToolBar; Flags: deletekey uninsdeletekey dontcreatekey
Root: HKCU; Subkey: Software\VB and VBA Program Settings\{#TheAppName}\MRUD; Flags: uninsdeletekey

I also have a specific entry that enables me to use the Common Dialog Control on systems where this component is installed but fails to initialise in Late Binding (which is what I use). Without this entry, you cannot use the MSComDlg.CommonDialog class in your code. This class is embedded in COMDLG32.OCX which is a creatable control only on systems where a development environment (and as such a proper license) is available. To allow creating this class on non-development PCs, you can add this license with this particular key and value (Default):

Root: HKCR; Subkey: Licenses\4D553650-6ABE-11cf-8ADB-00AA00C00905; ValueType: string; ValueData: gfjmrfkfifkmkfffrlmmgmhmnlulkmfmqkqj; Flags: createvalueifdoesntexist
Excel specific script

The next statement includes the script containing the complete logic for the installation, activation, deactivation, update and removal of the Add-In. This script also uses the definitions given at the beginning of this page. It will be described on the next page.

#include "D:\Development\Common\SetupScripts\ExcelAddinScript.iss"

Finally, this last statement closes the script, which I use for debugging purposes:

#expr SaveToFile("debug.iss")

Previous Page | Next Page

September 5, 2011

Install (and Uninstall) an Excel Add-In with InnoSetup – Page 3

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 2:47 pm
Tags: , , , ,

Checking Trust Settings (or Macro Security Settings)

As most know, Excel will permit running a single line of VBA code only after you have decided that you trust the code and that you trust access to VB Projects contained in your workbooks; an Add-In is just another flavour of a workbook and is subject to the same restictions. Trust to VBA code is set via Trust Settings (Excel 2007 and higher, in earlier releases of Excel they are called Macro Security Settings).

Although there seem to be ways to make Excel run the Add-In without touching the Trust Settings, for instance using certificates, I feel that the best option is to make the system run the way it was designed, with Trust Settings set in the correct manner. Corporate IT administrators appear to like this, as they may control them with Group Policies or other types of access control.

I have decided to check for the Trust Settings during the installation process because often my customers have complained that the Add-In refuses to run, triggering all kind of errors that I have later found out were all caused by incorrect Trust Settings. The script contains the necessary code for manipulating the trust settings programmatically, but I’ve decided not to use this opportunity and leave them untouched when I install or uninstall, in line with most corporate IT policies.

[Not] Copying the Add-In files

I decided not to let InnoSetup copy the Add-In (XLA or XLAM) and its support files (CHM, BIN, DAT, …) immediately to the destination folder on the PC but use the dontcopy flag instead and extract them from the package to a temporary folder and copy them to the final destination programmatically. This gives me more control when dealing with different versions of Excel.

Installation Sequence

Trimming down to the very basic, I have identified these major actions, steps and the chronological order of things:

  1. Pre-Install (before Inno even copies a single file)
    1. Check VBE6.DLL version and break out if needed
  2. Post-Install (after Inno has finished copying the files)
    1. Unhook the Add-In from Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In in the collection of Add-Ins
      4. Store its name and disable Add-In
      5. Close Excel
    2. Remove the old Add-In
      1. Physically delete Add-In from Add-Ins folder
      2. Remove Add-In reference from Registry
    3. Copy the new Add-In
      1. Extract Add-In and support files from package to temporary folder
      2. Force existence of Add-In destination folders (seen missing on French version of Excel)
      3. Copy files from temporary folder to Add-Ins destination folder
    4. Hook the Add-In into Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In in the collection of Add-Ins
      4. Enable the Add-In
      5. Close Excel
  3. Success-Install (when Inno has finished)
    1. Check the Trust settings
    2. Set the Trust settings (optional)

Uninstallation Sequence

Trimming down to the very basic, I have identified these major actions, steps and the chronological order of things:

  1. Pre-UnInstall (before Inno removes the stuff it installed)
    1. <nothing for the moment>
  2. Post-UnInstall (after Inno has finished removing its own stuff)
    1. Unhook the Add-In from Excel
      1. Start Excel
      2. Read Add-In installation folders (LibraryPath and UserLibraryPath)
      3. Locate Add-In
      4. Store its name and disable Add-In
      5. Close Excel
    2. Remove the old Add-In
      1. Physically delete Add-In from folder
      2. Remove Add-In reference from Registry

As you can see, there are parts in common between the installation and uninstallation. This was the main reason for making the script modular.

Let’s focus on the main steps.

Hooking the Add-In into Excel

Hooking the Add-In from Excel consists in locating the Add-In in the collection of Add-Ins managed by Excel and enabling it. The Add-Ins collection is part of the Application object. To locate an Add-In, you scan the collection and identify the individual Add-In either by a Name or by the Add-In’s file name. I usually use the name, which corresponds to the one advertised by Excel in its Add-Ins dialogs.

Once you have located the Add-In, you enable it by setting the .Enabled property to True.

Now, this works only if the Add-In is already visible in Excel, that is, Excel has found the Add-In on your hard drive but did not load it yet. To make Excel see the Add-In, put it in a particular folder. See Copying the Add-In for more details.

Editor’s note: there are ways to load an Add-In on-the-fly and let Excel handle most of the stuff, but this is not what we want to achieve in our installer.

Unhooking the Add-In from Excel

Unhooking the Add-In from Excel consists in locating the Add-In in the collection of Add-Ins managed by Excel and disabling it. The Add-Ins collection is part of the Application object.

Once you have located the Add-In, you disable it by setting the .Enabled property to False.

Excel will unload the Add-In from memory and make sure the next time Excel is booted, it will not turn up again. However, this does not fully remove the Add-In from Excel but keep it dormient. To fully remove it, you must remove the file from the hard drive (see Removing the Add-In below).

Copying the Add-In

To make Excel aware of an Add-In it must be copied to one of the working folders for Add-Ins. Although it is in theory possible to drop an Add-In on whatever local and non-removable drive (Excel makes a copy of the Add-In if you try to use one located on a remote or removable drive), in practice though it is recommended to use one of Excel’s “standard” folders, accessible via 2 properties on the Application object:

  1. UserLibraryPath — this is the path for Add-Ins that are visible to the Current User only
  2. LibraryPath — this is the path for Add-Ins that are visible to ALL users

Removing the Add-In

To permanently remove an Add-In from Excel you need to physically remove the file from Excel’s working folders for Add-Ins (see above).

Previous Page | Next Page

Install (and Uninstall) an Excel Add-In with InnoSetup – Page 2

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 1:54 pm
Tags: , , , ,

Building up the Script

An installer for an Excel Add-In consist in an application-specific part and an “Excel” part. During the application-specific part all application-related files are managed, like copying files, samples, user manuals and references, etc. During the “Excel” part all actions are carried out for hooking the Add-In into Excel.

Following this approach, I have designed the install script in such way that the application-related part is the main script and the Excel part is taken care of by an #include script with the only [Code] section that performs the factual installation (and removal) of the Add-In. The included script uses a set of #define statements and compiler directives inherited from the main script. I will focus on the included script and wrap it all up at the end of my story.

From InnoSetup I have used the following event routines for my customisation:

  • InitializeSetup
  • InitializeWizard
  • CurStepChanged
  • NextButtonClick
  • ShouldSkipPage
  • UpdateReadyMemo
  • RegisterPreviousData
  • InitializeUninstall
  • InitializeUninstallProgressForm
  • CurUninstallStepChanged

InitializeSetup – This routine runs very early so we use it to detect whether Excel is in 32- or in 64-bit (important to know if your Add-In uses components that are not 64-bit, for instance Microsoft Common Controls). It is also used to detect compatibility with the third-party application. Failure to comply will abort the installation.

InitializeWizard – All custom dialogs are created here, like the “Only for me” and “All Users” selection, the dialogs for selecting a folder for the help and samples and for the Add-In installation progress.

CurStepChanged – Used as a header for calling sub-routines dealing with Pre-Install, Post-Install and Success-Install (all custom routines).

NextButtonClick – Marks the selected components and will trap whether Excel is running before it is ready to install the files (from the [Files] section). Also used to memorise the values of the custom dialogs (see above).

ShouldSkipPage – Detects whether or not to show certain custom dialogs.

UpdateReadyMemo – Prepares the report of what the installer is about to do, including settings for custom dialogs.

RegisterPreviousData – Stores the user’s selection of the custom dialogs.

InitializeUninstall – Traps whether Excel is running or not.

InitializeUninstallProgressForm – This particular event routine is used to create a custom uninstall progress form, as InnoSetup does not provide access to normal custom progress forms which are available only during installation. Note: this event procedure is not properly documented.

CurUninstallStepChanged – Like CurStepChanged, used as a header for calling sub-routines dealing with Pre-UnInstall and Post-UnInstall (all custom routines).

Support functions

The following user-defined functions were created in support of the actions:

Code snippets

CheckVBE6DLLVersion

function CheckVBE6DLLVersion(): Boolean;
(*
	Checks the version of VBE6.DLL.
	Excel can crash adding code programmatically when the version of VBE6.DLL is equal to or
	older than 6.5.10.24. This version of the DLL can be found all the way up to Excel 2007 and
	it not automatically upgraded by Microsoft in its Office services packs or updates. Apparently
	starting Excel 2007 SP2 a more recent and "bug-free" VBE6.DLL is being distributed.
*)
var
	VBE6DLLPath: String;
	VersionNumberString: String;
	VerMajorMinor: Cardinal;
	VerBuild: Cardinal;
	VerMajorMinorNotGood: Cardinal;
	VerBuildNotGood: Cardinal;
begin
	// Default.
	//
	Result := true;

	// The rule for computing the values are:
	//
	//		Major & Minor: ( shl 16) or
	//		Revision & Build: ( shl 16) or
	//
	VerMajorMinorNotGood := (6 shl 16) or 5;
	VerBuildNotGood := (10 shl 16) or 24;

	// Fetch the location of VBE6.DLL, the version numbers of this file and compare.
	//
	RegQueryStringValue(HKEY_LOCAL_MACHINE, 'SOFTWARE\Microsoft\VBA','Vbe6DllPath', VBE6DLLPath);

	if GetVersionNumbers(VBE6DLLPath, VerMajorMinor, VerBuild) then begin
		// Get a string with the data.
		//
		GetVersionNumbersString(VBE6DLLPath, VersionNumberString);

		// Compare.
		//
		if not ((VerMajorMinor > VerMajorMinorNotGood) or ((VerMajorMinor = VerMajorMinorNotGood) and (VerBuild > VerBuildNotGood))) then begin
			// Oops outdated version of VBE6.DLL'
			//
			if IsRunningSilent then begin
				Log('Incompatible version of VBE6.DLL found. See ''README.TXT'' for details. Contact a System Administrator.');
			end else begin
				MsgBox('Warning: incompatible version of VBE6.DLL found.' + #13#10 + #13#10 +
					'An incompatible version of a Visual Basic Environment module (VBE6.DLL) is installed on this computer (' + VersionNumberString + '). ' + GetMsgBoxCrLfCharacter +
					'This module can under certain circumstances cause Excel to crash when used in junction with {#TheAppName}. ' + GetMsgBoxCrLfCharacter +
					'It is highly recommended that you upgrade this module to a newer version.' + #13#10 + #13#10 +
					'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
			end;
			Result := false;
	    end;
	end else begin
		// This is Ok for the moment.
		//
		//	MsgBox('Warning: VBE6.DLL not found.' + #13#10 + #13#10 +
		//		'An module of the Visual Basic Environment is missing or could not be retrieved.' + #13#10 + #13#10 +
		//		'Contact a System Administrator for assistance.', mbInformation, mb_Ok);
		//	Result := (true Or false);
	end;
end;

GetDesktop

function GetDesktop(S: String): String;
(*
	Return the path to the Desktop.
*)
begin
	if (InstallMode = 0) then begin
		Result := GetExpandedConstant('{userdesktop}', '{commondesktop}', '');
	end else begin
		Result := GetExpandedConstant('{commondesktop}', '', '');
	end;
end;

GetExcelVersionNumberAsNumber

function GetExcelVersionNumberAsNumber(): Integer;
(*
	Search Registry for installed Excel version. From highests version down. This extra
	function was created because 'StrToIntDef()' cannot handle transformation of strings
	containing 'decimals', which is what GetExcelVersionNumberAsString() returns.
*)
begin
	if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\14.0\Excel\InstallRoot', 'Path') then begin
		Result := 14;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\12.0\Excel\InstallRoot', 'Path') then begin
		Result := 12;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\11.0\Excel\InstallRoot', 'Path') then begin
		Result := 11;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\10.0\Excel\InstallRoot', 'Path') then begin
		Result := 10;
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\9.0\Excel\InstallRoot', 'Path') then begin
		Result := 9;
	end else begin
		Result := 0;
	end;
end;

GetExcelVersionNumberAsString

function GetExcelVersionNumberAsString(): String;
(*
	Search Registry for installed Excel version. From highests version down.
*)
begin
	if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\14.0\Excel\InstallRoot', 'Path') then begin
		Result := '14.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\12.0\Excel\InstallRoot', 'Path') then begin
		Result := '12.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\11.0\Excel\InstallRoot', 'Path') then begin
		Result := '11.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\10.0\Excel\InstallRoot', 'Path') then begin
		Result := '10.0';
	end else if RegValueExists(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\9.0\Excel\InstallRoot', 'Path') then begin
		Result := '9.0';
	end else begin
		Result := '0.0';
	end;
end;

GetExpandedConstant

function GetExpandedConstant(const First, Second, Default: String): String;
(*
	Sometimes IS cannot expand constants and throws up exceptions.
	This happens for instance with {userdocs} when it is mapped to a network drive.
*)
var
	ResultString: String;
	ExceptionRaised: Boolean;
begin
	// Try first constant.
	//
	ExceptionRaised := false;
	try
		ResultString := ExpandConstant(First);
	except
		ExceptionRaised := true;
	end;

	// Try second is specified.
	//
	if ExceptionRaised then begin
		if (Length(Trim(Second)) > 1) then begin
			ExceptionRaised := false;
			try
				ResultString := ExpandConstant(Second);
			except
				ExceptionRaised := true;
			end;
		end;
	end;

	// Return whatever default.
	//
	if ExceptionRaised then begin
		ResultString := Default;
	end;

	Result := ResultString;
end;

GetMsgBoxCrLfCharacter

function GetMsgBoxCrLfCharacter(): String;
(*

	Vista and W7 perform a UI-aware formatting of the text in a MsgBox breaking up lines at unpredictable positions
	so we can not insert hard carriage return/linefeeds in the text to break lines where we want. In earlier versions
	of Windows this is OK. Therefore, on Vista or W7 we return a space and let it handle formatting and use a hard CrLf
	in all other cases.

*)
var
	WindowsVersion: Integer;
begin
	WindowsVersion := (GetWindowsVersion shr 24);
	if (WindowsVersion >= 6) then begin
		Result := ' ';
	end else begin
		Result := #13#10;
	end;
end;

IsExcel2007OrHigher

function IsExcel2007OrHigher(): Boolean;
(*
	Excel 2007 has version number 12, so if the version on board is 12 or higher then
	it is Excel 2007 or higher. With Excel 2007+, XLAM Add-Ins formats can be used.
*)
begin
	Result := false;

	if (GetExcelVersionNumberAsNumber >= 12) then Result := true;
end;

IsExcel64Bit

(*
	Importing a Windows API function for understanding if EXCEL.EXE is a 32-bit
	or 64-bit application. This function has a few named constants. There appears
	to be no other accurate option than looking at the exe's PE header.
*)
const
	SCS_32BIT_BINARY = 0;	// A 32-bit Windows-based application
	SCS_64BIT_BINARY = 6;	// A 64-bit Windows-based application
	SCS_DOS_BINARY   = 1;	// An MS-DOS – based application
	SCS_OS216_BINARY = 5;	// A 16-bit OS/2-based application
	SCS_PIF_BINARY   = 3;	// A PIF file that executes an MS-DOS – based application
	SCS_POSIX_BINARY = 4;	// A POSIX – based application
	SCS_WOW_BINARY   = 2;	// A 16-bit Windows-based application

function GetBinaryType(lpApplicationName: AnsiString; var lpBinaryType: Integer): Boolean;
	external 'GetBinaryTypeA@kernel32.dll stdcall';

function IsExcel64Bit(): Boolean;
(*
	64-bit versions of Office use the standard node of the Registry named
	'Software\Microsoft\' which is the same for 32-bit Excel on 32-bit Windows.
	The node named 'Software\Wow6432Node\Microsoft\' is only used for 32-bit
	Excel installed on 64-bit Windows so we can avoid checking.

	Makes sure you have these [Setup] directives correctly set otherwise it may
	not read from the correct branch in the Registry on 64-bit systems:
		ArchitecturesAllowed=x86 x64
		ArchitecturesInstallIn64BitMode=x64
*)
var
	InstallRoot: String;
	BinaryType: Integer;
begin
	Result := false;

	// Look for the InstallRoot of Excel. This is where Excel is installed.
	//
	if not RegQueryStringValue(HKEY_LOCAL_MACHINE, 'Software\Microsoft\Office\' + GetExcelVersionNumberAsString + '\Excel\InstallRoot', 'Path', InstallRoot) then exit;

	// Look what binary type 'EXCEL.EXE' is.
	//
	if GetBinaryType(AddBackslash(InstallRoot) + 'excel.exe', BinaryType) then begin
		Result := (BinaryType = SCS_64BIT_BINARY);
	end;
end;

IsExcelRunning

function IsExcelRunning(const Typ: TMsgBoxType; const Buttons, Default: Integer; var MsgBoxResult: Integer): Boolean;
(*
	Checks whether Excel is running in memory, by hooking to the COM Automation Object
	'Excel.Application'. If the object exists, then Excel is running.

	Another way would be scanning WMI processes for 'EXCEL.EXE' (simplified, no exception handling, no message boxes, etc.):

	function IsExcelRunning(): Boolean;
	var
		loLocator: Variant;
		loWMI: Variant;
		loProcesses: Variant;
	begin
		Result := false;
		loLocator := CreateOleObject('WBEMScripting.SWBEMLocator');
		loWMI := loLocator.ConnectServer();
		loWMI.Security_.ImpersonationLevel := 3; // Impersonate
		loProcesses := loWMI.ExecQuery('Select * from Win32_Process Where Name = ''excel.exe''');

		if (loProcesses.Count > 0) then Result := true;

		loProcesses := Unassigned;
		loWMI := Unassigned;
		loLocator := Unassigned;
	end;
*)
var
	ExcelRunning: Variant;
begin
	// Try to get an active Excel COM Automation object. If True, then Excel is Running.
	// The installation cannot continue if Excel is running, so we start with 'not running'.
	//
	Result := false;

	// Try to get an active Excel COM Automation object.
	//
	try
		ExcelRunning := GetActiveOleObject('Excel.Application');
	except
		if (Length(Trim(GetExceptionMessage)) > 1) then Log(AddPeriod(Trim(GetExceptionMessage))) else Log('Microsoft Excel not running (this is Ok).');
	end;

	if Not VarIsEmpty(ExcelRunning) then begin
		Result := true;
		if IsRunningSilent then begin
			Log('Microsoft Excel is running, cannot continue. Contact a System Administrator.');
			MsgBoxResult := Default;
		end else begin
			MsgBoxResult := MsgBox('Oops! Microsoft Excel is running.' + #13#10 + #13#10 +
				'The {#TheAppName} can only be installed when Microsoft Excel' + GetMsgBoxCrLfCharacter +
				'is not running. Close all active sessions of Microsoft Excel and retry.' + #13#10 + #13#10 +
				'If this notification is unexpected, contact a System Administrator.', Typ, Buttons);
		end;
	end;

	// Don't need this instance anymore. It will also forcibly terminate the 'EXCEL.EXE' process.
	//
	ExcelRunning := Unassigned;
end;

IsInstaller

function IsInstaller(): Boolean;
(*
	IsInstaller() in itself does not exist but since it is the opposite
	of IsUninstaller, we can use the latter to mimic the missing function.
*)
begin
	Result := not IsUninstaller;
end;

IsRunningSilent

function IsRunningSilent(): Boolean;
(*
	Indicates if the Installer or UnInstaller is running "silent".
*)
begin
	if IsInstaller then begin
		try
			Result := WizardSilent;
		except
			Result := false;
		end;
	end else begin
		try
			Result := UninstallSilent;
		except
			Result := false;
		end;
	end;
end;

IsTrustAddInsAndTemplatesChecked

function IsTrustAddInsAndTemplatesChecked(): Boolean;
(*
	Check if 'Trust all installed add-ins and templates' setting is turned 'On'.
	This is a particular Registry setting and impacts on Excel's capability to
	execute macro code in Add-Ins.
*)
var
	DontTrustInstalledFiles: Cardinal;
begin
	// This value is established by Excel when not available in the Registry so we need to start 'True'.
	//
	Result := true;

	// Fetch the setting of 'Trust all installed add-ins and templates'. If '0' then it is enabled.
	//
	if RegQueryDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
											 '\Excel\Security','DontTrustInstalledFiles', DontTrustInstalledFiles) then begin
		Result := (DontTrustInstalledFiles = 0);
	end;
end;

IsTrustVBProjectChecked

function IsTrustVBProjectChecked(): Boolean;
(*
	Check if 'Trust access to Visual Basic project' setting is turned 'On'.
	This is a particular Registry setting and impacts on Excel's capability to
	execute macro code in Add-Ins.
*)
var
	AccessVBOM: Cardinal;
begin
	// This value is established by Excel when not available in the Registry so we need to start 'True'.
	//
	Result := true;

	// Fetch the setting of 'Trust access to Visual Basic project'. If '1' then it is enabled.
	// Note: Excel 2000 does not have this setting so we keep the default 'true'.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		if RegQueryDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
												 '\Excel\Security','AccessVBOM', AccessVBOM) then begin
			Result := (AccessVBOM = 1);
		end;
	end;
end;

SetTrustAddInsAndTemplates

function SetTrustAddInsAndTemplates(): Boolean;
(*
	Turns the 'Trust all installed add-ins and templates' setting 'On'.
	This is a particular Registry setting and impacts on Excel's capability
	to execute macro code in Add-Ins.
*)
begin
	Result := false;

	// Set the setting of 'Trust all installed add-ins and templates'. If '0' then it is enabled.
	//
	RegWriteDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
										  '\Excel\Security','DontTrustInstalledFiles', 0)

	// Return the result.
	//
	Result := IsTrustAddInsAndTemplatesChecked;
end;

SetTrustVBProject

function SetTrustVBProject(): Boolean;
(*
	Turns the 'Trust access to Visual Basic project' setting 'On'.
	This is a particular Registry setting and impacts on Excel's capability
	to execute macro code in Add-Ins.
*)
begin
	Result := false;

	// Set the setting of 'Trust access to Visual Basic project'. If '1' then it is enabled.
	// Note: Excel 2000 does not have this setting but it probably ignores it.
	//
	if (GetExcelVersionNumberAsNumber >= 10) then begin
		RegWriteDWordValue(HKEY_CURRENT_USER, 'SOFTWARE\Microsoft\Office\' + GetExcelVersionNumberAsString +
											  '\Excel\Security','AccessVBOM', 1)

		// Return the result.
		//
		Result := IsTrustVBProjectChecked;
	end else begin
		Result := true;
	end;
end;

Previous Page | Next Page

Install (and Uninstall) an Excel Add-In with InnoSetup – Page 1

Filed under: Excel,InnoSetup,Programming,Setup,Uncategorized,VBA — dutchgemini @ 1:50 pm
Tags: , , , ,

Introduction

Installing an Excel Add-In using Excel interactively is a pretty straight forward action and is completed within a few steps independent of the version of Excel used (2000-2010). It takes care of copying the Add-In to the correct folder on your hard drive (which is not always needed, we will see this later), it allows enabling/disabling the Add-In in a guided manner via a built-in Dialog and makes sure its state is preserved each time you restart Excel.

The problems start when your Add-In relies on additional files, for instance a .INI file or a .CHM help file (mine do). At this point, either your Add-In is self-sufficient (creating the missing files automatically or knowing where to find them) or you have to carefully design the way your Add-In is installed to a target PC and hooked to Excel.

Finally, the other thing not really supported by Excel is the uninstallation of an Add-In. As a matter of fact this is indeed not possible, the only thing you can do is to disable an Add-In but it will continue to appear in Excel as long as the Add-In is physically on your hard drive.

To replicate the behaviour and to overcome the limitations set by Excel, I worked out a script in VBScript —made easy when you know VBA— which did the job in an excellent manner but had two main drawback: a) everyone could read and alter my script, and b) it required being ably to run a scripting engine on the target PC which under restricted security policies was not always possible.

Therefore, I decided to migrate my scripts to a tool that allowed me to create self-contained installation packages. After reviewing a few, I decided to go for InnoSetup as it offers extensive customisation options but above all a scripting engine and compiler for customising the installation process to the finest grain. Exactly what I needed. And I wasn’t afraid of using Pascal again (Inno is “based” on this language) after years.

The Beginning

InnoSetup has a wizard that allows creating a basic installation scripts for applications, but this script is not really useful for installing Excel Add-Ins as these [usually] not involve executable or system binary files. I nevertheless decided to create a basic script and work it out to my needs, taking the logic from my original VBScript and rewriting it in Pascal Script. This has been much more fun and challenging than I’d expected in the first place.

The Logic

Installing an Excel Add-In programmatically —and outside Excel— needs to take care of the following issues (not in chronological order):

  • Add-In Installation folder
  • All Users or Current User installation
  • Localisation
  • Registry settings
  • Installation or uninstallation process
  • Additional files needed by the Add-In (configuration, help, references, samples, …)
  • Compatibility versus installed components (ActiveX, COM, …) or versus custom applications
  • Version of Excel on board (2000, 2002, 2003, 2007, 2010, 32- or 64-bit)
  • Security and Trust settings of Excel (aka Macro Security)
  • Version of Windows (XP, Vista, 7, Server releases, …)
  • Excel running or not
  • Starting and stopping Excel as an automation server
  • Visual feedback
  • Activation success or failure
  • Activation via Excel or via Registry
  • Silent or Verbose execution of the installer
  • First installation or update installation

The above shall all fit nicely in the installation logic and framework of InnoSetup.

Specific Issues

Version of Windows — For providing user feedback, the script makes use of message boxes. Starting Vista, these message boxes have become UI-aware, that is, based on the current DPI setting of the screen. Because of this, messages are automatically reformatted and will wrap in unpredictable positions, while respecting hard carriage-returns embedded in the message’s text, with often horrible results. For that reason, the script can test which version of Windows is available and embed either a space (Vista and above) or a carriage return (for all others) for proper wrapping.

Version of Excel — Starting Excel 2007, a new file format is available for Add-Ins, the .XLAM format. So if you program for different versions of Excel (I do) and you have both formats available, then you can install the appropriate version of the Add-In. Knowing the version of Excel is also important while scanning the system’s Registry for specific keys and settings (e.g. 32- or 64-bit Excel).

Trust Settings — Every release of Excel has particular Trust Settings. These settings affect the way Excel considers your code (whether macros embedded in a workbook or an Add-In) safe and will allow it to run. Trust settings are stored in the Registry and allow you to notify your customer if the actual settings are too restrictive for your Add-In. And if manipulated correctly, also enable them automatically upon need.

Excel running or not — You cannot install an Add-In programmatically while Excel is running because if will void whatever setting you make to the system once Excel is stopped. However, Excel is a formidable partner when it comes to providing information about where to install the Add-In (i.e. the LibraryPath or the UserLibraryPath) which is not available otherwise. So sometimes you need Excel but sometimes you can’t continue if it’s there. For that purpose, you need to be able to detect whether Excel is running or not.

Dependencies — The Add-Ins I develop interface a third-party software by means of specific API. Since this API is version dependent, I need to make sure that I do not install the Add-In on a system that does not have the correct release. While developing, I have also encountered many problems related to standard software components on the target PCs, such as crashes related to outdated VBE6.DLL, missing or unusable ActiveX components (a.o. Common Dialogs). For that reason I check in the script if what I need is available and react upon.

Previous Page | Next Page

December 30, 2008

Popup Menu on a Userform in Excel with VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 8:52 pm
Tags: , , , , , ,

Article reviewed on July 05, 2012 – Contains working project at the end (link).

Ever thought possible to show a popup menu on an MSComCtl.ListView placed on a UserForm within an Excel Add-In (the ones ending with .XLA) using a right-click of the mouse, “just” VBA and no other Win32 function, class or wrapper? No? Then continue reading. If you think it is possible but you never tried or do not know how, read as well.

In my daily struggling with VBA I came finally on the “Golden Tip” that made it all possible. And, this method works on any type of control you place on the UserForm, as long as it can intercept mouse clicks (left, middle or right button is a matter of your choice).

History
I am developing an Add-In for Excel connecting to a database. The Add-In retrieves the data in pure or in aggregated form and lets you place the result as a matrix (records are rows and fields are columns) on a worksheet. The definition of what the Add-In retrieves from the database is defined as ‘query’. The query is also stored in the workbook, in a [very] hidden worksheet.

One of the features of the Add-In is allowing to slice and trim the result of a query by fetching only a few single rows of the resulting (ADODB) RecordSet. The slice and trim is defined as a ‘restriction’. The restrictions are managed via a UserForm which has the ListView with the individual restrictions in it.

Wish
My desire was adding a right-click popup menu letting me establish the logical link between a list item and its predecessor, selecting from this menu the ‘AND’ or the ‘OR’. I did not want to add another button control to avoid overloading the form (I already have 5 of such).

First implementation
After some investigation in Excel’s and MSDN’s help, I bounced into a popup menu provided natively by Excel via a CommandBar objects. I decided to use it. To obtain the functionality of the right-click popup menu on the ListView on my UserForm, I used:

  • The MouseUp Event of the ListView, where I could trap the right click and identify the current selected list item;
  • A CommandBar as the menu container, to be activated via the ‘.ShowPopup‘ method
  • Two CommandBarButtons to be placed ‘on’ the CommandBar, one for the AND and one for the OR
  • Subroutine for dealing with the factual changes, to be called via the ‘.OnAction‘ property of the CommandBarButton

In a popup menu, the 2 command bar buttons act as menu entries.

However, this code was not working. In other words, the popup menu did show, I could click but none of them triggered the execution of the associated subroutine procedures. Without raising any error. I was troubled.

Second and final implementation
On the Internet (which took some time in order to establish the right terms to feed into King Google) I got the hint I needed: instead of adding 2 generic command bar buttons, I had to create 2 private variables as command buttons in the declaration part of the UserForm using the ‘WithEvents’ to allow the buttons to react on my mouse click.

To make this example work, create a UserForm and place a ListView – a ListView ActiveX control 6.0, remember to add a reference in your project to Microsoft Common Controls 6.0 (SP6) – called ‘ListView1’. Somewhere in the code add one or more items to ListView1.

In the Declaration section of the UserForm:

Option Explicit ' Always recommended

Private WithEvents mButton1 As CommandBarButton ' Manages the first popup menu entry
Private WithEvents mButton2 As CommandBarButton ' Manages the second popup menu entry

In the MouseUp method of the ListView:

Private Sub ListView1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                              ByVal x As stdole.OLE_XPOS_PIXELS, _
                              ByVal y As stdole.OLE_YPOS_PIXELS)
On Error Resume Next
    Dim wListItem    As MSComctlLib.ListItem
    Dim wListItemTag As String

    ' We intercept only the 'RightClick' without any button pressed.
    '
    If (Button <> xlSecondaryButton) Or (Shift <> 0) Then GoTo EndSub

    ' Locate the element. We have used the Tooltip to know if this element needs to be modified.
    '
    Set wListItem = lstCustomRestrictions.SelectedItem
    If wListItem Is Nothing Then Set wListItem = lstCustomRestrictions.HitTest(x, y)
    If wListItem Is Nothing Then GoTo EndSub

    ' This line protects the first entry in the ListView.
    '
    If (wListItem.Index = 1) Then GoTo EndSub

    wListItem.Selected = True
    wListItemTag = wListItem.Tag  ' The Tag contains a specially crafted object.

    ' Show the popup menu.
    '
    ShowPopupMenu wListItemTag    ' The 'Tag' indicates 'Time' or 'Dutch'.

EndSub:
    Set wListItem = Nothing
End Sub

The Click event of the 2 buttons declared in the form:

Private Sub mButton1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
On Error Resume Next
    If (Ctrl.State = msoButtonDown) _
    Then
        ' It was down, i.e. 'checked', so we need to uncheck and void.
        '
        SetToolTipText vbNullString
    Else
        ' It was up, i.e. 'unchecked', so we need to check and set.
        '
        SetToolTipText Ctrl.Parameter
    End If
End Sub

Private Sub mButton2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
On Error Resume Next
    If (Ctrl.State = msoButtonDown) _
    Then
        ' It was down, i.e. 'checked', so we need to uncheck and void.
        '
        SetToolTipText vbNullString
    Else
        ' It was up, i.e. 'unchecked', so we need to check and set.
        '
        SetToolTipText Ctrl.Parameter
    End If
End Sub

This is the first custom private procedure (called from the MouseUp subroutine):

Private Sub ShowPopupMenu(pLinkType As String)
On Error Resume Next
    Dim wPopupMenu As Office.CommandBar    ' Used for right-click on ListView1
    Dim wListItem  As MSComctlLib.ListItem

    ' First try to locate this commandbar. If it exists, then we destroy ad make it again.
    ' The name of the menu is not important but should be unique.
    '
    Set wPopupMenu = Application.CommandBars.Item("UserForm1Popup")
    If Not wPopupMenu Is Nothing _
    Then
        wPopupMenu.Delete
        Set wPopupMenu = Nothing
    End If

    ' Create the menu.
    '
    Set wPopupMenu = Application.CommandBars.Add(Name:="UserForm1Popup", _
                                                 Position:=msoBarPopup, _
                                                 Temporary:=True)
    wPopupMenu.Enabled = True

    ' Add the first child menu item to the popup
    '
    Set mButton1 = Nothing
    Set mButton1 = wPopupMenu.Controls.Add(Type:=msoControlButton, ID:=1, _
                                           Parameter:="Time", Temporary:=True)
    With mButton1
        .Caption = "Set Tooltip to current date/time (i.e. 'Now')"
        .Enabled = True
        If (pLinkType = "Time") _
        Then
            .FaceId = 990           ' Show check mark
            .State = msoButtonDown  ' Show pressed
        End If
        .Style = msoButtonIconAndCaption
        .Visible = True
    End With

    ' Add the second child menu item to the popup
    '
    Set mButton2 = Nothing
    Set mButton2 = wPopupMenu.Controls.Add(Type:=msoControlButton, ID:=1, _
                                           Parameter:="Dutch", Temporary:=True)
    With mButton2
        .Caption = "Set Tooltip to 'Dutch Gemini'"
        .Enabled = True
        If (pLinkType = "Dutch") _
        Then
            .FaceId = 990           ' Show check mark
            .State = msoButtonDown  ' Show pressed
        End If
        .Style = msoButtonIconAndCaption
        .Visible = True
    End With

    ' Show as a popup close to the mouse pointer.
    '
    wPopupMenu.ShowPopup

    ' Destroy the internal popup menu.
    '
    If Not wPopupMenu Is Nothing Then wPopupMenu.Delete
    Set mButton2 = Nothing
    Set mButton1 = Nothing
    Set wPopupMenu = Nothing
End Sub

And the second custom private procedure:

Private Sub SetToolTipText(pToolTipType As String)
On Error Resume Next
    Dim wListItem As MSComctlLib.ListItem

    Set wListItem = ListView1.SelectedItem
    If wListItem Is Nothing Then GoTo EndSub

    ' This line protects the first entry in the ListView.
    '
    If (wListItem.Index = 1) Then GoTo EndSub

    wListItem.Tag = pToolTipType
    Select Case pToolTipType
    Case "Time"
        wListItem.TooltipText = VBA.CStr(Now)
    Case "Dutch"
        wListItem.TooltipText = "Dutch Gemini"
    Case Else
        wListItem.TooltipText = ""
    End Select

    wListItem.Selected = False

EndSub:
    Set wListItem = Nothing
End Sub

That’s it.

For convenience I have made this sample project available as an Excel workbook which contains the necessary overhead for making the above work out-of-the-box. It also contains some code dealing with the positioning of the UserForm on multiple-monitor systems. Click here to download a copy of this document.

If you like it then you can make me aware of your appreciation by making a small donation to help supporting my work.

Blog at WordPress.com.