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

 

[Bridgford]

So, once the workbook is saved as an xla, I will never be able to add other statistical tables to the worksheet, or edit values, if needed? If such things arose, then I would have to regenerate the xla, then.

Well, thank you both for your help and have an excellent weekend.

Dale

 

[Andrew Poulsom]

So, once the workbook is saved as an xla, I will never be able to add other statistical tables to the worksheet, or edit values, if needed? If such things arose, then I would have to regenerate the xla, then.

No, you just change the IsAddin property to False.

 

[Bridgford]

No, you just change the IsAddin property to False.

OK, now I'm stumped (on something else). Where does the IsAddIn property live?

 

[Bridgford]

Never mind, I found it. I had closed the properties window.

 

[Andrew Poulsom]

It's a property of ThisWorkbook in the Visual Basic Editor.

 

[Bridgford]

Thanks. Sorry about that.

So, since I had some of this written as an Excel workbook, I can just modify the cell formulas to port them to vba, since I imported my statistical table from the workbook into my add-in. Would that be a correct assumption?

 

[Andrew Poulsom]

I don't know what you mean by "I can just modify the cell formulas to port them to vba". You will be writing custom functions won't you?

 

[Bridgford]

I meant that where I had the cell formula:
=VLOOKUP(B5,'Statistical Tables'!AR3:CO3,$J$7)

I could modify it for use in VBA so that it would be:
var=worksheetfunction.VLOOKUP(B5,'Statistical Tables'!AR3:CO3,$J$7)
I am sure the above is not 100% correct, but with some fudging, shouldn't I be able to do this?

 

[Andrew Poulsom]

Yes, but as you say it will need quite a bit of fudging. For example this:

'Statistical Tables'!AR3:CO3

would be:

ThisWorkbook.Worksheets("Statistical Tables").Range("AR3:CO3")

 

[RoryA]

Or use its code name - e.g.:
Sheet1.Range("AR3:CO3")

though obviously it would make the code more readable if you make the code names more descriptive!