1

I'm writing some VBA to check for changes to a spreadsheet when a button is clicked. The spreadsheet makes a copy of itself in the temp directory as determined by the environment variable on Workbook_Open(). This is all working fine, but the problem is with my button_click sub.

Following advice from this question, I'm trying to read the range of interest into an array. The problem is, my array is coming up empty. Is there something stupid that I'm forgetting to do here?

Dim wsOriginalWS As Worksheet
Dim varOriginalSheet As Variant
Dim wbkOrig As Workbook
Dim strRangeToCheck As String
Dim varOriginalSheet As Variant

'... some other non-relevant things

strRangeToCheck = "A5:HC231"
Set wbkOrig = Workbooks.Open(Filename:=FileStr)
Set wsOriginalWS = wbkOrig.Worksheets("Sheet1")
wsOriginalWS.Activate
With wsOriginalWS
    Set varOriginalSheet = Range(strRangeToCheck)
End With

'... some other non-relevant things.  At this point,
'... wbkOrig is still open, and I can see it with all of its data
'... while debugging.

itemp = getDimension(varOriginalSheet)

I have verified that everything up to Set varOriginalSheet = Range(strRangeToCheck) is working. The spreadsheet in the temp directory opens and is not empty. For some reason varOriginalSheet is empty.

Here's getDimensions:

Function getDimension(var As Variant) As Long
    On Error GoTo Err
    Dim i As Long
    Dim tmp As Long
    i = 0
    Do While True
        i = i + 1
        tmp = UBound(var, i)
    Loop
Err:
    getDimension = i - 1
End Function
Alex
  • 689
  • 1
  • 8
  • 22
  • 1
    Try: `Set varOriginalSheet = .Range(strRangeToCheck)`. Without the point its just a general range and not directly refering to `wsOriginalWS`. Also you dont need `wsOriginalWS.Activate`. – Plagon Mar 12 '18 at 14:08
  • Thanks - this is a good point, but it still comes up empty. – Alex Mar 12 '18 at 14:12
  • 1
    Ah okay, you also need `getDimension(Application.Transpose(varOriginalSheet))` to convert it to an array. – Plagon Mar 12 '18 at 14:20
  • Add `.Value` on the end. – Scott Craner Mar 12 '18 at 14:20
  • Do you have `On Error Resume Next` in the `'... some other non-relevant things`? – Scott Craner Mar 12 '18 at 14:21
  • @UGP Thanks, I included getDimensions thinking that the problem might be with that rather than actually getting the data into the array, but after further debugging, the array is definitely empty. I will make these changes though. – Alex Mar 12 '18 at 14:22
  • @ScottCraner - Add .Value to the end of .Range(strRangeToCheck)? Also, I don't have an On Error in some other non relevant things. It steps through the entire code in debug mode without throwing any errors. – Alex Mar 12 '18 at 14:24
  • Yes at the end of that line. – Scott Craner Mar 12 '18 at 14:25
  • Adding .Value to the end of .Range(strRangeToCheck) results in a type mismatch error. – Alex Mar 12 '18 at 14:27
  • Oh and do not use Set when setting a variant array. `varOriginalSheet = Range(strRangeToCheck).Value` – Scott Craner Mar 12 '18 at 14:43

0 Answers0