Variable number of arguments in ParamArray ArgList()

excel paramarray vba
Excel VBA guy picture Excel VBA guy · Jun 4, 2009 · Viewed 12.8k times · Source

If I want to pass a number of values for the ParamArray arglist via an array, how do I do it? From what I've read so far, on VBA, it appears that I need to explicitly list the values that I want to pass. But what if there are potentially different numbers of values to pass, so I do not know in advance how many I'll want to pass to the function? Is there not some way of using an array (a one-dimensional array) with a variable dimension?

Answer

barrowc picture barrowc · Jun 5, 2009

There are several ways to achieve that:

  • dimension the array to be larger than you require, keep a count of how many items actually get added and then Redim Preserve back to the correct size
  • use a Collection object instead (as a normal parameter rather than a ParamArray)

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More