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

I have a feeling it's due to using `Active`. [Don't do that](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), it's ***highly*** recommended to avoid using `.Select`/`.Activate`. — BruceWayne, Jul 07 '16 at 20:46

score 0 · Answer 1 · answered Jul 07 '16 at 20:54

0

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

answered Jul 07 '16 at 20:54

SeanC

15,695
5
45
66

ReDim is still receiving Error 9 when Preserve is on there. – Bryan Thomas Linsday Jul 07 '16 at 21:01
check that the array runs from 0 to 11 by using `LBound` and `UBound` – SeanC Jul 07 '16 at 21:05
Profile(1,2) is the only recognized variable, the rest are Error 9. – Bryan Thomas Linsday Jul 07 '16 at 21:15
Why are you trying to `ReDim` Profile? What do you want to do to it? See SeanC's suggestion about using `LBound` and `UBound` to identify the actual bounds, rather than guessing about them. I expect that the `Range` function produces an array that is dimensioned to fit the data. If you set a breakpoint in the routine and put a watch on Profile, you should be able to see its dimensions at any given point in the routine. – Craig Jul 07 '16 at 21:21
Sorry, I want to call out specific values in the array. I'll be able to test LBound and UBound tonight, not enough time right now. – Bryan Thomas Linsday Jul 07 '16 at 21:30

score 0 · Accepted Answer · answered Jul 07 '16 at 21:34

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.

You're correct, I didn't need to ReDim it. I simply didn't know that I had to use 2d values when in reality it should be a 1d row, but I've seen videos explaining this already so I understand now. Thanks for your help! — Bryan Thomas Linsday, Jul 08 '16 at 08:28

VBA: Error 9 when Loading Array Values

2 Answers2

Linked