Runtime Error - Object Variable Not Set

[OldJack]

When I run the following macro I get a “Runtime Error - Object Variable Not Set”. Can anyone tell me what I am doing wrong?

Sub Print_Scramble_Team_Results
Dim printRange(0) as new com.sun.star.table.CellRangeAddress
Dim oSheet as Object
Dim oSheetIndex as Integer
oSheet =ThisComponent.GetSheets.GetByName (“Team_Results”)
oSheetIndex = oSheetDestination.RangeAddress.Sheet '(THE ERROR OCCURS HERE)
printRange(0).Sheet = oSheetIndex
printRange(0).StartColumn = 4
printRange(0).StartRow = 5
printRange(0).EndColumn = 12
printRange(0).EndRow = 25
oSheet.setPrintAreas(printRange())
End Sub

[Lupp]

@OldJack

oSheetDestination

Where did you create this object?

[sokol92]

and one tip: always use Option Explicit.

[OldJack]

I’m very new at this and borrowed the code from another program. Obviously I haven’t created the oSheetDestination object. Can you help me with the correct syntax for that.
With thanks,
OldJack.

[Lupp]

For what sheet you want to set the print ranges isn’t a question of syntax. You need to decide.
Judging from the last line of your code it should be the sheet named "Team_Results". If so simply delete the "Destination" part from the non-existing variable name “oSheetDestination”.

REM I dont Dim all the variables, and I dont use standard prefixes. 
REM The standard prefix "o" was anyway used wrongly in one place.
Sub setPrintRange_For_Scramble_Team_Results()
Dim printRange(0) as new com.sun.star.table.CellRangeAddress
REM This special Dim statement is indispensable.
REM The method setPrintAreas expects a sequence of SheetCellRange structures.
REM In this case you need only one range in the sequence. It has the index 0.
mySheet = ThisComponent.Sheets.getByName (“Team_Results”)
With printRange(0)
 .Sheet = mySheet.RangeAddress.Sheet
 .StartColumn = 4
 .StartRow = 5
 .EndColumn = 12
 .EndRow = 25
End With
mySheet.setPrintAreas(printRange())
End Sub

By range name your range is E6:M26. (Indices start with 1 in the sheet, but with 0 in Basic/API.)
Using it you can get your sub shorter (and easier to read):

Sub setHardCodedPrintRangeForHardCodedSheet()
Const sheetName = "Team_Results"
Const rangeName = "E6:M26"
sheet = ThisComponent.Sheets.getByName(sheetName)
range = sheet.getCellRangeByName(rangeName)
sheet.setPrintAreas(Array(range.RangeAddress))
End Sub

The Array() function is used here to create a one-element-sequence from the single RangeAddress.

[OldJack]

@Lupp

For what sheet you want to set the print ranges isn’t a question of syntax. You need to decide.
Judging from the last line of your code it should be the sheet named "Team_Results". If so simply delete the "Destination" part from the non-existing variable name “oSheetDestination”. {… OR}
By range name your range is E6:M26. (Indexes start with 1 in the sheet, but with 0 in Basic/API.)
Using it you can get your sub shorter (and easier to read):
{… See Lupp’s post above.}

Thanks for all this. I think I’ve got it figured out. Now I’m struggling with the printer and page set up as the program has several different printouts with different orientations, margins, etc. so you may be hearing from me again. Thank you again for taking the time to help me.
Jack.

{@Lupp edited this post to reduce area usage.}

[OldJack]

Wolfgang,

Thank you for all your help. Could you point me in the direction to find how to set the printer properties using Libre Basic. I find the Libre Office Guide very difficult to access specific topics.

Jack.

[Lupp]

@OldJack

… Could you point me in the direction to find how to set the printer properties using Libre Basic. I find the Libre Office Guide very difficult to access specific topics.

Follow-up questions spoil the structure of this Q&A site, as they cannot be found based on the topics (short version of the question). Please ty to find an already answered question to the wanted effect (Search / magnifying glass) or create a new question.

BTW: To manipulate printer settings by user code may be difficult and error-prone. It can cause conflicts with system properties …
I never did.
The famous Andrew Pitonyak wrote in his “Useful Macro Information”:

5.6. Print Current Document
I played with this and I can print. I stopped trying to figure out how to print an A4 document on my ‘Letter’ printer! I wanted to set this by default but I decided that it is not worth my time for now.
…

To intercept a print job is a different thing, but I also avoid it. If your printer is capable of serving your needs, the default settings should do.
What you actually may need to change/manipulate are the PageStyle properties of the document or (in this case) the sheet. This should be done by UI means wherever possible at all. To write UserCode may be more fun, but should be avoided in such cases.
If you insist: Please tell your reasons.
If you now want to ask “How to create or change a PageStyle for a sheet in Calc by macro?” feel free to create a new question again.

[sokol92]

@Lupp

I dont Dim all the variables, and I dont use standard prefixes.

I don’t use Option Explicit.

Option Compatible
Sub Test
  Dim a as Long
  a=2
  Msgbox "2+2=" & (a+2), MB_ICONQUESTION
  
  ' Wait... 
  Msgbox "2+2=" & (а+2), MB_ICONEXCLAMATION
  
End Sub

[Lupp]

Didn’t get the clue.

[sokol92]

Did you run the macro above?

[Lupp]

I did. But the curtain was down.

[sokol92]

If you add Option Explicit, the result will change.
This is an illustration of my first post in this topic.

[sokol92]

Meet а.

[Lupp]

@sokol92

If you add Option Explicit, the result will change.
This is an illustration of my first post in this topic.

This didn’t occur to me, because I had removed the Option compatible (what I always do if there isn’t an urgent reason to rely on it). Under this condition Basic reported a syntax error, and highlighted the second occurrence of Msgbox "2+2=" & ( Once more now I made the mistake to think the error should be inside the highlighted string …
Well, LibO Basic doesn’t allow for non-plain-latin characters in names.
But even suspicious now concerning a “disallowed character” I remained in error state, and even copied the Msgbox "2+2=" & ( into a spreadsheet to look there for the unicode places. They all were the same as for the first occurrence of the string - of course.
Thus I learned something again what I interim had forgotten - something about myself: Don’t trust Wolfgang.
Answering your next question: No. I will not use Option explicit hence. Something I had learnt also previously: Don’t trust in automatisms.
In a critical case the wrong result of a string comparison based on the played common glyph for a latin small a and a cyrillic small a can cause me to shoot an airplane - if I have a weapon.
BTW; The Latin semicolon and the Greek question mark also have the same glyph in the fonts I know.

[karolus]

OT: The real problem with Basic, (without edgy kyrill letters ect.) is:

Sub Main
a = "2"
b = 2
msgbox( a + b )
msgbox( b + a )

End Sub

And the conclusion is: Dont use Basic

[Lupp]

Did you experience automatic conversion in Calc - or some other spreadsheet software?

@karolus

And the conclusion is:

Don’t use Calc.

I don’t like automatic conversion, and it has disadvantages. But it has also advantages.
From my point of view method overloading and operator overloading are similar and dangerous features, sometimes with the same effects. Will this be abolished for my sake? Or should I shout “Don’t use C++”!

As long as we keep asserting “a date is a number” without addressing the NullDate issue and the time zone, we shouldn’t be too apodictic about … whatever.

[karolus]

Calc manages to return at least the same result in the comparison

=(A1+B1)=(B1+A1)  →True

[karolus]

@Lupp

Don’t use Calc.

But in the domain of spreadsheets we hardly have an alternative that can do it better, you know?