[Solved] "Object variable not set" with optional parameters

[osxtra]

LibreOffice 6.3.2.2, OSX 10.13.6 ("High Sierra")

In a LibreOffice macro, is there any way to specify a default parameter if one is not explicitly passed when invoking a function, and the parameter in the function declaration is optional?

I have two "global" variables (Private Variants) in my module, docObj and docObj2

docObj is a reference to the main document, i.e, ThisComponent

docObj2 is a reference to a second document which the macro opens, i.e., stardesktop.LoadComponentFromURL(convertToURL("/path/to/the/2nd/document"), "_blank", 0, Array())

I have a function that returns a sheet object, but since I have two document objects, it needs to know which one to use, defaulting to the main document:

Code: Select all

Function getSheet(sheetName, Optional documentObject As Variant)
  If IsEmpty(documentObject) Then
    documentObject = docObj
  End If
  
  getSheet = documentObject.getSheets().getByName(sheetName)
End Function

Either:
docSheet = getSheet("Sheet1")
or
docSheet = getSheet("Sheet1", docObj)
works as expected, returing the correct sheet object.

However, I have other routines that do things like clearing ranges, and they are dependent upon getSheet:

Code: Select all

Sub clearRange(sheetName As String, sheetRange As String, Optional clearFlags As Integer, Optional documentObject As Variant)
	' Clear everything if not specified
	If IsEmpty(clearFlags) Then
		clearFlags = 1023
	End If

	' Clear the range
	getSheet(sheetName, documentObject).getCellRangeByName(sheetRange).clearContents(clearFlags)
End Sub

Since documentObject is optional in clearRange's definition, I expected the IsEmpty part of getSheet to handle referencing the proper document object.

Of course, if I was trying to work on the second document, I'd need to include it in the call:
clearRange("Sheet1", "A2:G7", , docObj2)
but, since I'm mostly working with the the primary document, thought this would work just as well:
clearRange("Sheet1", "A2:G7")

The first call works fine, but the second results in an "Object variable not set" error.

Inspecting documentObject:
When clearRange is called, its value is "<missing parameter>"
When clearRange calls getSheet, its value becomes Null

Sure, I could have code in every routine that calls getSheet to make sure I have passed it an object, but thought that making parameters optional meant I wouldn't have to explicitly specify them when calling the function.

Have fiddled with Option Explicit but having it or not doesn't seem to affect anything.

Is there any way around this, without going back and explicitly adding the desired document object into every function call?

Please advise. Thanks!

[Villeroy]

If you don't like Basic, I would recommend Python as macro langauge.
Basic variables distinguish between IsNull, IsEmpty, Is Nothing and IsMissing. The latter one is exclusively for missing parameters.

[Zizi64]

Optional documentObject As Variant)

Why Variant? The documentObject is an object type parameter, isn't?
Maybe you passed an another type of variables to the function...

When you use the type Variant, you can check the type of the passed parameter by usage the varType command. See it in my sample file:
viewtopic.php?f=20&t=99530#p478769

Code: Select all

Function ConvertFromUnix(UnixDate as variant) as variant

 Dim NumericValue as double
			print varType(UnixDate)
	select case varType(UnixDate)

		case 0 ' empty
			ConvertFromUnix = "The source cell is empty"
		case 2 to 5 'numbers
			ConvertFromUnix = UnixDate/86400000+25569
		case 7 'date
			ConvertFromUnix = "This is a date"					
		case 8  'string
			NumericValue = val(UnixDate)
			ConvertFromUnix = NumericValue/86400000+25569
		case 9  'object ???
			ConvertFromUnix = "This is an Object"
		case 11 'boolean
			ConvertFromUnix = "This is a boolean velue"
	end select


end function

[Villeroy]

Vartype 10 is undocumented. It is the "missing parameter" vartype.

[osxtra]

Thanks, Villeroy, am comfortable using Basic, and familiar with the "Is" functions.

Could use a better editor (just having bookmarks would be a great start), but that's a different topic.

All this started by trying to save some typing.

The issue is I can't get far enough into getSheet to use any "Is" functions, as it's crashing upon invocation, due to unexpected behavior of "Optional" parameters that may or may not be passed to it. (These "Optional" parameters do not act as expected. Or, at least, not as I expect them to act.)

I'm trying to have an optional parameter to one function be used in subsequent function calls, even if the paramater wasn't passed to the originating function.

The only function that should "care" about the optional parameter is the downstream one that actually needs it.

In this case, both clearRange and getSheet specify documentObject as an optional parameter.

If clearRange calls getSheet, but clearRange is called without specifying a document object, I would expect that code in getSheet should select the default one as needed. (Even though optional, clearRange does ultimately need the object, and getSheet should be allowed to take care of providing it).

This is accomplished by the "If" at the top of getSheet, which checks for presence of said documentObject, then setting it to the default one as needed (or not, if one was passed to it) before in turn calling documentObject.getSheets().getByName(sheetName)

So, by itself, getSheet("Sheet1") without passing docObj or docObj2 to it works fine, and getSheet("Sheet1", docObj) also works as expected.

I should be able to run clearRange("Sheet1", "A2:G7"), and when it invokes getSheet(sheetName, documentObject) to determine which document object it should use, getSheet should return the correct document object and allow the range to clear, even though no documentObject was passed to clearRange as it was specified as being optional.

What actually happens is that when clearRange("Sheet1", "A2:G7") is called without having the optional documentObject passed to it, and it in turn runs getSheet(sheetName, documentObject), documentObject does not exist, causing the invocation of getSheet to crash.

clearRange should not care that documentObject exists, per se. That's getSheet's job.

So, if function 1 specifies an optional parameter, and that optional parameter is not passed to it, but function 1 then calls function 2 invoking that same optional parameter, then function 2 should not complain about said missing parameter until it actually tries to use it.

I had been explicitly passing the desired document object to all functions, but since I'm mostly working with the primary document, thought it would make the code look a little less verbose by not explicitly specifying it everywhere and having the getSheet function take care if making sure the object existed.

As things stand, it looks I'll either have to revert to explicitly passing the document object down the chain in every function, or have code in every function that might need to pass an optional parameter to another function check and ensure said object exist before calling the downstream function. Ugh.

[Villeroy]

StarBasic is not a good programming language. Nevertheless your initial problem can be solved with if isMissing(blah) then ... or with if vartype(blah)=10 then...

[osxtra]

Why Variant? The documentObject is an object type parameter, isn't?

I was declaring things (sheet, range, etc.) as Object, but Andrew Pitonyak's book OpenOffice.org Macros Explained (3.3.10, Object Variables) suggested it would be better to use Variant for LibreOffice internals.

I took "internals" to mean things like a document object's getCellRangeByName method.

If that thought was incorrect, I can certainly go back to using Object...

[Zizi64]

If that thought was incorrect, I can certainly go back to using Object...

I do not know if it is incorrect or not, because I do not know: WHAT you passed to the function.

Please upload a prepared sample file with the embedded macro (icluded the procedure or sub that calling the problematic function).

[osxtra]

StarBasic is not a good programming language. Nevertheless your initial problem can be solved with if isMissing(blah) then ... or with if vartype(blah)=10 then...

I do have such a test in getSheet, but it's being called by clearRange, and since I did not explicitly pass the supposed optional document object parameter to clearRange, getSheet crashes at invocation rather than allowing the test to supply an object as needed.

If there's something I'm missing here, I'm all ears!

[osxtra]

Please upload a prepared sample file with the embedded macro (icluded the procedure or sub that calling the problematic function).

Sure thing!

[Villeroy]

You call clearRange with 2 string arguments, test for missing ClearFlags without handling the missing documentObject. documentObject remains unset. You have to handle this condition before you pass it over to the next routine.

[osxtra]

