We help IT Professionals succeed at work.
Get Started

Excel 2003 Scope Problem

TigerMan asked
Last Modified: 2012-06-21
I have declared "Dim ArtsArr" at the top of a module.
The attached code works fine and populates ArtsArr successfully.  I have Watched it and note that the variable ArtsArr goes out of context immediately on End Sub.  This is a public variable so that should not happen.  Why?

When I run the following code immediately after, I get a Type Mismatch on the For Counter = line.
That is because ArtsArr is now out of context, and therefore UBound(ArtsArr,1) is a null value.

Sub Add2012RCItoArtsArr(ArtsArr)
' calculate the RCI for each FoR in each article, and add into Cols 12, 15, 18 in ArtsArr
For Counter = 1 To UBound(ArtsArr, 1)
Do Stuff
Next Counter
End Sub

Excel insanity yet again ...

Anyone see the problem here?
Sub LoadArticlesFromOriginalSource()

Dim VirtualBook As Workbook, FilePointer As String
FilePointer = ThisWorkbook.Path & "\list-of-jnls.xls"
If Dir(FilePointer) <> "" Then
    Set VirtualBook = Workbooks.Open(FilePointer)
    NumArts = Range("A1").CurrentRegion.Rows.Count - 1
    NumCols = Range("A1").CurrentRegion.Columns.Count
    Dim ArtsArr()
    ArtsArr = Range("A2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, _
    VirtualBook.Close SaveChanges:=False
    MsgBox "File not found”
End If
End Sub

Open in new window

Watch Question
Most Valuable Expert 2012
Top Expert 2012
This problem has been solved!
Unlock 2 Answers and 16 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE