VB Array Return Problem

**STr83dgeboi** · 01-25-2010, 06:38 PM

This should be easy but I keep stumping myself...

I am writing a function for one of my classes to check my work and I can't see to pull a series in...

This is what I have so far but I am pretty sure the Series() part isn't working the way I wanted it to.
(I know there is already a Quartile function but its considered wrong by my professor, hence this macro)

N = total number in series

Q = what % quartile (1st Quartile = 25)

Series() = all the numbers in the series

I want to pull say, A1:A20 into an array, without defining it to just A1:A20

Code:

Function QuartileBUS(N, Q, Series())
    
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
    
        '''' Hold finds what number in the array the first quartile will be found
        Hold = (N + 1) * (Q / 100)

        '''' This drops the decimal so it will find a whole number, I figure it cant find  placeholder 5.44 in an array
        Hold2 = Hold

        '''' This finds the Quartile assuming the array was loaded in which isn't happening
        Quartile = Series(Hold2) + (Series(Hold2 + 1) - Series(Hold2) * (Hold - Hold2))
        
End Function

EXAMPLE:
Series to be pulled into an array
116
121
157
192
207
209
209
229
232
236
236
239
245
246
260
264
276
281
283
289
296
307
309
312
317
324
341
353

N = 28 (total number in series above)

Q = 25 (Trying to find the first Quartile)

Series(116,121,157...,341,353)

This is what it should do:

Code:

Function QuartileBUS(28, 25,A1:A28)
    
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
    
        Hold = (28 + 1) * (25 / 100) = (29)*(.25) = 7.25
        Hold2 = Hold - 7.25 = 7
        Quartile = Series(Hold2) + ((Series(Hold2 + 1) - Series(Hold2)) * (Hold - Hold2))

       Quartile = Series(7) + ((Series(7+1) - Series(7)) * (7.25-7))
       Quartile = 209 + ((229-209)) * (.25))
       Quartile = 209 + (20 * .25)
       Quartile = 209 + 5
       Quartile = 214

End Function

I don't know if that is too complicated or explained enough, please post if you don't understand my request =D

**pike** · 01-25-2010, 11:43 PM

Ih STr83dgeboimy

Code:

Arr = Application.WorksheetFunction.Transpose(Series)

Try something like

Code:

option explicit
Function QuartileBUS(N, Q, Series)
    
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
   Dim Arr
Arr = Application.WorksheetFunction.Transpose(Series)
        '''' Hold finds what number in the array the first quartile will be found
        Hold = (N + 1) * (Q / 100)

        '''' This drops the decimal so it will find a whole number, I figure it cant find  placeholder 5.44 in an array
        Hold2 = Hold

        '''' This finds the Quartile assuming the array was loaded in which isn't happening
        Quartile = Arr(Hold2) + (Arr(Hold2 + 1) - Arr(Hold2) * (Hold - Hold2))
        
End Function

**pike** · 01-25-2010, 11:48 PM

correction after testing
you can get remove the N bit
and need to comple fuction QuartileBUS=

Code:

Option Explicit
Function QuartileBUS(Q, Series)
    
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
   Dim Arr, N!
   
Arr = Application.WorksheetFunction.Transpose(Series)
N = UBound(Arr)
        '''' Hold finds what number in the array the first quartile will be found
        Hold = (N + 1) * (Q / 100)

        '''' This drops the decimal so it will find a whole number, I figure it cant find  placeholder 5.44 in an array
        Hold2 = Hold

        '''' This finds the Quartile assuming the array was loaded in which isn't happening
        QuartileBUS = Arr(Hold2) + (Arr(Hold2 + 1) - Arr(Hold2) * (Hold - Hold2))
        
End Function

**STr83dgeboi** · 01-29-2010, 07:28 PM

ok... so that didn't quite help, I don't know if I am doing this wrong or getting another error but I just get a #NAME? error.

Updated Question:

I have this Function
http://i278.photobucket.com/albums/k...oi/example.jpg

Code:

Function Array(Series())

Array = Series(1)+Series(2)
End Function

What I am having problems with is, how do I get this function to pull the highlighted cells into the array Series

**pike** · 01-29-2010, 07:58 PM

It does work
with the function

Code:

=QuartileBUS(Q, Series)

where Q = 25
which you wanted as 1st Quartile
and Series = A1:A25
as the data range

**STr83dgeboi** · 01-29-2010, 08:14 PM

Originally Posted by pike

It does work
with the function

Code:

=QuartileBUS(Q, Series)

where Q = 25
which you wanted as 1st Quartile
and Series = A1:A25
as the data range

Well then I am having some dumb newb error because I keep getting a #NAME? error.

If I copy and paste that code in, and complie the work, it doesn't give me the Quartile option anymore when I type =

I play around some more and see what I did wrong then

**pike** · 01-29-2010, 08:22 PM

Hi STr83dgeboi
Have a look at the attachment

**STr83dgeboi** · 01-29-2010, 08:31 PM

Originally Posted by pike

Hi STr83dgeboi
Have a look at the attachment

Thanks... I figured it out... I am using Office2007 and it doesn't like the Module unless you allow it... then you have to restart Excel to get it to work... Thank you very much my good man!

**STr83dgeboi** · 01-29-2010, 11:07 PM

Sry for double post, Have a new related problem...

I am trying to turn this from a function into a Form based formula and am having the same but different problem. One button grabs the set of cells like the Function was doing, and the second runs the answer and displays it.

First problem, I am using InputBox to grab the information but I don't know where it is going and how to access it

Second problem, I was trying to get the inputbox to display the information in the text box on the form

Third problem is related to the first one, can't get the answer till I figure out problem one.

I tried searching these forums and the internet with no luck for a solution to the first two problems =/

Code:

Private Sub CommandButton1_Click()
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
    Dim Arr, N!
   
   Arr = Application.WorksheetFunction.Transpose(?????)
   N = UBound(Arr)
        '''' Hold finds what number in the array the first quartile will be found
        Hold = (N + 1) * (Q / 100)

        '''' This drops the decimal so it will find a whole number, I figure it cant find  placeholder 5.44 in an array
        Hold2 = Hold

        '''' This finds the Quartile assuming the array was loaded in which isn't happening
        QuartileBUS = Arr(Hold2) + (Arr(Hold2 + 1) - Arr(Hold2) * (Hold - Hold2))
End Sub

Private Sub Getdata_Click()

Dim InputCells As Range

'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(Prompt:="Select Data", _
Title:="Select Data", Type:=8)

Data.Text = InputCells

End Sub

I attached what I have so far...

**pike** · 01-30-2010, 12:14 AM

Hi STr83dgeboi
Just change the prefix to solved and its best to start a new thread

**pike** · 01-30-2010, 01:39 AM

or

Code:

Private Sub CommandButton1_Click()
    Dim Hold As Long
    Dim Hold2 As Integer
    Dim Hold3 As Long
    Dim Arr,  Q!
   Q = InputBox("Select Quartile")

  Arr = Application.WorksheetFunction.Transpose(Application.InputBox(Prompt:="Select Data", Title:="Select Range", Type:=8))
         '''' Hold finds what number in the array the first quartile will be found
        Hold = (UBound(Arr) + 1) * (Q / 100)

        '''' This drops the decimal so it will find a whole number, I figure it cant find  placeholder 5.44 in an array
        Hold2 = Hold

        '''' This finds the Quartile assuming the array was loaded in which isn't happening
      MsgBox Arr(Hold2) + (Arr(Hold2 + 1) - Arr(Hold2) * (Hold - Hold2))

End Sub