Ask Your Question
1

¿Object variable not set?

asked 2016-11-29 01:48:39 +0200

ecarvallo gravatar image

updated 2018-10-08 18:42:31 +0200

endolith gravatar image

I have this error: BASIC runtime error. Object variable not set

And this is the code:

REM  *****  BASIC  *****

Private Sub Average()
dim Total_Marks as Integer
Total_Marks = Application.WorksheetFunction.Average(Sheet1.Range("G" & 31 & ":G" & 42))
ThisWorkbook.Worksheets("Sheet1").Cells(8,45).Value = Average_Marks

End Sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-29 03:19:21 +0200

Ratslinger gravatar image

This macro will calculate the average of cells G31 through G42 on Sheet1 and place the result in G45. The phrase "Average_Marks" will be placed in cell H45:

Sub SetAverage
    dim oFunction as variant
    dim aArgument(0) as variant
    dim oSheet as Object
    dim myRange as Object
    dim TotalCount as Double
'Need service'
    oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
'Sheet to be used'
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
'Range for the function'
    myRange = oSheet.getCellRangeByName( "G31:G42" )
'Set arguement for function'
    aArgument(0)=myRange
'Run & get result of function'
    TotalCount = oFunction.callFunction( "Average", aArgument )
'Place result  by getting location & setting Value'
    myRange = oSheet.GetCellRangeByName("G45")
    myRange.setValue(TotalCount)
'Place wording by getting location & setting String'
    myRange = oSheet.GetCellRangeByName("H45")
    myRange.setString("Average_Marks")
End sub

You may want to review some documentation on macros. You can start with Calc Macros

edit flag offensive delete link more

Comments

I get this error BASIC runtime error. An exception occurred Type: com.sun.star.container.NoSuchElementException Message: . on oSheet = ....

ecarvallo gravatar imageecarvallo ( 2016-11-29 03:55:35 +0200 )edit

That is the sheet name (the name on the TAB at bottom of sheet) on which the cells are present. Did you give the sheet a different name? Works here on Mint 18. Where are you putting the macro? How are you executing it - from a push button or from basic? Tried both ways and it works here. I can the same message if I put in an unknown sheet name.

Ratslinger gravatar imageRatslinger ( 2016-11-29 04:42:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-29 01:48:39 +0200

Seen: 804 times

Last updated: Oct 08 '18