This is a new version (2.0) of my 'First snippet' as the previous one
didn't always work as it should. I've tested it many times and it seems
to work well in all possible cases I could imagine. I hope it will help
some newbies like myself to better understand how some basic things work
in OOo-Basic.
I shall very much appreciate any suggestions you may give for a more
elegant or better writing of the macro.
Regards,
Ennio.
<?xml version="1.0"?>
<!--
$RCSfile: $
last change: $Revision: $ $Author: $ $Date: $
(c)2003 by the copyright holders listed with the author-tags.
If no explicit copyright holder is mentioned with a certain author,
the author him-/herself is the copyright holder. All rights reserved.
Public Documentation License Notice:
The contents of this Documentation are subject to the
Public Documentation License Version 1.0 (the "License");
you may only use this Documentation if you comply with
the terms of this License. A copy of the License is
available at http://www.openoffice.org/licenses/PDL.html
The Original Documentation can be found in the CVS archives
of openoffice.org at the place specified by RCSfile: in this header.
The Initial Writer(s) of the Original Documentation are listed
with the author-tags below.
The Contributor(s) are listed with the author-tags below
without the marker for being an initial author.
All Rights Reserved.
-->
<snippet language="OOBasic" application="Calc">
<keywords>
<keyword>macro</keyword>
<keyword>named range</keyword>
<keyword>delete/create named range</keyword>
<keyword>last used row</keyword>
<keyword>cell containing last balance</keyword>
<keyword>erase named range</keyword>
<keyword>create named range</keyword>
</keywords>
<authors>
<author id="ennio_sr" initial="true" email="[EMAIL PROTECTED]">Ennio-Sr</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Andrew Douglas Pitonyak</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Marc Santhoff</author>
<author id="" initial="false" email="[EMAIL PROTECTED]">Sasa Kelesevic</author>
</authors>
<question heading="erase-create_range_name">Reassign named range to a cell in last used row
<p>How to determine last row used and set a named range for a cell in that row</p>
<p>GregChi <[EMAIL PROTECTED]>; asked a similar question on [email protected] </p>
<p>and received workarounds more than a direct answer. I had a similar problem and after</p>
<p>reading Andrew Pitonyak's "Useful Macro Information for OOo" and asking a few questions</p>
<p>on [email protected], wrote a macro which does the job. It considers col. F as the one </p>
<p>containing a formula to determine current account balance (i.e. previous balance , plus credit,</p>
<p>minus debit). Col. headings (A to F) are: Date, Value_date, Description, Debit, Credit, Balance).</p>
<p></p>
<p>Once you have copy pasted the code in your Calc file, you can well assign the macro</p>
<p>(_0_create_last_bal) to the Event/Opening of document so that it is run when the file</p>
<p> is opened. A second macro (create_last_bal) mentioned in the dialog box, does the same</p>
<p>as it just calls the previous one.</p>
</question>
<answer>
<listing>sub _0_create_last_bal
' ----------------------------
' On error exit
On Error goto Label
' define variables
Dim oDoc As Object ' The spreadsheet we are working on
Dim oSheet As Object ' The sheet (usually first one)
Dim oCell As Object ' The particular cell we select
Dim oRange ' The created range
Dim oRanges ' All named ranges
Dim sName$ ' Name of the named range to create
msg$ = "I' m ready to accept new records: for each new one copy formula " +_
"in last balance cell (or run macro 'create_new_rec')."
oDoc=ThisComponent
ocell=ThisComponent.CurrentSelection
oRanges = ThisComponent.NamedRanges
sName$="last_bal"
' Here we verify whether there is already a range with that name and,
' in the affirmative, erase it so that the new one can be created:
If oRanges.hasByName(sName$) Then
oRanges.getByName(sName$)
oRanges.removeByName(sName$)
End If
' We go to the last cell on the desired column
odoc = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("[EMAIL PROTECTED] com.sun.star.frame.DispatchHelper}")
Dim args1(0) as new [EMAIL PROTECTED] com.sun.star.beans.PropertyValue}
' We save the file to make sure what is displayed is read correctly
' SEEMS NOT NECESSARY dispatcher.executeDispatch(oDoc, ".uno:Save", "", 0, array())
args1(0).Name = "ToPoint"
args1(0).Value = "$F$6"
dispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(oDoc, ".uno:GoDownToEndOfData", "", 0, args1())
' and check whether its value is zero
ocell=ThisComponent.CurrentSelection ' gets value of new current cell
vlc = oCell.getValue() ' get value of selected cell (last cell)
if vlc = 0 then
dispatcher.executeDispatch(oDoc, ".uno:Cut", "", 0, args1())
dispatcher.executeDispatch(oDoc, ".uno:GoUp", "", 0, args1())
' we determine the row number of the selected cell:
' numbering starts from 0, so we need add 1:
r$ = oCell.CellAddress.row+1
xcl$ = "Sheet1.$F$"+r$ ' ref to col F may be changed
else
' This is a special case, so we need add 2
r$ = oCell.CellAddress.row+2
xcl$ = "Sheet1.$F$"+r$ ' ref to col F may be changed
End if
' we set our named range name:
Dim oCellAddress As new [EMAIL PROTECTED] com.sun.star.table.CellAddress}
oCellAddress.Sheet = 0 ' The first sheet
oRanges.addNewByName(sName$,xcl$,oCellAddress,0)
' We arrange for copying the formula from last used row to the next one
dispatcher.executeDispatch(oDoc, ".uno:Copy", "", 0, Args1())
Dim args2(1) as new [EMAIL PROTECTED] com.sun.star.beans.PropertyValue}
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
dispatcher.executeDispatch(oDoc, ".uno:GoDown", "", 0, args2())
dispatcher.executeDispatch(oDoc, ".uno:Paste", "", 0, Args2())
dispatcher.executeDispatch(oDoc, ".uno:GoToStartOfRow", "", 0, args2())
Print msg$
Exit sub
Label:
print "Error!"
Exit Sub
End Sub
Rem ###################################################################
Sub create_new_rec
' we call the previous macro:
sub _0_create_last_bal
End Sub
' #####################################################################
</listing>
</answer>
<versions>
<version number="1.1.4" status="tested"/>
<version number="1.9.82 beta" status="tested"/>
</versions>
<operating-systems>
<operating-system name="Linux"/>
</operating-systems>
<changelog>
<change author-id="ennio_sr" date="2005-06-24">Version 2.0
I discovered some flaws in the initial version (on some circumstances "last_bal" was created on the last but one row). So it was necessary to re-write the code. The file structure is very simple (as said above). You can put this formula in cell F6:
=IF(ISBLANK(B6);0;F5-D6+E6).
</change>
</changelog>
</snippet>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
