Enabling Excel 4.0 macros and their use in a LAMBDA function

[jdellasala]

I recently found the 2 year old video Excel Formula to List All Sheet Tab Names and include Hyperlinks which included a sample file. The solution created a Named Range called SheetNames that used the formula

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())

However, both the sample file and a new file I created worked at first, but would eventually return #BLOCKED!. The problem was that Excel 4 Macros are blocked by default in current versions of Excel. I found the fix to that problem at the Microsoft Support site Working with Excel 4.0 macros. which then allowed the formula

=TRANSPOSE(SheetNames)

to provide a dynamically updated spilled array of Sheet Names which as demonstrated in the video could then be used to create hyperlinks to the appropriate sheet referencing the spilled array values.

Short version of the fix, go to File -> Options -> Trust Center -> [Trust Center Settings] -> Macro Settings -> check the Enable Excel 4.0 macros when VBA macros are enabled box, recommended settings under Macro Settings is Disable VBA macros with notification, then select File Block Settings on the left, and make sure Excel 4 MacroSheets and Add-in Files is checked in the Open column, and under Open behavior for selected file types, select Open selected file types in Protected View and allow editing. (not short, but as short as possible!)

As the video was at least two years old, it was made before LAMBDA was introduced, and well before BYROW was introduced, so I thought it would be easy to create a LAMBDA function that used SheetNames to create a spilled array of hyperlinks to actual sheet names. That solution has eluded me!

Side note - the "&T(NOW())" part of SheetNames returns nothing and is only there to force an update to the array. The downside is that it executes the entire function with every change in the Workbook.

One thing I have learned in attempting to create the LAMBDA is that while Excel 4 macros work in a Named Range, attempting to use them in a formula or LAMBDA just returns a message box "That function isn't valid."!

A LAMBDA function returning a dynamically updated spilled array of hyperlinked worksheet names would certainly be a valuable tool.

 

[JvdV]

There are issues with using HYPERLINK() function in iteration and with arrays in general. I also noticed more people found out during the past year or so. See this link for example. You won't be able to make this iterate through a given array without the given array being actually a written array in your sheet. To showcase what I meant I tried:

1. Create a new named function, e.g.: 'SheetNames' which refers to:

=TOCOL(GET.WORKBOOK(1))

2. Saved my workbook as 'xlsm', e.g.: 'Book1.xlsm'
3. In a new worksheet in cell A1 I added:

=SheetNames

4. In that same sheet in cell B1 I added:

