Can't calculate a threshold value through a Function and a Sorting procedure of an Array

[seigna]

I am trying alternative methods for calculating a minimum threshold value (0.05) using a Function, a Sort procedure, and arrays (CoursAction)

However, I get a message that an array is expected for the Var Function.

Does anyone have a guess what could the problem be?

See the attachment below.

Please Login or Register  to view this content.

[Norie]

I've had a look at the code and as far as I can see the problem is that Var isn't a VBA function, you can access it with Application.WorksheetFunction.Var.

[seigna]

Mmmm... but Var is just supposed to be a function I define and perform on CoursAction

[seigna]

I had a different code where I named it differently and I got the same error that it was expecting an array, or that the subscript was out of range..

[seigna]

What Var does is simply get the value from the array that corresponds to Int(N*A) with N=number of rows and A = 0.05

[MrShorty]

Is this all in one module? I note that Sub Performance has a variable Var (double) dimensioned, then, a couple of statements later you call Var the function. I wonder if what you are seeing is a naming conflict, where VBA is having trouble resolving the double variable Var and the function Var. I would try changing the name for one of those to something unique, to avoid the potential conflict.

[seigna]

Ahhh wait, I can just rename them to Function VarB otherwise.. It shouldn't change much though. My original function is called VarHistoric (the one on the real file I am working on) and it gives the same errors..

It is supposed to be in a module in the future. (tomorrow)

[seigna]

Let me explain in more detail what I am doing there:

As you can see, I have a file with a few column of data. I am going to perform calculations on each Column of the data (cause they correspond to stock values).
Here, specifically, I am going to
1. Construct a function that:
a/ Fills an array with range values
b/Sort the data in the array through a bubble sort procedure Sub Tri1
c/ Extract from the array the value that corresponds to the Int(N*A) value where A is a percentage specified in the sheet, and N is the number of rows in each column.

Now, it returns two errors: Expected Array and Subscript out of range. I have modified the code into three different things but I have never changed the fact I used that specific bubble sort procedure and that I place the values in an array. Could the problem be coming from somewhere there?

[seigna]

I also addressed this issue here. http://www.excelforum.com/excel-prog...nd-arrays.html

The code is a bit different though it is supposed to do the same thing.