Solved

referencing a workbook VBA

Posted on 2014-07-22
2
176 Views
Last Modified: 2014-07-23
I have a module


Sub RunTheReport()

Dim Repfile As Workbook
Set Repfile = ThisWorkbook

Application.ScreenUpdating = False

Call XYZ

End Sub

with in XYZ, I have a line of code saying

RepFile.activate

I am getting the "object Required" error

Where am i going wrong?

Thanks
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40211917
Hi,

You have to refer to the file in the sub

Sub RunTheReport()
Dim Repfile As Workbook
   Set Repfile = ThisWorkbook
   Application.ScreenUpdating = False
   Call XYZ(Repfile)
End Sub

Sub XYZ(myFile)
   myFile.Activate
End Sub

Open in new window



O ryou have to put
Dim Repfile As Workbook
at the beginning of your module out of any sub or function
as global variable

Regards
0
 
LVL 22

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 250 total points
ID: 40211926
Move
Dim Repfile As Workbook
above all subs in the module, and change to
Public Repfile As Workbook

Now Repfile is only to use in RunTheReport, not in other subs.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question