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

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" <(E-Mail Removed)> wrote in message
news:9D6496A3-2E4D-43AA-AB6A-(E-Mail Removed)...
> 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
>

Very awesome!

Thanks Michel,

Brad

"Michel Walsh" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:9D6496A3-2E4D-43AA-AB6A-(E-Mail Removed)...
> > 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
> >
>
>
>

On Thu, 21 Aug 2008 16:21:09 -0400, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:

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>