[Solved] Assign cell value to variable in a macro

Creating a macro - Writing a Script - Using the API

[Solved] Assign cell value to variable in a macro

Postby FTF » Thu May 13, 2021 1:37 am

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.
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
FTF
 
Posts: 5
Joined: Wed May 12, 2021 4:43 pm

Re: Assigning Variables in Macros

Postby FJCC » Thu May 13, 2021 1:55 am

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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8175
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Assigning Variables in Macros

Postby FJCC » Thu May 13, 2021 1:56 am

See the Macros Explained book on this site to learn a lot about macros: https://www.pitonyak.org/oo.php
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8175
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Assigning Variables in Macros

Postby robleyd » Thu May 13, 2021 2:14 am

If the cell contains a string - text - value, x = oCell.string.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 4047
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Assigning Variables in Macros

Postby Zizi64 » Thu May 13, 2021 7:34 am

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   Expand viewCollapse view
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
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
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.
User avatar
Zizi64
Volunteer
 
Posts: 9887
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Assign cell value to variable in a macro

Postby FTF » Fri May 14, 2021 1:43 am

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?
Open Office 4.1.7 on Windows 10
FTF
 
Posts: 5
Joined: Wed May 12, 2021 4:43 pm

Re: Assign cell value to variable in a macro

Postby FTF » Fri May 14, 2021 1:49 am

Thanks for your input Zizi64. Several times I have seen your posts when
researching a subject and they have helped me.
Open Office 4.1.7 on Windows 10
FTF
 
Posts: 5
Joined: Wed May 12, 2021 4:43 pm

Re: Assign cell value to variable in a macro

Postby FTF » Fri May 14, 2021 1:56 am

Thanks for your reply robleyd. It's good to know that I can get help
from people who know what they are doing.
Open Office 4.1.7 on Windows 10
FTF
 
Posts: 5
Joined: Wed May 12, 2021 4:43 pm

Re: Assigning Variables in Macros

Postby FTF » Sat May 15, 2021 12:50 am

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   Expand viewCollapse view
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.
Open Office 4.1.7 on Windows 10
FTF
 
Posts: 5
Joined: Wed May 12, 2021 4:43 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests