Syntax error when passing variable args in macro

Question

I have a macro for libreoffice that takes in 2 arguments.

A file path
Variable args that are sheet names to look for.

I iterate over the document pertaining to the first arg and get the sheet that matches the first variable arg. I then convert that sheet to CSV.



Sub ExportToCsv(URL as String, ParamArray sheetNames() As Variant)
  Dim saveParams(1) as New com.sun.star.beans.PropertyValue
  saveParams(0).Name = "FilterName"
  saveParams(0).Value = "Text - txt - csv (StarCalc)"
  saveParams(1).Name = "FilterOptions"
  saveParams(1).Value = "44,34,0,1,1" ' 44=comma, 34=double-quote

  GlobalScope.BasicLibraries.loadLibrary("Tools")
  URL = ConvertToURL(URL)
  document = StarDesktop.loadComponentFromUrl(URL, "_blank", 0,  Array())

  baseName = Tools.Strings.GetFileNameWithoutExtension(document.GetURL(), "/")
  directory = Tools.Strings.DirectoryNameoutofPath(document.GetURL(), "/")

  sheets = document.Sheets
  sheetCount = sheets.Count
  Dim x as Integer
  Dim requiredSheetIndex as Integer
  For x = 0 to sheetCount -1
    sheet = sheets.getByIndex(x)
    sheet.isVisible = True
    For i = LBound(sheetNames) To UBound(sheetNames)
      If StrComp(sheet.Name, sheetNames(i), vbTextCompare) = 0 Then
        requiredSheetIndex = x
      End If
    Next
  Next

  currentSheet = document.GetCurrentController.GetActiveSheet()
  sheet = sheets(requiredSheetIndex)
  document.GetCurrentController.SetActiveSheet(sheet)
  filename = directory + "/" + baseName + ".csv"
  fileURL = convertToURL(Filename)
  document.StoreToURL(fileURL, saveParams())
  document.close(True)
End Sub

Eg. ExportToCsv(<path>, 'Data'). Suppose the document has 4 sheets with the 3rd sheet as DATA, this sheet should be converted to CSV.

Previously I used to give the sheet idnex directly into the macro and it worked perfectly. But requirements changed and I have to pass in an array of possible names to match on. Hence the variable args.

But now I am getting a syntax error(Screenshot attached). I cant figure out what is wrong here.

Don't you just need Sub ExportToCsv(URL as String, sheetNames As Variant) ? Or because you're on libreoffice at least get rid of the brackets for sheetNames() (not sure what the ParamArray is for either). — jamheadart
– jamheadart, Commented May 19, 2020 at 12:25
@jamheadart I am not well versed with vba. I was using this as a reference - https://stackoverflow.com/questions/14716385/variable-argument-list-with-visual-basic — leoOrion
– leoOrion, Commented May 19, 2020 at 12:32

Brian M Stafford · Accepted Answer · 2020-05-19 13:15:24Z

3

I believe it is complaining about the ParamArray keyword. After a little digging, I discovered you need to include:

option compatible

at the top of your module. For more information, you can refer to this link.

answered May 19, 2020 at 13:15

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

leoOrion Over a year ago

This worked. Thanks. I am still facing issues in other areas but the compilation has succeeded now.

Collectives™ on Stack Overflow

Syntax error when passing variable args in macro

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related