Static Arrays

[Bridgford]

Hi,

I am trying to create some statistical functions as an Excel add-in. These functions need to reference values from statistical tables. I would much rather put these tables into the vba code as static arrays, but I am stumped as to how to accomplish this. The statistical tables would be two dimensional arrays and would be static.

How does vba want the values in a two dimensional array entered? Specifically, the first of the tables is 26 rows by 50 columns. When dimensioning the array, does vba expect rows, then columns, or vice versa?

Assuming I am successful in this portion, I will have questions on how to pull specific values from the array analogous to vlookup or hlookup, if anyone wants to expound on those.

Any insight is greatly appreciated.

Thanks,

Dale

 

[RoryA]

What kind of add-in? xla or dll?
If it's an xla you could simply store them on a worksheet.

 

[Andrew Poulsom]

Why don't you put the values on a worksheet in your Add-In. You can name the range if you want.

 

[Bridgford]

Thanks for your replies. I was intending to create an xla add-in, but I would prefer to not have anything on a worksheet.

Thanks,

Dale

 

[RoryA]

Can I ask why?

 

[Bridgford]

Ultimately, I don't know who will end up using it and I would rather not leave the tables exposed in the workbook. Perhaps it is shortsighted, but that is where my thinking is.

Since it has started to be kind of obscure, it is becoming a matter of principal and now I want to understand how two dimensional static arrays work in vba. Discussions I have found tend to be basic, and the one reference I found, I lost the page and can't relocate it.

In short, I'm sort of stubborn.

Thanks,

Dale

 

[RoryA]

Each to their own, but since the user can't see the sheets, it seems odd.

You also cannot declare a constant array of any sort in VBA. You would have to declare a variable and then populate it in a loop, hence my suggestion of a worksheet as it allows you to read all the values in in one go.

 

[Bridgford]

First, thanks to you both, as you each have weighed in on several occasions with useful advice.

I was unaware that the sheet would be unseen. That is different. Going the worksheet route, would it be more efficient to do the vlookup/hlookup worksheet function or read the table in as a range?

Do I have to do anything special to the sheet so that it doesn't show up?

Thanks again,

Dale

 

[RoryA]

For a table that small, I doubt you'd see any difference either way. I would probably read from the range.
When you save the workbook as an add-in, it will automatically become hidden (and cannot be unhidden without altering the IsAddin property)

 

[Andrew Poulsom]

You will only be able to access the worksheet while the workbook is an xls. It will be hidden when the workbook is saved as an xla. I would name the range (Insert|Name|Define) and use the INDEX and MATCH worksheetfunctions in VBA.