VBA: Error 9 when Loading Array Values

Question

I'm currently writing a multi-userform workbook that will check guests into the system by adding points. I'm attempting to make it more personalized by having the Main Label on the "Check In Form" respond to the guest by name once they checked in. I'm also making another userform that displays all of the guests information if they ask for it. Right now I have 2 concerning issues I have attempted to debug via other online resources.

1) During check in, I use an array called Profile to retrieve all information from that person. When I call out the range to add to the array, I end up with Error 9 "Subscript out of range." To remedy this, I attempted to ReDim Preserve the array, only to find out that my information has been cleared anyway.

Option Explicit
Dim Profile() as Variant, Point as Integer

Sub CheckIn()
ActiveCell.Offset(0, 6).Select
ActiveCell.Value = ActiveCell.Value + Point
If ActiveCell.Value >= 10 Then
    ActiveCell.Value = ActiveCell.Value - 10
    MsgBox ("Congradulations! You just earned one free Engineering Pad. Talk to your Membership chair to recieve your free pad.")
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Value + Point

Profile() = Array(Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12)))
'data is Error 9 here

ReDim Preserve Profile(0 To 11)
'data is cleared here

MainLabel.Caption = "Hello " & Profile(1) & " " & Profile(2) & ". You Have " & Profile(7) & " Points."

ActiveCell.EntireRow.Select
Application.Wait (Now + #12:00:05 AM#)
MainLabel.Caption = "Please Enter Your 9-Digit ID or Swipe Your Card"
End Sub

In addition, changing the data type from Variant to String only produces a type mismatch when I attempt to add the data to the Profile, even when Split() is used. How can this be fixed? Any advice is appreciated. Thank you!

Here is an image of my spreadsheet

Answer 1

Range() does not need an Array() around it to get the values... Also, Range will always produce either a singe value, or a 2 dimensional array.

Change:

Profile = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12))
'note the lack of parentheses

Also, as the row is the first element, you cannot redim the array with preserve, as preserve only works on the final dimension of the array.

ReDim Preserve Profile(0 To 11,0 to 2) would work but
ReDim Preserve Profile(0 To 22,0 to 1) would fail, as preserve is invalid in this context

Answer 2

When you assign values from a range of cells into a variant array, you always get a two dimensioned, 1-based array; even if that array is only 1 to 1 as the second rank (columns) or, as in your case, 1 to 1 in the first rank (rows).

dim profile as variant, acrw as long

acrw = activecell.row

with worksheets("MySheet1")   'know what worksheet you are on!!!!!
    profile = .Range(.Cells(acrw, 1), .cells(acrw, 12)).value2

    'the following should be 1:1 and 1:12
    debug.print lbound(profile, 1) & ":" & ubound(profile, 1)
    debug.print lbound(profile, 2) & ":" & ubound(profile, 2)

    'why are you redimming this at all?
    'ReDim Preserve Profile(0 To 11)
    'the following adds room for two more columns of data while preserving the values
    ReDim Preserve Profile(1 to 1, 1 To 14)
end with

You can only use the ReDim statement with Preserve to change the dimension of the second rank; never the first.

Use the LBound and UBound functions to determine the limits (aka boundaries) of your array.