Re: [ExcelVBA] Subscript out of range

 

Dear Lisa

Apart from in some deprecated legacy 16 bit API calls and a in couple of obscure MS Office functions, can you provide any examples of where the VBA Integer type must be used in preference to the Long in modern 32 bit and above PCs.

And for your second point, as someone who gave up using I, J, K and L (when I stopped using Fortran) in the early 1970s in favour of self-documenting natural language variable names I cannot repeat that advice often enough. Unfortunately the universities are still churning out people who have been convinced by those who should know better that it is more professional to use ZX81 names.

Regards

Derek Turner

England +++

---

From: "Green 1z@compuserve.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, 28 May 2014, 12:20
Subject: Re: [ExcelVBA] Subscript out of range

 

Hi Derek,

Integer does exist and has it's uses.

VBA generally converts integer to long it's true... but not always.

points 2 and 7 seem to say the same thing.

Lisa

Sent: Mon, May 26, 2014 11:47 am
Subject: Re: [ExcelVBA] Subscript out of range

Dear David

There are several things wrong with your code. This is what you should have written :-

Option Explicit ' NEVER work without this as first line. EVER.

Sub Exercise()

Dim nColumn As Long, nRow As Long

Dim r_1(2, 5) As Variant

    For nRow = 1 To 2

        For nColumn = 1 To 5

            r_1(nRow, nColumn) = Range(Cells(nColumn, nRow), Cells(nColumn, nRow)).Value

        Next nColumn

     Next nRow

End Sub

1. If you had used Option Explicit this would have flagged up earlier errors and forced you to dimension properly.

2. If you had named your variables better it would have been more obvious to you what was going on.

3. The VBA Integer type does not exist as such in modern PCs so use Long.

4. Using the Variant type for the arrays is better as you cannot always know what type of data is in the cells.

5. Dim r_1(1 To 2, 1 To 5) As Variant . . . is more in line with what you are trying to do.

6. And - you don't need the second array as you can refer to the second row of the array with its row index.

7. Don't call it R_1, call it something that makes sense in English.

Finally consider this code :-

Sub LoadTwoDimensionArrayAllAtOnce()

Dim r_1() As Variant

    r_1 = Range("A1:E2").Value

End Sub

Regards

Derek Turner

England +++

---

From: "David who1012000@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: "excelvba@yahoogroups.com" <excelvba@yahoogroups.com>
Sent: Sunday, 25 May 2014, 20:52
Subject: [ExcelVBA] Subscript out of range

 

Hello,

 

I am working on an exercise where I have 2 sets of numbers in 2 rows A1:E1 and A2:E2.

I am trying to populate these numbers in these into 2 arrays. The arrays are :

Dim r_1(1, 5) As Integer, Dim r_2(1, 5) As Integer

I am using nested For Loops to try and populate the values into the arrays:

For i = 1 To 2

    For j = 1 To 5

      r_1(row1, j) = Range(Cells(row1, i), Cells(row1, i)).Value

      r_2(row2, j) = Range(Cells(row2, i), Cells(row2, i)).Value

    Next j

 Next i

 

r_1(row1, j) = Range(Cells(row1, i), Cells(row1, i)).Value

This above is working and getting the right value into the array, but

 

r_2(row2, j) = Range(Cells(row2, i), Cells(row2, i)).Value

the above fails, with "Subscript out of range". I am not sure what is wrong.

 

Thanks in advance. Hope everyone's day is going well.

David

__._,_.___

---

Posted by: Derek Turner <g4swy@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___