Avatar of dgmoore1
dgmoore1Flag for United States of America asked on

Excel vba validation error

I am using VBA in Access to validate Excel files by checking the values of certain cells. This is working, but sporadically. In some cases the VBA is returning values that are different from the actual values of the cells. For example, Range("B30") may be null, but the vba returns a value that does not exist anywhere in the spreadsheet; or Range("A16") may contain "Program Manager", but the vba returns "Consultant", which also does not exist anywhere in the spreadsheet (except in a lookup list on a hidden sheet).

Does anybody know how I can troubleshoot this?

Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon


Could you send part of the code where the problem is?


Because I was getting these odd errors I added this section of code for diagnostic purposes:
Set CurrXLS = Xl.Workbooks.Open(f)
Xl.Visible = True
Dim I As Integer
  With CurrXLS
  For I = 1 To 30
    rng = "A" & I
    MsgBox I & " - " & Range(rng)
  Next I

Open in new window

When rng = "A14", the value in the spreadsheet is "Program Manager", but the VBA returns "Consultant", which does not exist anywhere in the sheet. When rng = "B31", the value in the sheet is null, but the VBA returns "26,500", which also does not exist anywhere in the sheet.
This seems to happen randomly - many of the cell values returned are correct, but several are not.
Martin Liss

Your VBA code is most likely just referring to a Range or a Cell without specifying the sheet on which the Range or Cell resides so the information is coming from some other sheet. To correct the problem you can do

With Sheets("Sheet1")
    ' this is just one line of what could be a whole block of code that uses values from Sheet1
    MyVar = .Range("A1").Value
End With

Open in new window

rather than just this.

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

I actually thought about using explicit references when I wrote the code, but apparently it flew out of my mind.

This does the trick - many thanks!
Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013