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

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.

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

Postby osxtra » Wed Oct 09, 2019 6:24 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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!
Last edited by Hagar Delest on Sat Oct 12, 2019 12:10 pm, edited 1 time in total.
Reason: tagged solved
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Wed Oct 09, 2019 6:45 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Wed Oct 09, 2019 7:35 pm

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   Expand viewCollapse view
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
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Wed Oct 09, 2019 8:07 pm

Vartype 10 is undocumented. It is the "missing parameter" vartype.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby osxtra » Wed Oct 09, 2019 9:38 pm

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.
Last edited by osxtra on Wed Oct 09, 2019 10:02 pm, edited 1 time in total.
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Wed Oct 09, 2019 9:53 pm

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...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby osxtra » Wed Oct 09, 2019 9:54 pm

Zizi64 wrote:
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...
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Wed Oct 09, 2019 9:59 pm

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).
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby osxtra » Wed Oct 09, 2019 10:01 pm

Villeroy wrote: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!
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby osxtra » Wed Oct 09, 2019 10:53 pm

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


Sure thing!
Attachments
Optional Parameter Test.ods
Optional Parameter Test
(13.84 KiB) Downloaded 11 times
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Wed Oct 09, 2019 11:30 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby osxtra » Wed Oct 09, 2019 11:59 pm

It's not clearRange that's crashing. It's getSheet. See the sheet I uploaded...
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Thu Oct 10, 2019 6:55 am

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Thu Oct 10, 2019 11:48 am

Zizi64 wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Thu Oct 10, 2019 12:09 pm

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"
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby Villeroy » Thu Oct 10, 2019 12:39 pm

I can't confirm that renaming changes anything with AOO and LO.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Thu Oct 10, 2019 2:21 pm

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby Zizi64 » Thu Oct 10, 2019 2:36 pm

Optional Parameter Test _Zizi64.ods
(12.51 KiB) Downloaded 6 times
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.8; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8389
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: "Object variable not set" when using optional parameters

Postby osxtra » Thu Oct 10, 2019 9:37 pm

Zizi64 wrote: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.
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby osxtra » Thu Oct 10, 2019 9:57 pm

'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   Expand viewCollapse view
' 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   Expand viewCollapse view
' 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   Expand viewCollapse view
' 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]
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: "Object variable not set" when using optional parameters

Postby osxtra » Thu Oct 10, 2019 10:03 pm

Zizi64 wrote:
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!
OpenOffice 4.1.5 on OSX
osxtra
 
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm


Return to LibreOffice

Who is online

Users browsing this forum: No registered users and 1 guest