Trouble posting a LAMBDA function

jdellasala

Board Regular
Joined
Dec 11, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
In attempting to post my first official LAMBDA function, at the bottom for the field Function syntax:, I've entered _GSheets(SURL), _GSheets( SURL ), GSheets(SURL), and GSheets( SURL ), but continue to get the message "Please match the requested format".... The sample is SHEETNAME(reference), so I'm not sure what the problem is.

Anyone know?
 

jdellasala

Board Regular
Joined
Dec 11, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
In attempting to post my first official LAMBDA function, at the bottom for the field Function syntax:, I've entered _GSheets(SURL), _GSheets( SURL ), GSheets(SURL), and GSheets( SURL ), but continue to get the message "Please match the requested format".... The sample is SHEETNAME(reference), so I'm not sure what the problem is.

Anyone know?
Additionally, I have two functions that do not require parenthesis, and in fact return an error if parenthesis are used. They return all sheet names in the workbook as a spilled row array, and a spilled list array (just the first LAMBDA with TRANSPOSE but probably more useful), and working on one that will return the sheet names as a HYPERLINKed list - bookmarks all Sheets (Probably _SheetLinks) which will also not need a parameter (probably). Is there anything special to posting those kinds of LAMBDA functions?

In case you're wondering, the Hyperlink lambda uses Macros 4 which can be called from within Excel, however by default after they're working, they will ultimately return #BLOCKED! without changing settings as per Microsoft's Working with Excel 4.0 macros. Clear instructions that work!

(Edit time ran out!)
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,978
1- Please use all capital letters in the function name:
GSHEETS(SURL) or GSHEETS(surl)

2- You can define a Lambda function without parameters or with optional parameters. Still, I don't think you can use the function without parenthesis just like you can't do that with any other Excel function (for example PI()):
Sample with an optional parameter:
Excel Formula:
TESTFUNCTION=LAMBDA([x],4)
or even no parameters:
Excel Formula:
TESTFUNCTION=LAMBDA(4)
Lambda Test
AB
14=LAMBDA(4)()
2
34#CALC!
Sheet1
Cell Formulas
RangeFormula
A1A1=LAMBDA(4)()
B1B1=FORMULATEXT(A1)
A3A3=TESTFUNCTION()
B3B3=TESTFUNCTION

You still have to enter =TESTFUNCTION() by the function definition and =TESTFUNCTION will already return a #CALC! error. Therefore, we set up the function signature field and applied the syntax rules accordingly.
 

jdellasala

Board Regular
Joined
Dec 11, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
1- Please use all capital letters in the function name:
GSHEETS(SURL) or GSHEETS(surl)

2- You can define a Lambda function without parameters or with optional parameters. Still, I don't think you can use the function without parenthesis just like you can't do that with any other Excel function (for example PI()):
Sample with an optional parameter:
Excel Formula:
TESTFUNCTION=LAMBDA([x],4)
or even no parameters:
Excel Formula:
TESTFUNCTION=LAMBDA(4)
Lambda Test
AB
14=LAMBDA(4)()
2
34#CALC!
Sheet1
Cell Formulas
RangeFormula
A1A1=LAMBDA(4)()
B1B1=FORMULATEXT(A1)
A3A3=TESTFUNCTION()
B3B3=TESTFUNCTION

You still have to enter =TESTFUNCTION() by the function definition and =TESTFUNCTION will already return a #CALC! error. Therefore, we set up the function signature field and applied the syntax rules accordingly.
Thanks again for your help. Not sure what issue I was having, but using all upper case and no leading underscore fixed the problem.

As for the LAMBDA with no parameter, I have what I think of as a LAMBDA because it creates a function with only a formula. Specifically

Excel Formula:
SHEETNAMES=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())

I suppose that should be posted in the General Discussion & Other Applications Forum as it doesn't use LAMBDA! I wouldn't even bother posting it except that I found it on a video posted over two years ago, and when I tried to use it, it would work at first, but then return #BLOCKED!. I was pleased to find the fix for that on Microsoft's Support site - Working with Excel 4.0 macros and thought it would be useful to others.

Take care.
 

Forum statistics

Threads
1,181,169
Messages
5,928,509
Members
436,606
Latest member
ppainter1958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top