Calc macro guides for a novice

[hribarinho]

Hi all,

I’m not new to programming, but I’m new to LO Basic for Calc. In a nutshell, I would like to build apps like this in LO Calc. I’ve started with different resources to get familiar, but I lack some structured guide to get me through all the basics.

Currently, I struggle with assigning the values of one named range to another and then clear the original range. Can someone suggest a guide that would get me started? For example, I’ve tried help documentation and I can get values from a single cell or write a value, but not for named ranges. I’ve tried the online help but it doesn’t show examples for named ranges.

So my question is twofold:

1. Can anyone suggest a tutorial, maybe by building something more complex?
2. How would I go about my named range manipulation:

• assign values from one named range to another
• clear values of the original named range

Thank you!
seba

[JohnSUN]

Welcome Sebastjan!
This is a good question - it can be answered quickly and briefly, “Look for chapters in Pitonyak’s book that fit the meaning,” or can answer long and in detail, “I use MRI for such purposes, now I’ll show you step by step how it’s done…” and then there is a lot of text and screenshots. Which answer would you prefer?

[mikekaganski]

From our Macro wiki page, you may find our Developer’s guide, which is another good tutorial, having own Spreadsheet related sections, which might interest you.

[hribarinho]

Thank you, I’ve tried now briefly to find the information in Pitonyak’s book and this is what puzzles me about my attempt:

Sub getNamedRangeValues
Dim oRange As Object
oRange = ThisComponent.NamedRanges.getByName("test1")
Print "Named range content: " & oRange.getContent()
End Sub

The above code actually prints to the msg box the sheet name and the range as $B$4:$B$8. I was expecting the actual content. What am I missing? Is the getContent related to the actual cells composing the named range and not to the value of those cells?

[mikekaganski]

As mentioned in the documentation,

The content can be a reference to a cell or cell range or any formula expression.

This is correct: the named range content is its “formula”, not the data contained in the possibly-referenced cells.

[karolus]

the »real« Cell(range) »behind« is

oRange.ReferredCells

from there you may read|write …Value , …String , …Formula etc. for single Cells or …DataArray , …FormulaArray , etc. for a single CellRange.

[hribarinho]

I’m starting to feel pretty dense. I tried iterating like so:

Sub getNamedRangeValues

Dim oRange As Object
oRange = ThisComponent.NamedRanges.getByName("test1")
Print "Named range content: " & oRange.getContent
Dim oCell As Object
For  Each oCell In oRange.ReferredCells
	Print oCell.getValue
Next oCell

End Sub

What I get is the error message for For Each oCell In oRange.ReferredCells saying “Not allowed value or data type. Data type doesn’t match.” – translated from Slovenian

[JohnSUN]

The task of copying data between named ranges could be solved with a code like this:

Sub copyNamedRange(sSourceRange As String, sTargetRange As String)
Dim oNamedRanges As Variant
Dim aSourceAddress As New com.sun.star.table.CellRangeAddress
Dim aTargetAddress As New com.sun.star.table.CellAddress
Dim oSheet As Variant
	oNamedRanges = ThisComponent.NamedRanges
	aSourceAddress = oNamedRanges.getByName(sSourceRange).getReferredCells().getRangeAddress()
	aTargetAddress = oNamedRanges.getByName(sTargetRange).getReferredCells().getCellByPosition(0, 0).getCellAddress()
	oSheet = ThisComponent.getSheets().getByIndex(0)
	oSheet.copyRange(aTargetAddress, aSourceAddress)
End Sub

Sub callSubCopy
	copyNamedRange("sourceRange", "targetRange")
End Sub

[hribarinho]

Works perfectly. I’ll learn from your code.

One more question: I come from VBA where copying is not preferred since it not as lean/fast as setting a range, getting its value and “setting” the target’s range value to that. Is this also a “problem” in LO Basic?

[karolus]

@hribarinho

as setting a range, getting its value and “setting” the target’s range value to that.

the equivalent in the Api is (get|set)(Data|Formula)Array, but you have to make sure that source and target have the same size.

[hribarinho]

Thank you, much appreciated!

[sswcharlie]

This macro looks good for what I need.
How do I modify to have a message to ‘Please enter name of range to transfer’, and a second message ‘Please enter name of range to transfer to’.
List of Named Ranges called ‘RangesAvailable’ Use a dropdown?

Thanks

Charles