Page 1 of 1
[Solved] Assign cell value to variable in a macro
Posted: Thu May 13, 2021 1:37 am
by 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.
Re: Assigning Variables in Macros
Posted: Thu May 13, 2021 1:55 am
by 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.
Re: Assigning Variables in Macros
Posted: Thu May 13, 2021 1:56 am
by FJCC
See the Macros Explained book on this site to learn a lot about macros:
https://www.pitonyak.org/oo.php
Re: Assigning Variables in Macros
Posted: Thu May 13, 2021 2:14 am
by robleyd
If the cell contains a string - text - value, x = oCell.string.
Re: Assigning Variables in Macros
Posted: Thu May 13, 2021 7:34 am
by 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
Re: Assign cell value to variable in a macro
Posted: Fri May 14, 2021 1:43 am
by 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?
Re: Assign cell value to variable in a macro
Posted: Fri May 14, 2021 1:49 am
by FTF
Thanks for your input Zizi64. Several times I have seen your posts when
researching a subject and they have helped me.
Re: Assign cell value to variable in a macro
Posted: Fri May 14, 2021 1:56 am
by FTF
Thanks for your reply robleyd. It's good to know that I can get help
from people who know what they are doing.
Re: Assigning Variables in Macros
Posted: Sat May 15, 2021 12:50 am
by FTF
FJCC wrote: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.