It's not clearRange that's crashing. It's getSheet. See the sheet I uploaded...

[Zizi64]

The "getsheet" is a reserved name of the API. Do not use it for name of your own routine.

Try to change all of the names "getsheet" to "mygetsheet" in your code.

Install and use one of the existing object inspection tools: XrayTool or MRI.
The tool can show you the names of the properties, procedures, interfaces of a programming object.

[Villeroy]

The "getsheet" is a reserved name of the API. Do not use it for name of your own routine.

http://www.openoffice.org/api/docs/comm ... dex-7.html
getSheets() and getSpreadsheet() are defined in the API. However, there is no technical reason why I should not use those names.
x = getSpreadSheet() is some routine in your programming language whereas every API function belongs to some object as in
x = obj.getSpreadsheet()
What you should avoid are the names that belong to your programming language, for instance now, mid, int, str which are type declarations or function names in the StarBasic language.

[Zizi64]

getSheets() and getSpreadsheet() are defined in the API. However, there is no technical reason why I should not use those names.

I do not know how and why it is happened, but the code works for me (in LibreOffice) after modifying the function name to "mygetsheet"

[Villeroy]

I can't confirm that renaming changes anything with AOO and LO.

[Zizi64]

Oh, sorry!

There is a useful macro module installed in my MyMacros library ( created by you, Villeroy!) copied from this place:
viewtopic.php?f=21&t=2762

And that contains a function with same name:
Function getSheet(byVal vSheet)
that may caused the conflict in the previous cases.

[Zizi64]

Optional Parameter Test _Zizi64.ods

(12.51 KiB) Downloaded 328 times

[osxtra]

The "getsheet" is a reserved name of the API. Do not use it for name of your own routine.

I am already using Xray; it has been most helpful. Have found getSheets (in ScTableSheetsObj), but not getSheet. Then again, there are many, many objects, perhaps I just haven't run across it yet.

That's not really the name I use anyway, had just chosen it for illustrative purposes in this post.

But thanks for the tip, Xray is a great tool! Hopefully others will make use of it as well.

[osxtra]

'k, after some thought, no sense continuing to beat this dead horse.

I thought I was working toward making a feature request for the basic runtime's function parser, which would be able to defer existence of optional objects as parameters in function calls, so long as a secondary "downstream" function took care of actually acquiring the object.

But then I realized that instead of tediously having every "downstream" routine that might touch that object test for its existence and instantiate as needed, I could simply have a function which performed the test, which would return either whatever object had been passed to it, or, if one was not passed, a default object (also instantiating the default if that had not yet been done).

If any particular sub or function needs to refer to the object multiple times, sure, I would declare it once so the reference can be called upon at will in the routine.

Otherwise, just wrap the reference in the "produce my object as needed" function anywhere it might be invoked, and chain from there.

i.e., instead of doing it in two steps:

Code: Select all

' Declare object in every function that might refer to it
documentObject = checkDocObj(documentObject)

' Use object
getSheet(sheetName, documentObject).getCellRangeByName(cellRange).clearContents(clearFlags)

it only needs to take one:

Code: Select all

' Get object as needed and use it
getSheet(sheetName, checkDocObj(documentObject)).getCellRangeByName(cellRange).clearContents(clearFlags)

Employing this method allows one to abstract the function code away from any particular document object, which will come in handy if you've got a project that uses multiple documents.

Here's one such example, which will open a second document and copy data from it into the primary document. It's not doing very much, but a more complicated macro with scads of object calls would really benefit from this wrapping technique.

Thanks for all the comments!

Code: Select all

' Declare constants
Const varTypeIsMissing = 10
Const varTypeIsNotInitialized = 0

' Declare global vars / objects available to this module
Private defDoc As Variant
Private pasteArgs(5) As New com.sun.star.beans.PropertyValue
Private unoDispatcher

