[Solved] Assign cell value to variable in a macro

[FTF]

Does anyone know how to assign a spreadsheet cell value to a variable in a macro?
My operating system is Windows 10. Open Office version is 4.1.7.
I have tried all the standard ways that I know of. For example:
x = oCell.getValue(2,3)
x = oCell.Value(C2)
x = oCell.Value("C2")
X= Range("C2")
X= Range(C2:C2)
The variables are always declared. When I try to run the macro
I get the message "Basic runtime error. Object variable not set"
If I put X=C2, it is ignored. If I put X= a specific number, it works.

[FJCC]

It looks like you are trying to use Visual Basic functions and syntax in OpenOffice. AN OpenOffice Basic version of getting a cell value is

Code: Select all

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCell = oSheet.getCellrangeByName("C2")
x = oCell.Value

I typed that without testing so let's hope I didn't make a mistake.
Note that the Value of a cell is its numeric value. If the cell contains text, the Value will be 0.

[FJCC]

See the Macros Explained book on this site to learn a lot about macros: https://www.pitonyak.org/oo.php

[robleyd]

If the cell contains a string - text - value, x = oCell.string.

[Zizi64]

The Basic (StarBasic, OpenOffice Basic, LibreOffice Basic) is a very simple "dialekt" of the Basic programming language.
There is nothing inside to get a cell of the Calc. But you can call the API functions of the Open/LibreOffice from your Basic code.
API: Application Programming Interface.

These are API functions:

Code: Select all

Thiscomponent // gets the actual document
.Sheets // gets all of the sheets
.getByName() // gets one of the sheets by its name (there is .ByIndex() function too). And more:

.getCellrangeByName()

.Value
.String
.Formula
.Formulalocal

You must study the thousands of the API functions - if you want to work efficiently with the Macros. And you can call the API functions from all of the supported programming languages.

I suggest you to install one of the excellent object inspection tools to list the existing properties and methods of the programming objects like a oSheet, oCell object.
Object inspection tools: MRI, XrayTool

[FTF]

Thanks a million FJCC. You are fantastic. Your code worked in my macro just fine! I spent countless hours researching OpenOffice help
and forum and tried an unbelievable number of combinations of code and syntax. I just never got the combinations you gave me. What
source did you have that allowed you get such an understanding? Was it the book that you recommended?

[FTF]

Thanks for your input Zizi64. Several times I have seen your posts when
researching a subject and they have helped me.

[FTF]

Thanks for your reply robleyd. It's good to know that I can get help
from people who know what they are doing.

[FTF]

It looks like you are trying to use Visual Basic functions and syntax in OpenOffice. AN OpenOffice Basic version of getting a cell value is

Code: Select all

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCell = oSheet.getCellrangeByName("C2")
x = oCell.Value

I typed that without testing so let's hope I didn't make a mistake.
Note that the Value of a cell is its numeric value. If the cell contains text, the Value will be 0.