hello, im new to macro programminng. I have a calc file named test and 1 sheet in it named alex, in cell A1 to A3 i have some numbers, i would like to write a macro where i get the values of A cells, process them and after that return them to cells B1 to B3.
Any idea how can i do this ?
Thanks in advance
Sorry, but your requirement is not specific enough to propose a good but short solution. What will you do with the numbers in the A-cells? Do they need to be treated independently or merged into a single new numer or three different numbers. Why should the input consist of 3 numbers and the output also be three numbers. It’s really not clear.
Hi,
a very, very brief idea could be you define your own function and call that function in cells B1 to B3. In macro editor define a new modul and a new function according to (for demonstration purposes this function just multiplies the three values given)
Function EXAMPLE ( In1 as long, In2 as long, In3 as long)
EXAMPLE = In1 * In2 * In3
End Function
Now you can add a formula to cell B1 like
=EXAMPLE(A1;A2;A3)
But be aware, considering my comment above, this may not the way to go. You may need to define more than one function or you need a different approach.
Code:
Sub doIt()
alexSheet = ThisComponent.Sheets.getByName("alex")
sourceRg = alexSheet.getCellRangeByName("A1:A3")
targetRg = alexSheet.getCellRangeByName("B1:B3")
theDA = sourceRg.getDataArray()
For j = 0 To 2
theDA(j)(0) = exampleProcess(theDA(j)(0))
Next j
targetRg.setDataArray(theDA)
End Sub
Function exampleProcess(p As Double) As Double
exampleProcess = p*p - p*100 + Sqr(p)
End Function