LotusScript tips

10 fundamental LotusScript tips

Bill Buchan
03.27.2007
Rating: -3.82- (out of 5)

1.      Option Declare

2.      Templates and versions

3.      Application lifecycles

4.      How to code

5.      “Measure twice, cut once”

6.      Extending arrays the easy way

7.      Use the list operator

8.      Logging agents

9.      Code structure

10.  Hiding your code

Fundamental LotusScript Tip #1. Option Declare

Make sure you always use “Option Declare.” It sounds obvious, but there are several determining factors as to why you should always use it. Here are some of the reasons:

  • If you don’t use “Option Declare,” all variables are created at runtime as variants, and this makes fact checking redundant.
  • Data conversion will cost your company 10 times your performance.
  • All your errors will be runtime errors.
  • Also, always remember that you should test often and test early. Use the strength of the compiler to help you.
Fundamental LotusScript Tip #2. Templates and versions

When working with templates and versions in LotusScript in custom code, there are a few things you should always do:

  • Always create templates with new code, which may include databases with an NTF extension or databases with Master Template Name set.
  • Make sure you create a separate version for each copy you’re working on.
  • And always keep your templates and versions in a central repository.

You might say these all sound like good reasons, but why should I use them? There are three major reasons:

1.      Because it makes it much easier to roll back.

2.      It helps with version control.

3.      It makes it simpler to construct test cases with other versions.

Fundamental LotusScript Tip #3. Application lifecycles

When dealing with the lifecycles of your applications, there are a few best practices you should follow:

1.      Always develop in a development sandbox — a non-production environment where each developer keeps his or her working copy of the code base.

2.      Always test in a user acceptance environment.

3.      Pass the template to your Domino administrator to copy.

Here is why you should adhere to these rules:

  • It’s a basic change-control system.
  • It is you doing your part as a development professional.
  • It shakes out hard-coded bugs, such as hard-coded server names, paths and replica IDs.
  • It allows you to run extensive testing without affecting your company’s production.
Fundamental LotusScript Tip #4. How to code

Here are the fundamental how’s and why’s of how to code in LotusScript:

  • Always code for maintenance.
  • Only code for performance if it is required. Make sure you get your code working in general, before you get it working for speed.

Here is why you should follow these LotusScript how-to coding guidelines:

  • The highest cost in development is software maintenance.
  • You make it easier for the person maintaining the application.
  • You could be working on that application!
Fundamental LotusScript Tip #5. “Measure twice, cut once”

“Measure twice, cut one” may sound a bit cliche, but it is a good analogy when you are writing LotusScript code. Here are a couple ways it applies.

  • You should always spend more time thinking about what you are coding and less time doing the actual coding.
  • You should always try to think of two different ways of solving the problem at hand. The best approach is usually a combination of the two, and you should always think about your data model.

This works well because you are spending more time planning and less time doing actual labor.

Fundamental LotusScript Tip #6. Extending arrays the easy way

You should always try to use “ubound” to establish the size of your array. Below you will find an example of it in use.

 
Sub initialize()
        Dim myArray() as String
        redim my Array(0)
        call myExtend (myArray, "Hello Sailor"")
end sub

function myExtend(S() as String, ns as String) as integer
        if (S(ubound(S)) <> "")
then redim preserve  S(ubound(S)+1)
        S (ubound(S)) = ns
        extend = true
end function

If you set up your arrays this way, you won’t need to keep a separate index of the array size; it also automatically trims the code. However, there is a minor drawback to implementing this. It will slow down large arrays, so you’ll need to define the “empty” value. Otherwise, it works great.

Fundamental LotusScript Tip #7. Use the list operator

When you use the list operator, it stores a value with a unique lookup key. It’s a good idea to use the list operator because:

  • It’s easy and fast
  • It’s built right into LotusScript (as long as you’re using version 4.5 or higher)

Here is some example code:

 
Dim WR list as String
WR("Peter") = "Perfect"
WR("Penelope") = "Pitstop"

Print "Peter's last name is: " +WR("Peter")
if not isElement(WR("Dick")) then print "Dick isn't racing!"
forall thisRacer in WR
        Print listtag(thisracer) + " " + thisRacer
end forall
Fundamental LotusScript Tip #8. Logging agents

Another good tip is to log your LotusScript agents, especially if they are scheduled agents. It’s also a good idea if you have a broad range of clients. If you do, you need to log both your client and scheduled agents runtime status.

If you don’t log your agents, chances are that your applications will break. Logging your agents will also help to let you know when your LotusScript agents do break. It’s also a good timing metric for performance testing.

Show caution when logging your agents though — you don’t want to make the logging so slow that it affects application performance!

Fundamental LotusScript Tip #9. Code structure

When dealing with the structure of your LotusScript code, it is usually good to keep it as short as possible. Smaller code sequences are easier to deal with and maintain and are easily reusable. A good rule to go by is if your code is more than a screen’s worth, you should determine if it is possible to break it up.

Some tips when determining your LotusScript code structure:

  • Try to keep your functions/procedures/classes at a manageable size.
  • Think before decomposing problems.
  • Don’t over comment — explain your “why.”
  • Try to use non-intuitive algorithms.
Fundamental LotusScript Tip #10. Hiding your code

When we say we’re “hiding our code,” we’re basically saying that we’re decomposing it so consumers can’t see it. You don’t want anyone to be able to view it, not even yourself.

When you hide your code, you are inferring a logical interface with clear naming. As such, your users and customers can write to the interface but not the code itself. This is good practice because it simplifies the coding experience.

You can easily hide your code by using “private/public” methods in your classes, script libraries, etc.

10 everyday LotusScript tips

Bill Buchan
03.27.2007
Rating: -4.10- (out of 5)

1.      Use error trapping

2.      Use defensive coding

3.      Protect your code

4.      Use NotesDateTime instead of strings

5.      Use DXL as Transport

6.      Use wizard interface in your Notes client

7.      Consuming Web services

8.      Use classes

9.      Use the Evaluate command

10.  Use “trusted servers”

Everyday LotusScript Tip #1. Use error trapping

Error trapping tests a particular condition when running a program. If the program runs into an error, it will then execute a corresponding routine to fix the error. This should always be done. Error handling is not a joke — it should always be handled in a serious manner and should always be mandatory.

There are two ways to go about this:

  • Use a single error handler at the top of your LotusScript code. It is simple to implement, but at times difficult to keep in context.
  • Implement your error handler at the function level. It’s a little bit more work, but it’s also much more granular.
Everyday LotusScript Tip #2. Use defensive coding

While it may seem a bit paranoid, you should practice using defensive LotusScript coding — it will save you time in the long run. Always assume the worst and check all inputs on every function. It doesn’t usually affect performance, but it is a good failsafe.

Here is a good example of defensive LotusScript coding in a typical function.

 
Function mytest (p1as String, p2 as String) as integer
       mytest = false
       if p1 = "" then exit function
       if p2 = "" then exit function
       . . .
       '  Now actually do something!
       . . . .
       mytest = true
end function
Everyday LotusScript Tip #3. Protect your code

When creating commercial applications, it’s a very good idea to hide your code. But, you may be asking how. There are actually two ways you can go about it:

  • Create a template and click on “hide design.” This is easy to do, but it may end up allowing form customization.
  • You could also remove your LotusScript source code from your script libraries. Use the NotesNoteCollection command to find your script design document. Then replace the “$ScriptLib” with a String — “Hello.” This is not the easiest way to go about this process, but your other design elements can be modified as well. (Do not do this on your development copy!)
Everyday LotusScript Tip #4. Use NotesDateTime instead of strings

You should never store date/time values as strings. It is always good practice to use NotesDateTime structures instead and save them.

You might say, sure, but why? Well, you never know how the client will interpret dates. Is it dd/mm/yyyy or mm/dd/yyyy? It also means that views will be able to sort on dates.

Trust me, this is a good tip to practice. This issue comes up more often than you might think.

Everyday LotusScript Tip #5. Use DXL as Transport

A good reason to consider using DXL as Transport stems from a situation where a customer wants to easily send back “log” documents. When this happens, you can use a LotusScript agent to:

  • Pick up all selected documents.
  • Create a memo with a rich-text field.
  • Use DXL to store the documents in the rich-text field.

At the receiving end, this will:

  • Unpack the mail message to a DXL stream.
  • Construct new documents to store the data.

This way, you are transferring data without replication. Below is a sample code of this being implemented.

 
Dim sSession As New NotesSession
Dim dbThis As notesDatabase
Set dbThis = sSession.CurrentDatabase
Dim dc As NotesDocumentCollection
Set dc = dbThis.UnprocessedDocuments
If (dc Is Nothing) Then exit sub
If (dc.count < 1) Then exit sub
Dim doc As NotesDocument
Set doc = dc.GetFirstDocument
While (Not doc Is Nothing)
Dim de As NotesDXLExporter
Set de = sSession.CreateDXLExporter()
Call de.setInput(doc)
Dim dxl As String
dxl = de.Export
' continued overleaf..
Dim dbMail As
New NotesDatabase("", "")
Call dbMail.OpenMail()
Dim docM As NotesDocument
Set docM = dbMail.CreateDocument
Call docM.ReplaceItemValue
("Form", "Memo")
Call docM.ReplaceItemValue
("Recipients", "logs@hadsl.com")
Call docM.ReplaceItemValue
("SendTo", "logs@hadsl.com")
Call docM.ReplaceItemValue
("Subject", "Log Documents")
Dim rt As New NotesRichTextItem
(docM, "Body")
Call rt.AppendText(dxl)
Call docM.Send(False)
Set docM = Nothing
Set de = Nothing
Set doc = dc.GetNextDocument(doc)
Wend
Everyday LotusScript Tip #6. Use a wizard interface in your Notes client

When using a wizard interface with your Lotus Notes client, there are a few steps you should follow:

  • Create a form with a tabbed table.
  • Set the tabs to “1,” “2,” “3,” etc.
  • Select “Switch Rows Programmatically.”
  • Set the “name” field to the name of the table; for example: “RequestTable.”
  • Create a variable on the form with $Name; for example: “$RequestTable.”
  • Have your “forward” and “back” buttons increment/decrement the variable.
Everyday LotusScript Tip #7. Consuming Web services

There are two different ways you can go about consuming Web services. The first is quick and to accomplish it, you should follow these steps:

  • Install Microsoft SOAP on client machines.
  • Write LotusScript to create a Microsoft SOAP object. This is a good option because it is quick and handy when it comes to testing. Unfortunately, it is platform-specific, requires dynamic link libraries on clients, and there is no timeout.

Below is some code that illustrates how to create the Microsoft SOAP object.

 
Dim Client As Variant
Set Client = CreateObject("MSSOAP.SoapClient")
'Initialize connection to the Web Service
Call Client.mssoapinit
("http://localhost/testWS.nsf/Simple?wsdl")
'Call our simple GetEmailAddress
function provided by Web service
Dim result As String
result = Client.getJoke()
'output result to message box
Messagebox result, 48, "Get Joke"

The other approach is a little different. It’s big and robust and uses Stubby. Just point it at a Web service and it produces the code for you.

Some good points about it are that it is multi-platform, scalable and there are no dynamic link libraries. However, it does require you to use more than four lines of code.

Everyday LotusScript Tip #8. Use classes

When developing with LotusScript, it is always a good idea to use classes. Here are some reasons why:

  • Classes help to bundle data and code in one place.
  • They decompose problems into “objects.”
  • They help to write smaller, more focused code.
  • They help define and implement the internal data model.
  • They aid reusability.

Classes have a good design methodology, which leads to Java. But everyone is not used to them and it may take time to sink in. Below you will see some code that implements classes.

 
Class Person
private nName as NotesName
private strUNID as String
sub new(strNewName as string, strNewUNID asString)
me.nnName = new NotesName(strNewName)
me.strUNID = strNewUNID
end sub
public function getName as String
if (me.nnName is nothing) then exit function
getName = nnName.Canonical
end function
public function getUNID as String
getUNID = strUNID
end function
end class
Everyday LotusScript Tip #9. Use the Evaluate command

The Evaluate command allows you to run @Functions within LotusScript. It is sometimes quicker and easier, as it allows you to use your favorite function in certain situations.

An example of it might be:

 
evaluate(|@unique|)

Don’t overuse it though. Loads of LotusScript functions mimic @functions.

Everyday LotusScript Tip #10. Use “trusted servers”

It is good practice to use trusted servers because scheduled agents cannot normally open databases on other servers.

The “trusted servers” field in a Lotus Domino R6 server document’s security section allows servers to trust other servers. By doing this, it allows you to centralize “collection” agents. You also simplify your architecture and limit the number of agents you use. However, it does rely on a fast, reliable network infrastructure.

10 advanced LotusScript tips

Bill Buchan
03.27.2007
Rating: -4.12- (out of 5)

1.      Understand binding

2.      Code for performance

3.      Use lists and classes

4.      Use class inheritance

5.      Use platform-specific LotusScript code with classes

6.      Use version-specific LotusScript code with classes

7.      Use LSI_Info()/GetThreadInfo

8.      Use the execute command

9.      Use advanced logging

10.  Mixing Java and LotusScript

Advanced LotusScript Tip #1. Understand binding

There are two types of binding: early binding and late binding.

Early binding is set by the compiler and works well because it uses type checking, works quickly and is easy to use. An example of early binding might be:

 
Dim S as String

Late binding is set at runtime. It is very flexible, but doesn’t use type checking. Unfortunately, the performance isn’t as good as early binding and you might run into some runtime errors.

 
Dim V as variant
Dim S as new NotesSession
set V = S.CurrentDatabase
print V.getTitle()
Advanced LotusScript Tip #2. Code for performance

When you’re coding for performance, always remember that expensive operations include opening Lotus Notes databases, and views and documents with lots of fields. So, when you’re collecting data, remember to cache views wherever possible and use NotesViewEntry instead of opening documents.

As an example, let’s say you have a Lotus Notes database with 100,000 documents in it. This would take seven hours to actually open every document in the Lotus Notes database, if you don’t code for performance and use views. If you do code for performance, it will only take you 60 minutes to open these using NotesView and only 12 minutes if you use NotesViewEntry!

Advanced LotusScript Tip #3. Use lists and classes

It’s good practice to use LotusScript lists and classes because classes bind complex data and operations. Lists can look these up quickly in memory. For a quick example, here’s how we might extend our Person class:

 
dim People list as Person
dim PeopleByUNID list as Person
Dim P as new Person
("Joe Bloggs/ACME", "010101010201020")
....
set People(P.getName) = P
set PeopleByUNID(P.getUNID) = P
if (isElement(People("Joe Bloggs/ACME"))) then _
Print "Joe's UNID is: " +
People("Joe Bloggs/ACME").getUNID
if (isElement(PeopleByUNID("010101010201020"))) then _
Print "UNID '010101010201020' is: " + _
PeopleByUNID("010101010201020").getName
Advanced LotusScript Tip #4. Use class inheritance

Class inheritance allows us to “Extend” classes to add functionality. For example:

 
class StaffPerson as Person
private strStaffID as String
sub new(strNewPerson
as String, strNewUNID as String)
end sub
public function setStaffNumber(newNo as String)
strStaffID = newNo
end function
public function getStaffNumber as String
getStaffNumber = me.strStaffID
end function
end class
Advanced LotusScript Tip #5. Use platform-specific code with classes
 
Dim s as new NotesSession
Dim mem as variant
select case s.platform
case "Windows/32"
set mem = new getMemW32()
case "AIX"
set mem = new getMemAIX()
case else
Print "Platform not supported"
set mem = nothing
end case
if (not mem is nothing) then
call mem.printMemory()

Class getMem
function getMem() as long
getMem = 0
end function
sub printMemory
print me.getMem()
end sub
end class

Class getMemW32 as getMem
function getMem() as long
getMem = getWindowsMemory()
end function
end class

Class getMemAIX as getMem
function getMem() as long
getMem = getAIXMemory()
end function
end class
Advanced LotusScript Tip #6. Use version-specific code with classes
 
Dim s as new NotesSession
dim vCU as variant
select case s.version
case 5
set vCU = new createUser()
case 6
set vCU = new createUserv6()
case else
Print "Version not supported"
set vCU = nothing
end case
if (not vCU is nothing) then _
call vCU.CreateUser(....)

Class createUser
function createUser(...) as integer
....
end function
end class

Class createUserv6 as createUser
function createUser(...) as integer
....
end function
end class
7. Use LSI_Info()/GetThreadInfo

You can use the LSI_INFO() command to get some runtime information. Be aware though that this information is superceded by the GetThreadInfo command.

If you use GetThreadInfo(11), that will return you the calling class. If you use GetThreadInfo(10), that will return you the function name. And these are just the beginning.

Through error trapping, we can track where we came from. We don’t have to pass lists of parameters to error trapping code. It also prevents coding errors through using the copy and paste method.

Here is an example of this in use, preceded by the calling code:

 
' calling code...
...
ExitFunction:
exit function
errorhandler:
Call RaiseError()
resume exitFunction
end function
Function RaiseError()
Dim thisType As String
Dim es as String
thisType = Typename(Me)
' Not a class, use the calling module instead
If (thisType = "") Then thisType = Getthreadinfo(11)
es = thisType & "::" & Getthreadinfo(10) & ": "
If (Err = 0) Then
es = es + "Manually raised an error"
Else
es = es +
"Run time error: (" + Trim(Str(Err)) + ") " + _
Error$ + " at line: "+ Trim(Str(Erl))
End If
Print es
end function
Advanced LotusScript Tip #8. Use the execute command

By using the execute command, you can run LotusScript from a string. Doing this accommodates version/platform differences at runtime. Here’s an example:

 
Dim executeString as String
executeString = |
print "Hello world"
dim s as new NotesSession
dim db as NotesDatabase
set db = s.currentDatabase
print "Current Database name is: " + db.Title
|
execute (executeString)
Advanced LotusScript Tip #9. Use advanced logging

By using the OpenNTF “OpenLog” solution, you can make simple LotusScript library additions to your code, provide “called from,” “error,” and “line number” functionality. Our system now works on error trap and displays all objects in memory.

Advanced LotusScript Tip #10. Mixing Java and LotusScript

By mixing Java and LotusScript together, you can really get the most out of each scripting language. The trick is to use each language to its strengths. For example, Java is good for Web service, network I/O, and multithreaded operations. LotusScript is the traditional Lotus Notes development language and works in the user interface.

Mixing the two languages together is easy — just call an agent, passing a Lotus Notes document.

You should also know that this works both ways, as you can call Java from LotusScript. This is called LS2J. An example is below:

 
// Create a Script Library of type "Java" called xlib
// containing the following function:
public class calculator {
public int add(int a, int b) { return a + b; }
public int div(int a, int b) { return a / b; }
public int mul(int a, int b) { return a * b; }
public int sub(int a, int b) { return a - b; }
}
Option Public
Use "xlib"
Uselsx "*javacon"
Sub Initialize
Dim mySession As JavaSession
Dim myClass As JavaClass,
calculator As JavaObject, a,b,c As Integer
Set mySession = New JavaSession()
Set myClass = mySession.GetClass("calculator")
Set calculator = myClass.CreateObject()
a = 10
b = 5
c = calculator.mul(a,b)
MessageBox "a * b = " & c
End Sub

LotusScript Lists.

One of the most powerful features of LotusScript is hardly used. Its been around for *ages* (Like, early 4.5 days, boys and girls) and would save you much pain. And yet, when I look at code – they’re not there. Why ? I don’t know.

What are they ?

Lists.

Half of the audience has went “Yeah”, and moved to the next topic. Those who have stayed on – well, brace yourself.

Imagine having an array type “thing” that you don’t have to initialise. That has auto-building indexes so you can look things up – quickly. That maintains itself. And is very, very simple to use!

Its a list.

For instance, lets pick a very trivial problem. We have four people, with first names and last names, and we want to look up the persons last name by their first name. Simple, eh ?

Using an array, you’d create the array, populate it, and every time you wanted to search it, would have to run through each element, checking it. Ouch. When you get into thousands of elements, well, it gets slow.

Lists help you.

For instance.

dim myList list as String

mylist(“Penelope”) = “Pitstop”
myList(“Peter”) = “Perfect”
myList(“Dick”) = “Dastardley”
myList(“Professor”) = “Pat Pending”.

Print “Peters last name is: ” + myList(“Peter”).

This would, of course, produce “Peters last name is: Perfect”.

All together. Slap forehead. Say “Wow!”.

And its as simple as that.

To declare,

dim myvariablename list as whateverTypeYouWant
Just a simple addition of the word list between the variable name and the typename.

Cool. But I can see you asking questions like: Well, how do I check if something is in a list. As it stands (with this rather wacky example), it’d cause a run-time error if you didn’t exactly get the indexing “thing” right. Ouch. Not good.

So use the “IsElement()” function. Like this.

If (IsElement(myList(“Peter”))) then
print “Peters last name is: ” + myList(“Peter”)
else
print “Peter is not on the list. Is he still in the race ?”
end if

Is that cool ?

So – how do you easily print out all members of a list without knowing in advance their values ? Simple again. Use FORALL.

For instance:

Print “Todays starters in the race are:”

forall Racers in myList
Print “Surname: “+ Racers
Print “First Name: ” + listtag(Racers)
end forall
Wow. So we can get the VALUE of the list assignment using forall. But whats this “ListTag” thing ? Its a way of getting the index value back. In this case, “listtag(Racers)” will produce “Penelope” on the first iteration, etc, etc.

Neat ?

So how do I get rid of ONE entry in the list ? Use Erase

erase myList(“Dick”)

To erase all entries in the list, use:

erase myList.

Couple of tips:

  • Lists can get as large as you like. No limits. Well, okay, basically your constrained by the memory in your machine. Dont worry, I’ve used lists with MILLIONS of entries before.
  • List comparisons tend to be case sensitive. Dont use “Option nocase” in declarations, as you can guarantee that some twit will delete it, and break your code. Convert everything to uppercase for the index value instead. Then you know what your doing
  • Caveat: DONT try to dump a list straight onto a field as a multi-element array. You’ll get garbage. Convert it into an array first.
  • Step through this code example in a debugger and expand the list to see whats in it.

Use them twice, and call them your own.

Lists are like a “Green Belt” thing in Lotusscript. They’re not difficult, but I see *lots* of people NOT using them, when I have to audit code. I think to myself “Ouch – this is really bad – and unreliable. This programmer could save himself a *lot* of effort using lists”.

Things like directory comparisons, for instance, you could collect *all* names from both sides of your compare, and compare them in memory, really quickly. You can pre-cache frequently looked up information from views.

This separates the hunt and peck coding brigade from proper code jockeys. Use it. Impress your friends. Speed your code. Make your life easier.

Enjoy,

123 LotusScript Tips/FAQ’s

1.      How to use a range as argument in a custom @function – why cant I define a function as:
myfunc(R As Range)

2.      Allowed property method usage in custom @ functions – I get the message “Only Get Property requests can be serviced…..” messages ?

3.      Calling DLLs from 123 with LotusScript.

4.      Can you access Win32 registry keys in LotusScript?

5.      How do I construct a DLL with exported functions for 123?

6.      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=sheet*totcols*totrows + col*totrows + row
‘Return the cell value
GetVal=Rgs(indx).CellValue
End Function

Anuncios
  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

Anuncios
A %d blogueros les gusta esto: