Array Compartments

[Brad]

Thanks for taking the time to read my question.

I am defining an array at the start of my Function. My problem is telling
Access how many compartments to reserve, it's a floating number. I tried
making a variable but the error msg I'm getting says I need a static number.

Is there a way around this?

Brad


Here is my code. I thought that I could use Excels ability to calculate
Median instead of reprogramming this. If it's harder to do this than
reprogram it, please let me know.

Function GetMedian()

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim objExcel As Object
Dim x As Integer
Dim NumOfRec As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_Results")

NumOfRec = rst.RecordCount





Set objExcel = CreateObject("Excel.Application")

Dim dblData(NumOfRec) As Double

rst.MoveFirst
x = 0
Do Until rst.EOF
dblData(x) = rst!Protein
x = x + 1
Loop

GetMedian = objExcel.WorkSheetFunction.Median(dblData)

rst.Close

Set rst = Nothing
Set objExcel = Nothing

End Function

 

[Michel Walsh]

You should dim without dimension and use Redim:


Dim x() AS Double
...
Redim x( intVar ) AS Double



If you want to keep the values previously in your variable, use


Redim Preserve x( intVar )


as example.

Note that OPTION EXPLICIT DOES NOT COVER YOU on Redim variables, so,
be careful about typos.


You can also use GetRows on your recordset, rather than looping over it and
storing the values in an array:


Dim x() As Variant
x=rst.GetRows()




Vanderghast, Access MVP

 

[Brad]

Very awesome!

Thanks Michel,

Brad

You should dim without dimension and use Redim:


Dim x() AS Double
...
Redim x( intVar ) AS Double



If you want to keep the values previously in your variable, use


Redim Preserve x( intVar )


as example.

Note that OPTION EXPLICIT DOES NOT COVER YOU on Redim variables, so,
be careful about typos.


You can also use GetRows on your recordset, rather than looping over it and
storing the values in an array:


Dim x() As Variant
x=rst.GetRows()




Vanderghast, Access MVP

 

[Tom van Stiphout]

On Thu, 21 Aug 2008 16:21:09 -0400, "Michel Walsh"

When using Redim it is very cool to also use its counterpart: Erase.
See help file.

-Tom.
Microsoft Access MVP

You should dim without dimension and use Redim:


Dim x() AS Double
...
Redim x( intVar ) AS Double



If you want to keep the values previously in your variable, use


Redim Preserve x( intVar )


as example.

Note that OPTION EXPLICIT DOES NOT COVER YOU on Redim variables, so,
be careful about typos.


You can also use GetRows on your recordset, rather than looping over it and
storing the values in an array:


Dim x() As Variant
x=rst.GetRows()




Vanderghast, Access MVP

<clip>