Global Variable For Workbook

Dec 3, 2007

how to set up a global varable over a workbook? Incognito439

View 5 Replies


Global Variable Or Pass Variable Between Sub-routines

Jan 26, 2007

The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.

I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.

View 9 Replies View Related

Setting A Global Variable

May 27, 2008

If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.

I'm using Excel 2000.

View 10 Replies View Related

Make Variable In VBA Global

Dec 13, 2008

I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use

Dim intRows As Interger
But now how do I make it global.

View 9 Replies View Related

Global Vs. Public Variable

Oct 28, 2009

explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms?

View 9 Replies View Related

Declaring A Global Variable

Oct 20, 2006

I have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?

View 4 Replies View Related

Global Variable Declaration

Dec 25, 2007

I have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA. I am trying to declare a global variable, assign it a value, then use that global variable. Within 'ThisWorkbook' I have the following...

Public myText As String
Private Sub Workbook_Open()
myText = "Hi There"
End Sub

...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox myText
End Sub

Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank. This is all fine, except I want the message box to state "Hi There". What am I doing wrong? Is the variable declared (publically) correctly? Am I assigning the public variable the value correctly?
Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?

View 3 Replies View Related

Global Variable For UserForm

Jan 1, 2008

I maked a userform who use global parameters (to let the user decide where write things) and later i need to use this parameter in a module (who is the main program). I try to resolve this problem put this global parameters like global parameters in the module and later in the both sides (in the module and in the useform), but it can't work. How i can resolve that?, i.e., How i can use a global parameter in a userform and the same global parameter in the modulo with the same data?

View 6 Replies View Related

A Variable Which Is Used By Several Subs In A Worksheet But Is Not Global?

Dec 7, 2008

I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?

View 4 Replies View Related

Setting Global Variable From A UserForm

Jul 31, 2009

I have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"

View 4 Replies View Related

Declare & Initialize A Variable As Global Using Vba

Feb 10, 2009

how to declare&initialize a variable as Global in vba?

I have a variable ,
now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.

View 9 Replies View Related

Declare Global Variable In Application Level

Sep 17, 2009

I need to declare a global variable in Application level not Module level.

I have declare a variable in Module1 and then Module2 with same name then complile it and get success. That means there has two variable with same name in different Module. I think this was not a proper global variable declaration by which I can allow to declare only one variable in all Module, Class every where. I have used code as below -

View 9 Replies View Related

Variable Amount For Global Functions/Formulas

Jun 22, 2008

How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.

View 5 Replies View Related

Excel 2010 :: How To Match Worksheet Names Against Global Variable

Jun 10, 2013

I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.

I am not sure of two things:

1. how to find the already existing tab?

2. Once I find out how do I programatically delete it, so the code can continue

The code below works with the exception of the last section (trying to achieve the questions stated above).

I am using Excel 2010.

Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then


View 5 Replies View Related

Global Options In Workbook

May 13, 2006

Is there any way to select things like tools - options across all worksheets in a workbook? I often want to suppress display of zeroes in an entire workbook, but so far the only way I've found to do this is to go to eash worksheet in turn and go thru the procedure on each one.

View 3 Replies View Related

Global Macros In Personal Workbook

Mar 7, 2008

How do you get macros to be universally accessible as functions without using the "personal.xls!" prefix? For instance I have the macro testmacro(), and if I do "personal.xls!testmacro(...)" it works fine, but if I just do "testmacro(...)" it gives me a "# NAME?" error. I have PERSONAL.xls (which is in the XLStart folder) as a hidden window. I tried unhiding and re-hiding it

View 5 Replies View Related

How To Specify Workbook With Variable

Mar 3, 2014

I have a spreadsheet where a user inputs a client name (ClientName). I will create a new Workbook with the ClientName along with the current date. 2014-03-03John Doe.xls

I am trying to now input data into 2014-03-03John Doe.xls by specifying it in VBA but I can't seem to reference it properly.

DateStr = Format(Date, "yyyy-mm-d")
Comment = Application.InputBox("Type in Client Name ", "File Name")
WBName = DateStr & Comment
Application.Workbooks(WBName).Sheets(1).Range(A2).Value = "QTY"

But it gives me an error.

View 4 Replies View Related

Set Workbook Variable And Use It Within Another Sub?

May 19, 2014

I have this code which opens another workbook and defines the two open workbooks as MainWkbk and NextWkbk:

Private Sub wbo()
'open file selected from dropdown on Macros tab and define the two open workbooks
Dim singlepath As String


This works great for going between the open workbooks within the same sub but I also need to be able to use MainWkbk/NextWkbk in another sub within the same module and cannot seem to get it to work!

I tried adding this to the top of the module;

Private MainWkbk As Workbook
Private NextWkbk As Workbook

but this doesn't work, it always says the variable is not set when in the next sub.

I also tried


within the next sub but this is not right either.

View 1 Replies View Related

