Microsoft Office Tutorials and References
In Depth Information
IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORK-SHEET
Cell E3 contains the worksheet name in the external workbook.
Cell F3 contains the range name in the external workbook.
Cell G3 builds an external VLOOKUP using =VLOOKUP(B3,PULL("’"&D3&E3&
In real life, you may opt to forgo columns C:G and use this single formula:
Figure 132. Harlan Grove’s custom PULL function can replicate INDIRECT
for external workbooks.
Gotcha: Be careful with PULL . The function actually opens a new instance of
Excel, opens the external workbook, and then builds an array using a cell from
the external workbook. This works ﬁ ne for a few cells, but it would take a very
long time to calculate 10,000 formulas, each containing a PULL function.
Summary: You can use the custom PULL function to extend the INDIRECT
concept to closed external workbooks.
IN VBA, DETERMINE THE NUMBER OF
THE ACTIVE WORKSHEET
Challenge: You want to refer to the worksheet two sheets to the right of the
active worksheet in VBA. How can you ﬁ gure out the index number of the current
Solution: You can ﬁ gure out the index number of the current worksheet by
using ActiveSheet.Index .
Figure 133. How can a macro tell that you are on worksheet 2?