Finding the size of a variant

[Paul Johnson]

Hi,

I've got most of the code in my VBA running (which is a miracle given it's the first time I've attempted anything in VBA), but I've hit a snag.

I am reading in a variable number of rows by like this

dim cells() as variant
for module = 0 to 9
    for student = 2 to 35
        if module < 3 then
            s = nd1sc(module) + student
            e = nd1ec(module) + student
            inuse = nd1compulsory
        else
            s = nd1se(module - 3) + student
            e = nd1ee(module - 3) + student
            inuse = nd1elective
        endif
    redim cells() as variant
    cells = inuse.getCellRangeByName(s & ":" & e)
    for checked = 0 to ubound(cells) ' dies here
        if cells(checked) = "P" then pass = pass + 1
    next checked
 

nd1compulsory / elective are just two workbooks in my spreadsheet

The code will compile, but when I run the code, it dies saying that the array has to be dimensioned.

As the number of cells being read in are variable, is there a way to find the size of the number of cells read in?

The idea of the spreadsheet is so I can keep of the overall marks for my students starting in September.

Thanks

Paul

[NeoPa]

Paul,

You ReDim the Cells array, but you never specify any dimensions so UBound has nothing to work from (hence the error).

Cells is also a used word in Excel, so it's strongly advisable to avoid using it for something else. It's only likely to confuse you.

As a last comment, let me direct you towards some sensible instructions on how best to approach using and posting code on here (When Posting (VBA or SQL) Code).