# Trouble posting a LAMBDA function



## jdellasala

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

jdellasala said:


> 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

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 TestAB14=LAMBDA(4)()234#CALC!Sheet1Cell FormulasRangeFormulaA1A1=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

smozgur said:


> 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 TestAB14=LAMBDA(4)()234#CALC!Sheet1Cell FormulasRangeFormulaA1A1=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.


----------