How To Get Workbook Name From Variable Sheet Name

Nov 11, 2011

I have an Active sheet which is

MySName = ActiveSheet.Name

Here MySName gives the active sheet name.

I require 1 more variable to take this MySName and assign it to a workbook name and make it is as .xls as i required this workbook name to be used later to close the workbook

For ex:
MySName = ActiveSheet.Name

this gives MySName =aaaa

Now i want like MyWBook = aaaa.xls

But aaaa i want it from variable as we cannot hardcode it.

View 1 Replies View Related

Assigning Variable As Workbook

May 15, 2014

So I have a macro that is saving new excel files based on month and year. I want to assign a variable but I keep getting an error.

Here's what I have:

Sub AssignVariables()
Dim Rebates as Workbook
Dim Master as Workbook
Dim month as string


The last two lines are the errors. I want to type in:


But the error occurs as it gets to the last two lines. By the way, all these two documents are open.

View 4 Replies View Related

Dim Current Workbook As Variable

Jul 2, 2014

I have a Macro that opens a closed file to VLookup some info and pull it to my spreadsheet then close that workbook. the problem I have run into is writing the Macro correctly so if I were to change the file name of the workbook I am running the macro with that it will still use the range declared for the workbook. I have colored the file path that I want to dim in Blue so that it will be easier to see what im trying to do. here is my current macro that works perfectly unless I save my current workbook under a different file name...

Sub Button7_Click()

Sheets("Sheet1").Unprotect "Password"

Workbooks.Open Filename:="\NDGI-SPARE-1UsersownerDocuments2014Vom ResultsVomResults.csv"

Dim c As Range

[Code] .....

View 8 Replies View Related

Set A Workbook Variable As A String

Oct 20, 2009

I have a string variable contacting a file.

I want to set a workbook variable to this file, WITHOUT actually opening it!

DIM wb as Workbook
DIM filenm as String

filenm = "C: empdata.xls"
set workbook = filenm

So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!

View 9 Replies View Related

Using Workbook Name Stored As A Variable

Jul 27, 2006

I am opening a workbook using a macro. I need to run a macro in the workbook I am opening when it opens. I have created a variable containing the workbook name but don't know how to use it to run the macro.

Dim WBName
WBName = ActiveWorkbook.Name
Application.Run _
"'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'!aStartProcess"

'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'! needs to be replaced by the name stored in WBName

aStartProcess is the name of the macro in that workbook I want to run.

View 9 Replies View Related

Set Variable To Worksheet In Another Workbook

Jan 4, 2008

In conjuction with a previous request I've tried to create my own vba code (actually amend existing code i have) but its failing........ whats wrong??

Sub Retail_1_Xfer()
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application. ScreenUpdating = False
Set Isht = Worksheets("Retail Team 1")
Set Lsht = Worksheets("Raw Data")
NextRow = Lsht. Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
Lsht.Range("A" & NextRow).Value = Isht.Range("A9").Value
Lsht.Range("B" & NextRow).Value = Isht.Range("A4").Value
Lsht.Range("C" & NextRow).Value = Isht.Range("B9").Value
Lsht.Range("D" & NextRow).Value = Isht.Range("N3").Value
Lsht.Range("E" & NextRow).Value = Isht.Range("N2").Value
Lsht.Range("F" & NextRow).Value = Isht.Range("BJ9").Value
End Sub

I think its got something to do with the fact that the worksheet " Retail Team 1 " is in another workbook

View 2 Replies View Related

VBA Workbook Reference Via Variable

Feb 18, 2008

Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.

Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.

View 4 Replies View Related

Vlookup On Different Workbook With Variable Sheet Name?

May 28, 2014

I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.

[Code] .....

View 5 Replies View Related

Opening Workbook And Assigning Variable

Jul 14, 2014

I am trying to open a workbook, assign it a variable and use that variable later. here is what i have

[Code] ......

When I try to activate it later on in another procedure using:

[Code] copy to clipboard

I receive an "Automation Error"

I have tried declaring it as a public variable, even a global variable and cannot get this to work.

All I'm really wanting to do is open a workbook (where the name will change depending on the book being opened), assign that workbook a variable name, and use it later in the code.

View 7 Replies View Related

Reference Row In Another Workbook With Integer Variable

Jan 28, 2014

I need to reference a different workbook in a while loop. Each iteration, PopFive increases by 3, as I want to pull the numbers of every third row. How does the syntax of the line go after the " ! "?

[Code] ......

View 1 Replies View Related

Assigning Variable To Part Of Workbook Name?

Dec 22, 2011

Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)

I have this working no problem but here is my snag.

The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).

Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.

A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)

Sub collate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


View 2 Replies View Related

Referencing Variable Workbook Tab In VLookup?

Nov 29, 2012

I have a challenge with Vlookups!

I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02

I then have another workbook which has employees in column B and the client code in C3.

The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.

I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved