VBA Sub that Accepts large number of arguments - Best way to parse-CodePudding

I use a lot of QueryTables.Add in VBA and it has a ton of

Here is the class that makes this happen:

Option Explicit

'notice everything is typed correctly so no casting needed
Public URLStr As String
Public WSNameStr As String
Public BackgroundQuery As Boolean

Private Sub Class_Initialize()
   'set default values as needed
   URLStr = "default value"
   WSNameStr = "default value"
   BackgroundQuery = True
End Sub

CodePudding user response：

Having such a huge number of parameters is a complete non sense. There is clearly a problem in your design.

Option 1 : Why don't you create a class ? And instead of passing plenty of parameters, you pass only one instance of the class inside of which you'll have all what you need.

Option 2 : Why don't you put your parameters in a dictionnary ? You will pass only one Parameter to your method that includes several values.

Option 3 : Why don't you use an array simply ?

CodePudding user response：

This is the best approach I could come up, allowing me to use defaults and and add parameters, without having to adjust code really, just the calling subs.

Example Class named clsQueryTables

Private Const xlAllTables = 2 ' All tables
Private Const xlEntirePage = 1 ' Entire page
Private Const xlSpecifiedTables = 3 ' Specified tables
Public WebSelectionType As Integer
Private Sub Class_Initialize()
    WebSelectionType = xlAllTables
End Sub

Module:

Public QueryArgs As New clsQueryTables

Sub Testing()
 Dim URLStr As String, WSNameStr As String
 URLStr = "http"
 WSNameStr = "Test"

 QueryArgs.WebSelectionType = xlAllTables
 Debug.Print "From Caller Sub QueryArgs.WebSelectionType = " & QueryArgs.WebSelectionType
 Call Query_Web_URL(URLStr, WSNameStr)
 
 QueryArgs.WebSelectionType = xlEntirePage
 Debug.Print "From Caller Sub QueryArgs.WebSelectionType = " & QueryArgs.WebSelectionType
 Call Query_Web_URL(URLStr, WSNameStr)
End Sub
Sub TestTwo()
 Dim URLStr As String, WSNameStr As String
 URLStr = "http"
 WSNameStr = "Test"
 Call Query_Web_URL(URLStr, WSNameStr)
End Sub

Public Sub Query_Web_URL(URLStr As String, WSNameStr As String)

 Dim WS As Worksheet
 Call WorksheetCreateDelIfExists(WSNameStr)
 Set WS = Worksheets(WSNameStr)

 With WS.querytables.Add(Connection:="URL;" & URLStr, Destination:=Range("$A$1"))
  .Name = URLStr
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = False
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = QueryArgs.WebSelectionType
  .WebFormatting = xlWebFormattingAll
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
 End With

Debug.Print "From Sub QueryArgs.WebSelectionType = " & QueryArgs.WebSelectionType
End Sub

Debug Results:

From Caller Sub QueryArgs.WebSelectionType = 2
From Sub QueryArgs.WebSelectionType = 2
From Caller Sub QueryArgs.WebSelectionType = 1
From Sub QueryArgs.WebSelectionType = 1
From Sub QueryArgs.WebSelectionType = 2