[Solved] Assign cell value to variable in a macro
[Solved] Assign cell value to variable in a macro
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.
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.
Last edited by FTF on Sat May 15, 2021 12:45 am, edited 1 time in total.
Open Office 4.1.7 on Windows 10
Re: Assigning Variables in Macros
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
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.
Code: Select all
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCell = oSheet.getCellrangeByName("C2")
x = oCell.Value
Note that the Value of a cell is its numeric value. If the cell contains text, the Value will be 0.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Assigning Variables in Macros
See the Macros Explained book on this site to learn a lot about macros: https://www.pitonyak.org/oo.php
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Assigning Variables in Macros
If the cell contains a string - text - value, x = oCell.string.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Assigning Variables in Macros
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:
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
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
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
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Assign cell value to variable in a macro
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?
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?
Open Office 4.1.7 on Windows 10
Re: Assign cell value to variable in a macro
Thanks for your input Zizi64. Several times I have seen your posts when
researching a subject and they have helped me.
researching a subject and they have helped me.
Open Office 4.1.7 on Windows 10
Re: Assign cell value to variable in a macro
Thanks for your reply robleyd. It's good to know that I can get help
from people who know what they are doing.
from people who know what they are doing.
Open Office 4.1.7 on Windows 10
Re: Assigning Variables in Macros
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 isI typed that without testing so let's hope I didn't make a mistake.Code: Select all
oSheet = ThisComponent.Sheets.getByName("Sheet1") oCell = oSheet.getCellrangeByName("C2") x = oCell.Value
Note that the Value of a cell is its numeric value. If the cell contains text, the Value will be 0.
Open Office 4.1.7 on Windows 10