Code Changes

In most cases, you need to make some changes to the VBA code that was written for a standard workbook to make it suitable for an Add-in. This is particularly true if you reference data within your Add-in workbook. Most Excel programmers write code that assumes that the workbook is the active workbook and that the worksheet is the active sheet. Nothing is active in an Add-in, so your code must explicitly reference the Add-in workbook and worksheet. For example, in Chapters 13 and 15, the code assumed that it was dealing with the active workbook, using statements like the following:

With Range("Database") Set rngData = .Rows(2) Call LoadRecord scbNavigator.Value = 2 scbNavigator.Max = .Rows.Count End With

This code only works if the worksheet containing the name Database is active. In your Add-in code, you need to include a reference to the workbook and worksheet. You could say:

With Workbooks("Addins.xlsm").Sheets("Data").Range("Database")

A more useful way to refer to the workbook containing the code is to use the ThisWorkbook property of the Application object that refers to the workbook containing the code. This makes the code much more flexible; you can save the workbook under any filename and the code still works:

With ThisWorkbook.Sheets("Data").Range("Database")

You can also use the object name for the sheet that you see in the Project Explorer:

With Sheet1.Range("Database")

You can edit both the workbook's programmatic name and the sheet's programmatic name in the Properties window. If you change the sheet's programmatic name, you must also change your code. If you change the workbook's programmatic name, you can use the new name if you wish, but ThisWorkbook remains a valid reference, because it is a property of the Application object and a member of <globals>.

If you want to be able to ignore the sheet name to allow the name Database to exist on any sheet, you can use the following construction:

With ThisWorkbook.Names("Database").RefersToRange

0 0

Post a comment