Read value in from inactive sheet through VBA

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

Hi.
I keep a few global variables in a sheet called "admin" and give cells name
like:
pct_fee, gross_price etc.

How can I read named ranges smarter than this through VBA:
Sub mytest()
dim varpct_fee
Sheets("admin").Select
ActiveSheet.Range("pct_fee").Select
varpct_fee= ActiveCell.Value
End Sub

I want to use this psaudo instead:

Sub mytest()
dim varpct_fee
varpct_fee = Sheets("admin").Range("pct_fee").Value
End Sub

In general I want to store global values in a "smart" way, easy to change
for the assigned users, without entering VBA mode. With the first code, the
sub starts jumping around in the workbook which is visible for the user and
makes him worried....

Kind regards

Mr. Smith
 
Mr., Your second sub should work fine. One thing, it might be better
to specify the workbook name the sheet is in to avoid conflicts in case
your user has several workbooks open:
Sub mytest()
dim varpct_fee
dim A as worksheet
Set A=ThisWorkbook.Worksheets("admin")
varpct_fee = A.[pct_fee]
End Sub

or

Sub mytest()
dim varpct_fee
With Workbooks("Abc.xls").Worksheets("admin")
varpct_fee = .Range("pct_fee")
End With
End Sub

The .Value is not really needed to get range values. I like to use
brackets [] for my range names simply because it saves typing. Some
people prefer the range("rangename") style.
James
 
Sub mytest()
dim varpct_fee
varpct_fee= Range("pct_fee").Value
End Sub
This does what you want without any screen changes to worry the user. In
the future if you want to prevent screen changes, bracket your code with
these statements:
Application.ScreenUpdating=False
Application.ScreenUpdating=True
HTH Otto
 
Back
Top