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&
"’!"&F3),2).
In real life, you may opt to forgo columns C:G and use this single formula:
=VLOOKUP(B3,PULL("’C:\aaa\[Sales"&TEXT(A3,"yyyymmdd")&".xls]
Sheet1’!$A$4:$B$12"),2,FALSE)
Part
3
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 fi 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 fi gure out the index number of the current
worksheet?
Solution: You can fi 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?
 
 
Search JabSto ::




Custom Search