Save lookup data in template (.xlt)

[Bill James]

Is it possible to store lookup data in a .xlt template file and access the
sheet from with the VBA code stored in the .xlt file itself?

I created a .xlt template with one worksheet and a toolbar with one button.
This template contains data along with some macros that reference that data.
Unfortunately, the macro ends with an error when run from a workbook without
the referenced worksheet. Is it possible to reference the worksheet
contained within the .xlt template?

 

[Dave Peterson]

When you store the code/data in a .xlt, then every workbook that's based on this
template will contain the code/data.

Have you thought of separating your code/data and putting it into an addin.
Then the user could load the addin when they need the macros.

It might make it easier for you if you ever have to update the code or data--who
knows how many workbooks could have created using the .xlt template workbook.

But in either case (.xlt or .xla), you could refer to a worksheet named "Sheet1"
in the workbook that contains the code with something like:

msgbox thisworkbook.worksheets("sheet1").range("b99").value

ThisWorkbook is the workbook that owns the code.

 

[Bill James]

Thanks Dave for the tip. I ended up referencing the workbook directly
(requiring it to be opened) and referencing the data from VBA using:

Application.WorksheetFunction.VLookup(myIndex,
Workbooks("Royalties.xls").Sheets("Royalties").UsedRange, 4, False)

The data needs to be modifiable by the user, so I thought it would be best
to keep it in a workbook. The user will need to open the workbook to run
code contained within the workbook.

I was trying to avoid writing a full add-in, but the night is still young
....