Unable to run a simple code

macro
common

asked 2019-12-04 19:30:41 +0100

michel.azzi
1 ●1 ●3

updated 2019-12-04 20:07:15 +0100

Lupp
11855 ●32 ●87 ●164

Good day to all, please i am kind of new to libreOffice and i am trying to run a piece of code that i previously tried on Excel and worked. but i cant seem to correct the problem with Libreoffice. The task is simple, i am trying to assign a copy action to a button, but the target cell will be extracted from the value inside another cell ( a picture is attached). the VBA code that worked perfectly in excel and showed me errors in Libreoffice was:

Private Sub CommandButton1_Click()
With Worksheets("Sheet1")
  .Range("K10").Copy Destination:=.Range(.Range("K6").Value)
End With
End Sub

image description

edit retag flag offensive close merge delete

Comments

Your code clearly is "very Excel-VBA". Since I don't feel sure about what you actually meant I cannot edit your post using the tool for posting code without a risk to introduce errors.
Concerning general issues with moving VBA code to LibreOffice, you may read the recent thread https://ask.libreoffice.org/en/questi....

Lupp ( 2019-12-04 19:41:15 +0100 )edit

Well, meanwhile I verified the code and edited the question to make it (as I think) a bit better readable.
It uses typical VBA constructs an syntax and cannot run in native LibreOffice Basic.
However, it was correctly executed under
Option VBAsupport 1 .

Lupp ( 2019-12-04 20:10:03 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2019-12-04 19:38:38 +0100

Opaque
3329 ●1 ●8 ●31

updated 2019-12-04 19:43:17 +0100

Hello,

don't assume all VBA code is working (VBA has limited support in LibreOffice) and at least you would need to add:

Option VBASupport 1 at the beginning of your Basic Modul (i.e. before any Sub or Function statement).

(May be it doesn't work anyway, though you made the setting. I did not test, but without that option it definitely will not)

Hope that helps.

edit flag offensive delete link

add a comment

answered 2019-12-04 20:40:42 +0100

Lupp
11855 ●32 ●87 ●164

After reading my answer to the above linked question you may sudy this attached example.
Along with the VBA code only executable under the mentioned option it contains a native "implementation" of the proceeding:

Sub commandButton2_Click_nativeLibreOfficeBasicWithAPI
theSheet    = ThisComponent.Sheets.getByName("Sheet1")
sourceRange = theSheet.getCellRangeByName("K10")
paramCell   = theSheet.getCellRangeByName("K6")
targetCell  = theSheet.getCellRangeByName(paramCell.String)
theSheet.CopyRange(targetCell.CellAddress, sourceRange.RangeAddress)
End Sub

edit flag offensive delete link

Comments

Good day, thank you for this great direction. i Still have 2 main concerns that i would like to have your help with: 1- in one of the cases, the "sourceRange" is a cell that contains a formula that i need only to copy the answer. what i am facing is this code is copying the formula inside the cell not the answer itself.

the code i am using is this one:

Sub commandButton2_Click_nativeLibreOfficeBasicWithAPI theSheet = ThisComponent.Sheets.getByName("Sheet1") sourceRange = theSheet.getCellRangeByName("K10") paramCell = theSheet.getCellRangeByName("K6") targetCell = theSheet.getCellRangeByName(paramCell.String) theSheet.CopyRange(targetCell.CellAddress, sourceRange.RangeAddress) End Sub

2- i see in the code that we are calling the sheet involved by its name, i think if i duplicate the sheet and change its name, i suppose that the code will not run again because the sheet name is changed. is there anyway to automatically upgrade the code ...(more)

michel.azzi ( 2019-12-06 20:14:27 +0100 )edit

add a comment

Unable to run a simple code

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

Unable to run a simple code edit

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

Unable to run a simple code