Search code examples
excelbasiclibreoffice-calcvba

LibreCalc Equivalent Function to Excel Application.InputBox

I have recently converted to using LibreCalc over MS Excel. I have a number of Excel Macros that I would like to use in LibreCalc, several of which use the Application.InputBox function. I have enabled VBA support in LibreCalc, but unfortunately the function is not recognised. Here's an example of the code - 

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub Project_Data_Sort_By_Date()

Dim x As Long
Dim StartCont As Double
Dim EndCont As Double
Set R = Selection 'Select data range'
RowCnt = R.Rows.Count
colcnt = R.Columns.Count
Set TheTimes = Application.InputBox("Please enter time range: ", "User input", Type:=8) 'Times is corrected full data range of sample period'

In MSExcel, the Application.InputBox function with type=8 would result in an input box appearing, and which would allow the user to enter a range of cells into the input box. With the usual Basic InputBox function, cell ranges can not be input.

Thus, is there an equivalent function in LibreCalc, or is there a different way in which you can manually select a range of cells and assign it to an argument, which can then be called up later in the macro?

Solution

  • You can use the same runtime function in CALC: 

    range = Inputbox("Please enter time range:", "User input")
Times = ThisComponent.Sheets().getByIndex(0).getCellRangeByName(range)

    You can pass range as a string to that function, too: 

    Times = ThisComponent.Sheets().getByIndex(0).getCellRangeByName("B2:C10")