Ask Your Question
0

Unable to run a simple code

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

michel.azzi gravatar image

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

Lupp gravatar image

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 gravatar imageLupp ( 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 gravatar imageLupp ( 2019-12-04 20:10:03 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

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

Opaque gravatar image

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 more
0

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

Lupp gravatar image

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 more

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 gravatar imagemichel.azzi ( 2019-12-06 20:14:27 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 28 times

Last updated: Dec 04