=HYPERLINK(A1#&"!A1")

Note that you may skip step 3 and reference 'SheetNames' directly in step 4, but you'd end up with all hyperlinks working and referencing the very first sheet. Also note that you don't need to iterate the sheets nomore now using LAMBDA() helper functionality.

 

[jdellasala]

There are issues with using HYPERLINK() function in iteration and with arrays in general. I also noticed more people found out during the past year or so. See this link for example. You won't be able to make this iterate through a given array without the given array being actually a written array in your sheet. To showcase what I meant I tried:

1. Create a new named function, e.g.: 'SheetNames' which refers to:

=TOCOL(GET.WORKBOOK(1))

2. Saved my workbook as 'xlsm', e.g.: 'Book1.xlsm'
3. In a new worksheet in cell A1 I added:

=SheetNames

4. In that same sheet in cell B1 I added:

=HYPERLINK(A1#&"!A1")

Note that you may skip step 3 and reference 'SheetNames' directly in step 4, but you'd end up with all hyperlinks working and referencing the very first sheet. Also note that you don't need to iterate the sheets nomore now using LAMBDA() helper functionality.

Maybe it just isn't possible. Even in the sample file from the video, she first lists SheetNames, and then HYPERLINKS to it in a separate cell using generic "Go to sheet" for the hyperlink display name. I was hoping someone could figure out how to work it out. Thanks for the info!

 

[JvdV]

Maybe it just isn't possible. Even in the sample file from the video, she first lists SheetNames, and then HYPERLINKS to it in a separate cell using generic "Go to sheet" for the hyperlink display name. I was hoping someone could figure out how to work it out. Thanks for the info!

But the above comment does work and returns a full dynamic list of clickable hyperlinks to each sheet. Note I left the 2nd parameter empty since using this parameter will mess with the outcome.

 

[jdellasala]

But the above comment does work and returns a full dynamic list of clickable hyperlinks to each sheet. Note I left the 2nd parameter empty since using this parameter will mess with the outcome.

Very nice! I didn't even try it before. It does need one small adaptation to adjust for sheet names with spaces by adding a single quote immediately after the filename and one at the end of the string:

=SUBSTITUTE(SheetNames,"]","]'")&"'"

Also, I found that when I added or renamed a sheet, it didn't take, sometimes even after a save. I used the trick in the Video for it to always update by adding "&T(NOW())" to the end of the original:

=TOCOL(GET.WORKBOOK(1))&T(NOW())

Otherwise it seems to work great! the output from SheetNames can always be hidden.

Nice work though!

 

[smozgur]

@jdellasala: Although the adaptation for sheet names with spaces and making the function work volatile with T(NOW()) trick nicely in post #5, I switched post #2 as the marked solution to this question since it contains the base solution and is also a good explanation to provide better help to future readers. Just FYI.

 

[jdellasala]

@jdellasala: Although the adaptation for sheet names with spaces and making the function work volatile with T(NOW()) trick nicely in post #5, I switched post #2 as the marked solution to this question since it contains the base solution and is also a good explanation to provide better help to future readers. Just FYI.

I happened to look at this again, and while using only one Defined Name formula instead of two, and TOCOL may be shorter than TRANSPOSE, the formula

=HYPERLINK(A1#&"!A1")

doesn't work, and SheetNames doesn't easily update - which is why I kept T(NOW()) in the formula, although I will admit it isn't needed.
Populating Cell A1 with SheetNames is unneeded. What's needed is

=HYPERLINK("#'"&SheetNames&"'!A1")

The formula is missing # preceding the [Filename] Reference, and single quotes around the entire Sheet Name which are not required IF there are no spaces in the Sheet Name, but don't hurt if there aren't.
See for yourself

Book1
	A	B	C	D	E
1	[Book1]Sheet1		[Book1]Sheet1!A1		#'[Book1]Sheet1'!A1
2	[Book1]Sheet2		[Book1]Sheet2!A1		#'[Book1]Sheet2'!A1
3	[Book1]Sheet 3		[Book1]Sheet 3!A1		#'[Book1]Sheet 3'!A1
4	[Book1]Sheet5		[Book1]Sheet5!A1		#'[Book1]Sheet5'!A1
5	[Book1]Sheet4		[Book1]Sheet4!A1		#'[Book1]Sheet4'!A1
6	[Book1]Sheet6		[Book1]Sheet6!A1		#'[Book1]Sheet6'!A1
7
8	SheetNames=TOCOL(GET.WORKBOOK(1))
Sheet2

Cell Formulas
Range	Formula
A1:A6	A1	=SheetNames
C1:C6	C1	=HYPERLINK(A1#&"!A1")
E1:E6	E1	=HYPERLINK("#'"&SheetNames&"'!A1")
Dynamic array formulas.

.
Just sayin - not the solution that works, and not to the question. I'm pretty convinced that until MS addresses with issues HYPERLINK has with arrays, there is no answer to my original question - how to get the display text incorporated into the LAMBDA. It just doesn't work. The only way I got it to work was to use the LAMBDA to get the link targe, but then have a second column for the Display Name and only use Cell References in the HYPERLINK formula

Book1
	E	F	G
1	#'[Book1]Sheet1'!A1	This is Sheet1	This is Sheet1
2	#'[Book1]Sheet2'!A1	This is Sheet2	This is Sheet2
3	#'[Book1]Sheet 3'!A1	This is Sheet 3	This is Sheet 3
4	#'[Book1]Sheet5'!A1	This is Sheet5	This is Sheet5
5	#'[Book1]Sheet4'!A1	This is Sheet4	This is Sheet4
6	#'[Book1]Sheet6'!A1	This is Sheet6	This is Sheet6
Sheet2

Cell Formulas
Range	Formula
E1:E6	E1	="#'"&SheetNames&"'!A1"
G1:G6	G1	=HYPERLINK(E1,F1)
Dynamic array formulas.

But that's BORING!
Then again, maybe it should have been posted in the LAMBDA group, but I don't know if any of this REALLY qualifies as a LAMBDA - even when functions are incorporated!