Set a range with more than 1 parameter as variable

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Viviany Souza
Posts: 3
Joined: Wed May 29, 2019 11:00 pm

Set a range with more than 1 parameter as variable

Post by Viviany Souza »

Hello

I'm writing a code, where I want to add a range. I want this range to depend on variables for columns and rows as well. My problem is to write the interval column with variav.

Code: Select all

    Dim LI1
    Dim LF1
    Dim xh
    
   'Anel 01
         For i = 1 To NLoops Step 1 
         
             LI1 = 21
             LF1 = LI1 + NumTRECHO1 - 1
         
             xh = 23 + (11*(i-1))
    
             oSheet.getCellByPosition(xh, 20+NumTRECHO1).setFormula( "=SUM(M + ("xh")" & LI1 & ":M + ("xh")" & LF1 & ")")
         
         Next i
OpenOffice 3.1 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Set a range with more than 1 parameter as variable

Post by Lupp »

Inserted by editing:
Sorry. I missed that this was your first post to the forum. Therefore:
First of all a welcome!

(My original post:)
First of all:
This is not a topic fitting under "General discussion". The appropriate branch is eother "Calc" or "Macros".
You should tell clearly what you want to achieve. Most likely user code is not needed for it.
If at all user code shold be an appropriate means regarding your goals, you again should tell what you want instead of posting dysfunctional code. Analysing this kind of code just to be able to reversely find out what was intended is an annoying waste of time.
If you post code you should consider that spreading it sparse over a canvas will not make it understandable. Clearly structured, but compact, is better, and most relevant again: Comment on your code to make your intentions clear.
If you yourself don't feel sure about understanding the details sufficiently, never pack a lot of steps into one line of code. It aggravats design and understanding as well.
Look at this now. (It's not "a solution".)

Code: Select all

Dim LI1, LF1, xh REM Types? 
actRow = 20 + myParameter REM Does not depend on the control (Loop-) variable i.
LI1 = 21 REM If LI1 is functionally a constant, you should handle it this way.
LF1 = LI1 + myParameter - 1 REM Again independent of the control variable.
REM For the constant 21 you introduced a (non-speaking) name.
REM For the constants 23, 11, and 20 you didn't. Why?

For i = 1 To numLoops
  xh_i = 23 + (11*(i-1)) 
    REM First time i is used. I added the "_i" to the name to emphasise this. Creates 
    REM the sequence xh_i = 23, 34, 45, ...  used for API column indices. Is this what you want? 
  myCell = oSheet.getCellByPosition(xh_i, actRow)
    REM Assuming actRow is 55 you get the cells X55, AI55, AT55 ... Is this what you want?
  REM You seem to try to do something every  11th column the same row. What sense in it?
  myFormula_i = 
  REM "=SUM(M + ("xh")" & LI1 & ":M + ("xh")" & LF1 & ")" Doesn't tell me clearly what's intended.
  REM ***** ==== YOU should tell me. ==== *****
  myCell.setFormula(myFormula_i)
Next i
Last edited by Lupp on Tue Jun 11, 2019 5:15 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Set a range with more than 1 parameter as variable

Post by Lupp »

To allow a formula in Calc to be designed in one place (cell), and after a 'Copy' was pasted elswhere to also work there as expected, first of all the means of absolute vs. relative addressing exists.
If you need to reference variable ranges on parameter positions of functions depending on the cell where your formula is working in, you have at hand the standard functions INDIRECT() and OFFSET(). INDIRECT will mostly need to cooperate with ADDRESS() e.g. or with more elaborate constructs returning a range address as a string. Very rarely necessary to write user code for it. But even if you have to, it's not a bad idea to test your ideas concerning the composition of addresses in Calc cells.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply