A clever way to pass lot of ranges from the sheets to VBA and vice versa

**TopXorGuy** · 01-07-2013, 11:39 AM

I have a financial application in Excel that has lots of sheets with lots of data ranges per sheet, I need all this data when the user hits the "Execute" button.

In order to pass this data to VBA (which will pass it in turn to .NET), I have created lots of named ranges, but the VBA turned into massive assignment between named ranges and VBA variables and became less readable. Sometimes I have a list of sheets that have exactly the same structure, but different data, so I start appending a suffix to each named range to match that, which is not ideal!

I am thinking along the lines of having one UDF per sheet type. The arguments of this UDF will be the ranges of this particular sheet, so say I have a sheet called "Sheet of Type T" which has 2 data ranges on it, say "A1:A5", "B10:B20", I will add a UDF to the upper left corner of the sheet (the A1 cell) and the UDF is going to look like "=T(A1:A5,B10:B20)"

The beauty of this UDF that the data will be tracked automatically if the user wanted to move the ranges around, plus auto complete, also, it can return true or false or even a string to tell the user if he/she made a mistake in their selection or the selected range does not match the required data.

When the user hits "Execute" I will loop over the sheets, pick these UDFs and parse them to collect the parameters out of them and assign where the parameters point to VBA variables.

If you reached this far, then thank you! Do you have a better solution or a tip of how I can improve this method or even a better method to pass lots of data back and forth between the sheets and VBA?

**Norie** · 01-07-2013, 11:45 AM

Quick question, why do you need all the data when the user hits Execute?

**TopXorGuy** · 01-07-2013, 11:56 AM

The data are passed to a .NET engine where it is processed and another set of ranges come back. The data are not processed in VBA, the VBA is meant to carry the data and meant to be readable and editable by the user (say the user wants to further process the returned data, or modify the data in VBA before execution).

**PingPing** · 01-07-2013, 01:30 PM

There's a 'Names' collection you could loop through to identify the ranges on each sheet in the entire workbook. See http://msdn.microsoft.com/en-us/libr...ffice.11).aspx.

Also consider 'ParamArray'. ParamArray allows you to pass an arbitrary number of arguments to a procedure (Sub or Function). See http://msdn.microsoft.com/en-us/library/538f81ec.aspx