How to Pass More Than 60 Values to a VBA Procedure in WPS Office
Question details
The user needs to pass more than 60 values to a VBA procedure without creating a long list of individual parameters.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Writing or maintaining a VBA macro in WPS Office and reducing a procedure signature with 60 or more arguments.
- Observed behavior
- The procedure needs to accept many values in a maintainable way, such as an array, range, object, or custom data structure.
Open the workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, and identify the Sub or Function that currently has the long parameter list. If the data is already in a worksheet, note the range address you want to pass, such as A1:CB2.
Pass a Worksheet Range or Variant Array to the Procedure
Use this when the 60+ values are already stored in a worksheet range or can be assembled into a single array.
VBA procedures can accept a Variant parameter and receive an entire range’s Value2 array. This removes the need to declare 60 individual ByVal arguments and makes the call much shorter.
Inside the procedure, the data is a 1-based two-dimensional array, so values are addressed by row and column index.
In WPS Spreadsheets, press Alt+F11 or click the Developer tab and select View Code to open the VBA editor.
Locate the procedure and replace the long parameter list with Sub ProcessData(ByVal data As Variant).
In the calling macro, use Call ProcessData(ThisWorkbook.Sheets("Data").Range("A1:CB2").Value2) so the entire range is passed as one argument.
Inside ProcessData, read or assign values using indexes such as data(1, 1) for the first row and first column.
Use a Custom Class or Dictionary Object for Named Values
Use this when the values have distinct meanings and you want readable names instead of positional indexes.
Consolidate Module-Level Variables or Split the Procedure
Use this when the same values are used by several procedures in one module or when one large procedure should be split into smaller steps.
Pass Many Values to a VBA Procedure with WPS Office Spreadsheets
WPS Office Spreadsheets supports VBA macros, so you can replace 60+ individual arguments with a single Range or Variant array, custom class, or Dictionary object. This keeps macros maintainable and compatible with Excel VBA projects.
- 1. Open the VBA editor in WPS Spreadsheets: Click the Developer tab and select View Code, or press Alt+F11, to open the VBA editor.
- 2. Create or open the macro: In the Project Explorer, double-click the module that contains the procedure you want to update.
- 3. Replace the long parameter list: Change the Sub or Function signature to Sub ProcessData(ByVal data As Variant) so it accepts one array or object instead of many arguments.
- 4. Pass the consolidated data: In the calling macro, pass ThisWorkbook.Sheets("Data").Range("A1:CB2").Value2 or an object variable to the procedure.
- 5. Run and test the macro: Click Run > Run Sub/UserForm or press F5 to verify that the procedure reads all values correctly.
Frequently Asked Questions
Can WPS Office run Excel VBA macros that use Range.Value2 arrays?
Yes. WPS Spreadsheets supports VBA macros. Open the VBA editor with Alt+F11, change the procedure to accept a Variant, and pass Range("A1:CB2").Value2 from the calling code.
How do I pass more than 60 values from a worksheet into a VBA Sub without listing each argument?
Declare the Sub with one Variant parameter, for example Sub ProcessData(ByVal data As Variant). Then call it with the range’s Value2 property. The data becomes a two-dimensional array addressed as data(row, column).
What is the difference between passing a Range object and passing a Variant array to a VBA procedure?
A Range object keeps the worksheet reference and methods, while a Variant array created from Range.Value2 is a snapshot of the values. Use the Variant array when you only need the data and want stable values even if the sheet changes.
Can I pass a custom class or Dictionary object to a VBA procedure in WPS Spreadsheets?
Yes. Insert a Class Module from the VBA editor Insert menu and define public properties, or add a reference to Microsoft Scripting Runtime and pass a Dictionary object for named lookups.
Does WPS Spreadsheets support macro-enabled Excel files?
Yes. WPS Spreadsheets can open and save .xlsm macro-enabled workbooks, so you can maintain VBA projects that originally come from Microsoft Excel.
