123 LotusScript Tips and FAQs

	123 LotusScript Tips/FAQ's How to use a range as argument in a custom @function - why cant I define a function as: myfunc(R As Range) Allowed property method usage in custom @ functions - I get the message "Only Get Property requests can be serviced....." messages ? Calling DLLs from 123 with LotusScript. Can you access Win32 registry keys in LotusScript? How do I construct a DLL with exported functions for 123? How can I access the value of a cell within a range in a custom @ function - I cant use object methods such as Cell() in a custom @ function.
	1. How to use a range as argument in a custom @function ? The lack of documentation on this has caused a lot of head scratching. For example I want to create a custom @function to count the blank cells in a range: @countblank(A:A1..A:D300) has a corresponding function definition : Function COUNTBLANK(Rg) 123 sheet cells basically contain only two types of contents : STRING and DOUBLE. 123 custom @ functions are meant to accept cell contents and range arguments and they do this via the Variant type. Functions defined in 123 LotusScript can be used as custom @ functions only if the arguments are untyped or typed as Variants. Our example @countblank() is meant to count the total cells in a range would be defined as follows: Function COUNTBLANK(Rg) 'Allowed Rg treated as Variant Function COUNTBLANK(Rg As Range) 'Not Allowed in custom @ function must be untyped or variant. Function COUNTBLANK(Rg As Variant) 'Allowed but same as COUNTBLANK(Rg) custom @ function arguments are assumed to be a Variant. Variants can contain a large number of different argument types e.g. String, Integer, Long, Double and Range etc and the LotusScript function Typename() can be used to read the type of variable held by the variant. e.g. tname$ = Typename(Rg) would return "DOUBLE", "STRING", "LONG", "RANGE" if input as part of a custom @ function. If Rg is a single cell reference then 123 determines the Typename() of the cell value either "STRING" or "DOUBLE". The exception to this is that a blank cell is set to "LONG" (instead of "DOUBLE") and this can be used to detect blank cells. If Rg is a range reference e.g. "A1..A2" or "C:D34..C:E105" or HISTORY (where HISTORY is a valid range) Rg will be stuffed into a VARIANT with Typename() "RANGE". Instead do not specify any input argument types and test the arguments with Typename(A) as in the sample below: Function @COUNTBLANK(Rg) if Typename(Rg)<>"RANGE" Then Exit Function Dim MyRange As Range Set MyRange=Rg 'Note you need to use "Set" to have 123 link the argument to the internal range MyRange
	2. Allowed property/method usage in custom @ functions ? Newcomers to custom @ function design using LotusScript will have noticed the error dialog that warns : "Only Get Property requests can be serviced. No properties can be set and no commands can be executed because the application is in a read-only state. Make sure the application is in a read-write state then try again." This ensures that an @ function does not break the rule that it cannot modify any part of the workbook or workbooks. It can only return a value in the cell or cell formula in which it is located. However, this is annoying when class methods look like they should be OK only to be rejected. For example the Cell method in the code below: Function MYATFUNCTION(Rg) Set cellrange=Rg.Cell(0,0,0) MYATFUNCTION=Rg.Contents End Function This code sequence is not allowed because Cell() is a *method* you must use the Cells *property* instead : Function MYATFUNCTION(Rg) Dim cellrange As Range Dim Rgs As Ranges 'Rgs will be is a collection of all cells in Rg Set Rgs=Rg.Cells 'assuming Rg is a valid range Set elsewhere in the code (not shown) cellrange=Rgs(0) 'index to Rgs is zero numbered and is the top left top sheet cell of a range see MYATFUNCTION=cellrange.Contents End Function See Item 6 how to index a cell in a range using the above technique. As a general rule programmers of custom @ functions can only use 'Properties' of a 123 object and then only 'Get' not 'Set'.
	3. Calling DLLs from 123 with LotusScript. The 'Declare' statement in LotusScript facilitates the calling of external functions within DLLs. Using 'Declare' you can access the windows SDK performing functions not available within LotusScript itself e.g.: read .ini files read/set registry values locate/manipulate windows on the desktop environment generate sounds use the windows common dialog set e.g. file open, file save, color palette You can also access your own compiled 32 bit DLLs using external windows language compilers e.g. Microsoft Visual C, Borland C++, Delphi etc. See Item 5. How do I construct a DLL with exported functions for 123? Below is a copy* of a Lotus LotusScript 123 FAQ article :http://www.lotus.com/ Quote How do I call DLL functions? When you create LotusScript applications for 1-2-3, you are not limited to calling LotusScript procedures. Your LotusScript applications can call any procedures that are compiled in a dynamic-link library (DLL). To call procedures in a DLL, you need to know the following: The name of the DLL The full path for the DLL (if it is not in your default path) The names and parameters for procedures that you want to call *Note* The following example calls a Win32 API function named sndPlaySound that is stored in the DLL file C:\WINDOWS\SYSTEM\WINMM.DLL.This function plays a Windows .WAV file. To use this function in a LotusScript application, first declare the function and then call it from a script. Enter the following statements in (Declarations) for (Globals) if you want to call .WAV files from any script in your application. ' Runtime Dependencies: ' Files and paths: WINMM.DLL must be installed in C:\WINDOWS\SYSTEM ' or somewhere in your current path. The sound file ' OFF2RACE.WAV must be installed in the subdirectory ' C:\WINDOWS\MEDIA. ' Declare a return value to use when you call the DLL ' function in a script. Dim SoundReturnValue As Integer ' Declare the DLL function as a public function in LotusScript. Declare Public Function sndPlaySound Lib "winmm"_ Alias "sndPlaySoundA" _ ( Byval WaveFile As String, Byval theFlags As Long ) _ As Integer ' Declare some of the constants used by parameters of the DLL function. Public Const SND_SYNC = &H0000 ' play synchronously (default) Public Const SND_ASYNC = &H0001 ' play asynchronously Public Const SND_NODEFAULT = &H0002 ' silence (!default) if sound not found Public Const SND_MEMORY = &H0004 ' pszSound points to a memory file Public Const SND_LOOP = &H0008 ' loop the sound until next sndPlaySound Public Const SND_NOSTOP = &H0010 ' don't stop any currently playing sound The following script calls the declared function and specifies a .WAV file to play. Sub TestSoundFiles SoundReturnValue = sndPlaySound( "C:\WINDOWS\MEDIA\OFF2RACE.WAV", SND_SYNC ) End Sub End Quote See also 4. below
	4. Can you access Win32 registry keys in LotusScript? Yes! Yes. Pioneering these requirements have been: http://www.grs.se/notesfaq/canaccess.html http://www.goes.com/~graham/addshort.html Ken Yee's NotesFAQ site Ken Yees site has an item :Can you access Win32 registry keys in LotusScript- that applies equally to 123.
	5. How do I construct a dll with exported functions for 123? Rules to look out for are as follows: must be 32 bit dll be careful of name mangling in exported functions of .cpp source file based dlls. Use of the Microsoft Visual C specific: __declspec( dllexport ) double fnMyFunction(double mynumber) will produce a compiler mangled name such as _FNMYFUNCTIOND8EKK@ which the DECLARE statement will not find! Using export "C" does not entirely remove the name mangling. Instead use a .DEF file and spell out the function names in the EXPORT section. strings that you need to modify in the DLL need to be passed ByVal e.g. Declare Public Function getUpper Lib "mydll" ( Byval myStr As String) As Integer The corresponding function exported from the DLL would be: extern "C" double __stdcall GETUPPER(char* myStr) here modifying the string myStr in the DLL will modify the string in the LotusScript function/sub contrary to what might be expected! Alias for the DLL file name can be used to supply the mangled name e.g.: Declare Public Function getUpper Lib "mydll" Alias "_MYDLL9R8F8G" ( Byval myStr As String) As Integer where _MYDLL9R8F8G is the mangled name found using the DUMPBIN utility in Microsoft Visual C. Better to use a DEF file see above.
	6. How can I access the value of a cell within a range from within a custom @ function As a custom @ function in LotusScript does not allow use of object methods such as Cell() i.e. cellrange = range.Cell(row, column, [sheet]) We are forced to use properties such as Cells see Item 2. Lets say you are given a range as input and you need to get the value of cell with coordinates (S,C,R) relative to the top left cell. ' function returns the value at sheet S from top (+ve down), C columns to right (right +ve), R rows from top (down +ve) Function GetVal(Rng,S,C,R) Print "Typename(Rng)=",Typename(Rng) If Typename(Rng)<>"RANGE" Then Exit Function Dim inRange As Range Set inRange=Rng Dim row As Integer Dim sheet As Integer Dim col As Integer 'Get offsets using Cint integer type converter row=Cint(R) sheet=Cint(S) col=Cint(C) 'Check limits Dim totcols As Integer totcols=inRange.EndColumn-inRange.StartColumn+1 If col>totcols-1 Then Exit Function Dim totrows As Integer totrows=inRange.EndRow-inRange.StartRow+1 If row>totrows-1 Then Exit Function Dim totsheets As Integer totsheets=inRange.EndSheet-inRange.StartSheet+1 If sheet>totsheets-1 Then Exit Function 'Index into range using Ranges collection Dim Rgs As Ranges Set Rgs=inRange.Cells Dim indx As Long indx=sheettotcolstotrows + col*totrows + row 'Return the cell value GetVal=Rgs(indx).CellValue End Function

Quote

How do I call DLL functions?

4. Can you access Win32 registry keys in LotusScript?