How to load Arrays from Excel Sheet into VBA?

Question

I want to write a function which translates exam points into a grade. If i define the Arrays inside the function it works as expected. But i would like to give the user the option to use his own point/grade structure as a parameter of the function.

I tried this to load an Array from the spreadsheet

This one gives me an Value error in excel.

Function GRADE(Points, ParamArray Pattern())

 If IsMissing(Pattern) Then Pattern = Array(90.5, 80, 75, 70, 65, 60, 55, 50, 45, 40, 35)

 Grades = Array(1, 1.3, 1.7, 2, 2.3, 2.7, 3, 3.3, 3.7, 4, 5)
 
 For i = LBound(Pattern) To UBound(Pattern)
  If Points >= Pattern(i) Then
   GRADE = Grades(i)
   Exit For
  End If
 Next i

End Function

You call this as UDF? Probably you need to define the Pattern-Parameter as Range. — FunThomas
– FunThomas, Commented Jan 17, 2023 at 10:17
You're going to need to show us how you intend to call the function. Is the ParamArray going to be loaded with a range - eg =GRADES(A1,B1:B10) , or as an array - eg. =GRADES(A1, 95, 90, 85, 80, 75, 65, 55, 50, 45, 40, 35) — CLR
– CLR, Commented Jan 17, 2023 at 10:32
1. Should all the time Pattern and Grades contain 11 elements? If not, is there a logic based on which to build Grades according to Pattern input? 2. As suggested above, you can replace ParamArray parameter with a range (or to an array). GRADE(Points, rngPat As Range) And pass it to the function as "A2:A12". Then, declare Pattern As Variant and use Pattern = rngPat.Value. And obtaining a 2D array you should use If Points >= Pattern(i, 1) Then... — FaneDuru
– FaneDuru, Commented Jan 17, 2023 at 10:32
I think maybe you don't understand ParamArray. How are you inputting your Array? =grade(22,50,40,30,20,10,5,2,1) works OK, as does using individual cell references: =grade(22,B1,B2,B3,B4,B5,B6,B7,B8). However, if you are using a multi-cell Range for an argument to Pattern, you need to detect that and then extract each value in the range reference to your pattern array in your code. And, of course, ParamArray can take a variety of inputs. eg `=grade(50,B2:B8,90,95) would also be valid input. You just have to figure out what's what in your code. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 17, 2023 at 14:10

VBasic2008 · Accepted Answer · 2023-01-17 15:27:25Z

0

Grading System UDF

Option Explicit

Function GRADE(ByVal Points As Double, ParamArray Pattern()) As Double

    If IsMissing(Pattern) _
        Then Pattern = Array(90.5, 80, 75, 70, 65, 60, 55, 50, 45, 40, 35)
    Dim Grades(): Grades = Array(1, 1.3, 1.7, 2, 2.3, 2.7, 3, 3.3, 3.7, 4, 5)
 
    Dim Result As Double, i As Long
 
    For i = LBound(Pattern) To UBound(Pattern)
        If Points >= Pattern(i) Then Result = Grades(i): Exit For
    Next i
    
    If Result = 0 Then Result = 6
    
    GRADE = Result

End Function

answered Jan 17, 2023 at 15:27

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to load Arrays from Excel Sheet into VBA?

1 Answer 1

Grading System UDF

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Grading System UDF

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related