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

Question

I use a lot of QueryTables.Add in VBA and it has a ton of parameters. I was looking to "functionalize" my subs for this task and I think I've run into a limit for VBA, though I couldn't find an exact number online, but when I tried to make all parameters optional my editor went red. When removing some, it went green, but I was already considering a way to perhaps make a single optional argument that would perhaps be some sort of Param:Value keypair that I could then split/parse/apply in the sub. This would also allow me to use this in the future for other subs with hopefully less code. I also don't currently manipulate many of the QueryTables params, but I'd like to "futureproof" this sub.

Here is my current Sub Header, note the ' If you try to add that last param, the editor goes red. What I'm looking for is suggestions how to best approach

How to accept a large number of params
How to then utilize them in the code.

Public Sub Query_Web_URL(URLStr As String, WSNameStr As String, Optional AdjustColumnWidth As String, Optional Application As String, Optional BackgroundQuery As String, Optional CommandText As String, Optional CommandType As String, Optional Creator As String, Optional EditWebPage As String, Optional EnableEditing As String, Optional EnableRefresh As String, Optional FetchedRowOverflow As String, Optional FieldNames As String, Optional FillAdjacentFormulas As String, Optional ListObject As String, Optional MaintainConnection As String, _
Optional Parameters As String, Optional Parent As String, Optional PostText As String, Optional PreserveColumnInfo As String, Optional PreserveFormatting As String, Optional QueryType As String, Optional Recordset As String, Optional Refreshing As String, Optional RefreshOnFileOpen As String, Optional RefreshPeriod As String, Optional RefreshStyle As String, Optional ResultRange As String, Optional RobustConnect As String, Optional RowNumbers As String, Optional SaveData As String, Optional SavePassword As String, Optional Sort As String, Optional SourceConnectionFile As String, Optional SourceDataFile As String, Optional TextFileColumnDataTypes As String, Optional TextFileCommaDelimiter As String, Optional TextFileConsecutiveDelimiter As String, _
Optional TextFileDecimalSeparator As String, Optional TextFileFixedColumnWidths As String, Optional TextFileOtherDelimiter As String, Optional TextFileParseType As String, Optional TextFilePlatform As String, Optional TextFilePromptOnRefresh As String, Optional TextFileSemicolonDelimiter As String, Optional TextFileSpaceDelimiter As String, Optional TextFileStartRow As String, Optional TextFileTabDelimiter As String, Optional TextFileTextQualifier As String, Optional TextFileThousandsSeparator As String, Optional TextFileTrailingMinusNumbers As String, _
Optional TextFileVisualLayout As String, Optional WebConsecutiveDelimitersAsOne As String, Optional WebDisableDateRecognition As String, Optional WebDisableRedirections As String, Optional WebFormatting As String, Optional WebPreFormattedTextToColumns As String, Optional WebSelectionType As String, Optional WebSingleBlockTextImport As String, Optional WebTables As String)  ' , Optional WorkbookConnection 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 = xlAllTables
  .WebFormatting = xlWebFormattingAll
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
 End With

End Sub

Update:

I see now, that string could require me to cast certain params as there correct type, so that would be first issue. And the truth is, I don't need currently to pass many params, so my main goal is more so, future proofing so I can add in more params without messing up existing code.

In this regard, my question becomes how can I pass variables as optional, but if not passed, use the default, without doing If variable <> "" Then for each possible variable.

@BrianMStafford do you have an example URL I could look at by any chance? — FreeSoftwareServers, Sep 15 '21 at 17:03
Here's a [link about Types](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-statement). — Brian M Stafford, Sep 15 '21 at 17:05
@BrianMStafford I updated my question, that made me realize how Id have to cast string to correct type all over the place, but more so, how can I use default if variable not passed without a million `If/Then` statements? — FreeSoftwareServers, Sep 15 '21 at 17:12
I think my vba error was not related to number of parama, but character limit, just a guess but I knew that approach was wrong lol — FreeSoftwareServers, Sep 15 '21 at 18:23

score 2 · Accepted Answer · answered Sep 15 '21 at 17:58

2

If I understand your requirements I would suggest the following approach using a Class:

Option Explicit

Private Sub Test()
   Dim Parms As QueryTableParameters
   
   'uses all default values
   Set Parms = New QueryTableParameters
   Query_Web_URL Parms
   
   'change some or all parms
   Set Parms = New QueryTableParameters
   Parms.URLStr = "I changed the default"
   Parms.BackgroundQuery = False
   Query_Web_URL Parms
End Sub

Private Sub Query_Web_URL(ByRef Parms As QueryTableParameters)
   'use the parms as needed
   Debug.Print Parms.BackgroundQuery
   Debug.Print Parms.URLStr
   Debug.Print Parms.WSNameStr
End Sub

This gives the following output:

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

answered Sep 15 '21 at 17:58

Brian M Stafford

8,483
2
16
25

This is a proper answer w/ a great example, thank you! I had gotten on the correct path already, but this is basically exactly what I came to the same conclusion. Thank You! – FreeSoftwareServers Sep 15 '21 at 18:11
Can you take a look at my answer, any critique is welcome! – FreeSoftwareServers Sep 15 '21 at 18:12
Is there a reason you dont use CALL? I always do – FreeSoftwareServers Sep 18 '21 at 17:39
CALL is old-style coding and essentially deprecated. See [this post](https://stackoverflow.com/questions/56504639/call-keyword-deprecated-or-not). – Brian M Stafford Sep 18 '21 at 23:02

score 1 · Answer 2 · answered Sep 15 '21 at 17:03

1

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 ?

answered Sep 15 '21 at 17:03

Thomas Carlton

5,344
10
63
126

I've made a few classes, but generally just for storing keys with multiple string values. I'm unsure how I would implement any of your options without a million `if/then` inside my sub for each variable to see if it was passed, any suggestions? – FreeSoftwareServers Sep 15 '21 at 17:14

score 0 · Answer 3 · answered Sep 15 '21 at 18:10

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

As requested, here is a brief critique. You are on the right path. I would 1) move URLStr and WSNameStr into the class 2) get rid of the global class 3) pass the class as a parm. — Brian M Stafford, Sep 15 '21 at 18:24
@BrianMStafford There is no default value for the URLStr, but I'm assuming all your comments are on the same thought, keeping things together/tidy. — FreeSoftwareServers, Sep 15 '21 at 18:59
Yes, tidy is good! Also, keep in mind the default value may be a null string in many cases. — Brian M Stafford, Sep 15 '21 at 19:08

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

3 Answers3