Create Public Variable containing data from WorkSheet

Question

Simplified Excel Code:

Public MyFile As String
Public varSheetA As Variant
Public SelRangeA As Range
Public wsCopy As Excel.Worksheet

Sub SelectFile_Click()

MyFile = Application.GetOpenFilename()  'Aquire filepath from user
If (MyFile <> "False") Then
Range("B1").Value = "File Found"
End If
End Sub

Sub LoadFile_Click()

Dim WbOne As Workbook
Dim strRangeToCheck As String
strRangeToCheck = "A1:T2000"

Set WbOne = Workbooks.Open(MyFile) 'Open that file
Set wsCopy = WbOne.Worksheets(1)   'Try to copy
Set varSheetA = wsCopy.Range(strRangeToCheck) 'Try to copy
Set SelRangeA = wsCopy.Range(strRangeToCheck) 'Try to copy

WbOne.Close 'This is where we lose the references & values
End Sub

Sub DisplayFile_Click()

Range("A4").Value = varSheetA(1, 1)
End Sub

The end result of this program is to have the values from WorkSheet(1) in a Range or Variant Array so that I can edit and display them as needed and eventually copy the values back into the original file. However when I run this code, all the Public variables I initialize are empty when LoadFile_Click exits (more specifically when WbOne closes.

Previously my code looked like varSheetA = WbOne.Worksheet(1).Range(strRangeToCheck) although I'm currently in the process of testing different methods because that way didn't seem to work.

Anyone see any fundamental problems with what I'm trying to do? Thanks!

Does this throw an error? Or are you asking if this is the most efficient way to do what you're trying? If you want to save a range for use in another Sub/Function, perhaps you can pass the variables through? — BruceWayne
– BruceWayne, Commented Sep 2, 2016 at 20:56
@BruceWayne Huge fan - I edited my question to specify the fact I'm running into an error :P . The references are empty when the Workbook closes even though the values should still be present in the Variant or Range variables. Unless I'm just passing references? Not values? — Tyler Dickson
– Tyler Dickson, Commented Sep 2, 2016 at 21:01
SelRangeA = wsCopy.Range(strRangeToCheck).Value (do not use Set here) That will give you a variant array of the values from the specified range. — Tim Williams
– Tim Williams, Commented Sep 2, 2016 at 21:03

Tim Williams · Accepted Answer · 2016-09-02 21:09:13Z

2

Option Explicit

Public MyFile As String
Public varSheetA As Variant


Sub SelectFile_Click()
    MyFile = Application.GetOpenFilename()  'Aquire filepath from user
    If (MyFile <> "False") Then
        Range("B1").Value = "File Found"
    End If
End Sub

Sub LoadFile_Click()

    Const strRangeToCheck As String = "A1:T2000"

    With Workbooks.Open(MyFile)
        varSheetA = .Worksheets(1).Range(strRangeToCheck).Value
        .Close False
    End With

End Sub

Sub DisplayFile_Click()
    Range("A4").Value = varSheetA(1, 1)
End Sub

answered Sep 2, 2016 at 21:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tyler Dickson Over a year ago

Appreciate it - I do believe I'm also going to need the Range variable as well though if I'm wanting to update the values and put them back into the original file

Tim Williams Over a year ago

You cannot store a reference to a Range object if you want to close the file: as soon as it's closed you lose the object which your Range variable points to. If you want to put back the (edited) data then you know where it needs to go (A1:T2000) without needing to persist the Range object.

Collectives™ on Stack Overflow

Create Public Variable containing data from WorkSheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related