' Copy data from one calc doc to another
Sub sCopyDataBetweenDocs
	' Declare local vars
	Dim ctlFrom As Variant
	Dim ctlTo As Variant
	Dim otherDoc As Variant
	Dim frameFrom As Variant
	Dim frameTo As Variant
  
	' Make sure our private vars are instantiated
	initPrivateVars
  
	' Open the second document, setting the arguement inside convertToURL as needed
	otherDoc = stardesktop.LoadComponentFromURL(convertToURL("/path/to/my/second/document.ods"), "_blank", 0, Array())
  
	' Set the document controllers
	ctlFrom = otherDoc.CurrentController
 	ctlTo = defDoc.CurrentController
 	frameFrom = ctlFrom.Frame
 	frameTo = ctlTo.Frame
  
	' Select the source range, explicitly specifying the document object
	ctlFrom.Select(getSheet("SourceSheet", otherDoc).getCellRangeByName("A2:F10"))

	' Copy source range to the clipboard using the "From" controller's frame
	unoDispatcher.executeDispatch(ctlFrom.Frame, ".uno:Copy", "", 0, Array())
  
	' Select the target range, using the default document object
	ctlTo.Select(getSheet("TargetSheet").getCellRangeByName("C4"))

	' Paste formats, numbers(values), and strings using the "To" controller's frame
	unoDispatcher.executeDispatch(ctlTo.Frame, ".uno:InsertContents", "", 0, pasteArgs())

	' Close the second document without saving
	otherDoc.close(False)
  
	' Deselect the pasted data
	unoDispatcher.executeDispatch(ctlTo.Frame, ".uno:Deselect", "", 0, array())
End Sub

' VARIANT: Return a document's sheet object
Function getSheet(sheetName As String, Optional optDoc As Variant) As Variant
	' Return the sheet object
	getSheet = checkDoc(optDoc).getSheets().getByName(sheetName)
End Function

' VARIANT:  Return a document object (parameter needs to be OPTIONAL or it will crash if not supplied, is empty, or is uninitialized)
Function checkDoc(Optional optDoc As Variant) As Variant
	' Check to see that an object was passed
	If varIsMissing(optDoc) Then
    	' It was not.  Check to see if the default document object has been instantiated
		If privateVarsNeedInit(defDoc) Then
			' It has not.  Populate it along with any other private vars this module may need to access
			initPrivateVars
		End If

    	' Set the passed object to the default document object
    	optDoc = defDoc
	End If

	' The return document object will either be the one that was passed to this function,
 	'   or the default document object
 	checkDoc = optDoc
End Function

'BOOLEAN: Returns true if passed var is missing
Function varIsMissing(Optional vVar) As Boolean
	varIsMissing = IIf(VarType(vVar) = varTypeIsMissing, True, False)
End Function

' BOOLEAN: Return true if an object is not initialized
Function privateVarsNeedInit(Optional vVar As Variant) As Boolean
	privateVarsNeedInit = IIf(VarType(vVar) = varTypeIsNotInitialized, True, False)
End Function

' Initialize private (i.e., global to this module) vars
Sub initPrivateVars
	' Populate any Private vars you need accessible anywhere in the module
	defDoc = ThisComponent
  
	unoDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

	' Set paste argusments array
	pasteArgs(0).Name = "Flags"
	pasteArgs(0).Value = "STV"
	pasteArgs(1).Name = "FormulaCommand"
	pasteArgs(1).Value = 0
	pasteArgs(2).Name = "SkipEmptyCells"
	pasteArgs(2).Value = False
	pasteArgs(3).Name = "Transpose"
	pasteArgs(3).Value = False
	pasteArgs(4).Name = "AsLink"
	pasteArgs(4).Value = False
	pasteArgs(5).Name = "MoveMode"
	pasteArgs(5).Value = 4

  ' Keep adding whatever other globals you might need
End Sub

[/code]

[osxtra]

Optional Parameter Test _Zizi64.ods

Didn't look at this code until after my last post, but yes, that's exactly what I finally went with, after accepting that optional parameters didn't act the way I wanted them to.

Thanks again!