ActiveCell.Offset

[WelshTim]

I have a macro derived from Excel which uses ActiveCell.Offset in many places to set variables, control a loop etc. This works fine while running OO Calc 3.2.1 but gives a run time error when being run by a colleague using OO Calc 3.3. There's a thread on this subject already at http://user.services.openoffice.org/en/ ... 20&t=38779 but the solution is so sketchy that I can't make any sense of it. The easiest fix would appear to be to download OO 3.2 but I can't find that anywhere. So........

Is there a simple replacement for VBA lines like:
VariableName = ActiveCell.Offset(0,0)
Do until ActiveCell.Offset(0,0) = ""
ActiveCell.Offset(1,0).Select

Or is there anywhere that I can download version 3.2.1 so that it'll work as is?

Thanks,

Tim.

[Villeroy]

When a helper function to substitute VBA "ActiveCell" is too sketchy I'd strongly recommend to install MS Excel. Be prepared that you will not be able to leave behind that program in the near future.
I move this topic to the macro forum since it has only to do with Excel VBA.

For whatever reason you try to select cells one by one:

Code: Select all

oView = ThisComponent.getCurrentController()
oCell = getActiveCell(oView)
while oCell.getCellByPosition(0,0).getString() <>""
  oCell = getOffsetRange(oCell,1,0,0,0)
  oView.select(oCell)
wend

Both helper functions, getActiveCell and getOffsetRange, are part of my code snippet you linked already.

[WelshTim]

I still can't get the macro to run. I would use Excel as advised but unfortunately, my customer has only installed OO on their users' laptops so that isn't an option. Is there anywhere that I can download OO version 3.2 which does support ActiveCell.Offset in a macro? Then I don't need to change anything.

[Villeroy]

If you don't get the above code running you have a completely different problem not related to OpenOffice.org. I give up.

[Zizi64]

Hi, WelshTim,

I have tried your example code snippet, but it not work in my Go-OO based OxygenOffice3.2.1.

Code: Select all

REM  *****  BASIC  *****
Option VBASupport 1

Sub Main
Dim VariableName as object
	VariableName = ActiveCell.Offset(0,0)
	Do until ActiveCell.Offset(0,0) = ""
	ActiveCell.Offset(1,0).Select
	Loop
End Sub 

Here is the error message:

VBA_OOBasic.png (6.68 KiB) Viewed 21269 times

When I copied the linked OOBasic example subroutine and functions into a new macro module of a spreadsheet file, they work fine for me.
(I created a simple example file for you.)

VBA_OObasic.ods

(12.39 KiB) Downloaded 697 times

[vbakke]

It seems to me this is a very recurring question for which I have been completely unable to find any documentation whatsoever, let alone a good answer.

I came across a solution purely by trial and error using LibreOffice 4.0, but the solution also seem to work for older versions of OpenOffice and LibreOffice.

After having read a lot of comments with a lot of partial suggestions (which I'm sad to say I have found rather hopeless to check out and get to work), I will instead provide all the details needed to replicate the solution.

Create a LibreOffice Calc document called MacroTest.odf.
Inside the document, rename the worksheet named Sheet1 to TestSheet (the tab on the bottom left).

Create a macro by going to Tools / Macros / Organize Macros / LibreOffice Basic.
Important! This macro must be linked to the file, so you can NOT use My Macros.
Under MacroTest / Standard, click on New (button on the right), and accept the name Module1.

Paste the following (read: at the end of this text) code into the editor and save.

Switch back to the worksheet, enter 123 into A1 and then make A1 the active cell by clicking on it.

Switch back to the macro, place the cursor at the top. Now press F5 and click on Run.

Switch back to the worksheet and... see any difference?

If this works / doesn't work for you, or if you find this at all useful, I'm happy to read about it...

Regards,
Vegard Bakke
Norway

--------------------------------

' How to get data from the active cell (where the cursor is) and put it somewhere else inside a given worksheet.

Code: Select all

Option VBASupport 1

Sub CopyFromActiveCell()

; Initialize the variables (not all that neccessary, but it makes for tidy programming)
Dim activecellvalue
Dim activerow as Long
Dim activecolumn as Long

' Specify worksheet with active/current cell
  With Worksheets("TestSheet")

' Assign the active cell's value, row and column to local variables
' Do a test by entering a value in any cell, then make it the active cell, run the macro and watch the magic :-)
    activecellvalue = ActiveCell.Value
    activerow = ActiveCell.Row
    activecolumn = ActiveCell.Column

' Copy the active cell's value to a cell below and to the right of the active cell
    Cells(activerow+1,activecolumn+1) = activecellvalue

' Copy the active cell's value to range B5 x D6
    .Range(Cells(5,2), Cells(6,4)).Value = activecellvalue

  End With
 
End Sub

[Villeroy]

Option VBASupport 1
does not turn on any VBA. It is an option that makes this program's StarBasic interpreter try to translate VBA code. I have no idea why so many precious development hours have been wasted for this close to useless type of VBA translation. May be there was some academic concern. VBASupport has almost no practical use.

Microsoft spent several years to port their own VBA language from Windows to Mac Office. Within this decade you must not expect that any non-MS software will be able to run VBA natively.