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
ADVERTISEMENT
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.
Code:
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
[code]....
View 5 Replies
View Related
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
Jul 11, 2007
This is the code after editing to make it more clear
Public Sub 1()
BookA= activeworkbook. name
BookB=Application.Workbooks.Add
Workbook(BookB).activate
End Sub
Public Sub 2()
BookB=Activeworkbook.name
With BookB. sheets(1)
.range("A1")=BookA.sheets(1).range("B1")
End Sub()
At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with thisworkbook.name when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.
View 6 Replies
View Related
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
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
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
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
Dec 3, 2007
how to set up a global varable over a workbook? Incognito439
View 5 Replies
View Related
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
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
Mar 10, 2009
I am trying to get a module wide variable to provide the current filename to the various subs.
View 2 Replies
View Related
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
Feb 10, 2009
how to declare&initialize a variable as Global in vba?
I have a variable ,
listGroup=Array("aaa","bbb","ccc")
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
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
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
Apr 23, 2007
Working with named ranges is new to me, and very aggrevating to me too! I am trying to create a Named Range inside a worksheet_change event that is Global. I can create it, but it's always local to the sheet where the change event is happening.
For example:
Names.Add Name:="RandomName", RefersTo:=Target
...creates a local name instead of global one (whereas it would be global if it was created in just a normal subroutine).
Aside from using this trick: Change refersto property to workbook-level
View 8 Replies
View Related
Apr 29, 2013
I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Compile Error:
Expected: list separator or )
[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")
[Code].....
View 4 Replies
View Related
Jan 9, 2014
I have a macro that compares two sheets (in a workbook that might contain 10 sheets). Currently, the user enters the names of the two sheets in two input boxes. Is there a way that the user need only to click on a sheet tab and the sheet names are used in the macro? (this would eliminate any misspellings)
View 2 Replies
View Related
Mar 21, 2012
I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("E4:E100")
For Each cell In Target
[Code]....
View 5 Replies
View Related
May 20, 2014
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name
[code].....
But it's erroring out on me.
View 4 Replies
View Related
Jun 4, 2009
I have 2 sub, and I want to use the same variables that reference values on my spreadsheet.
for instance, I declare and set ws as worksheet("sheet1") in the first sub.
in the second one, I also want to reference to the same ws. Instead of having to declare it twice, being redundant, what would be a good way to do this?
View 9 Replies
View Related
May 8, 2007
How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.
View 3 Replies
View Related
Aug 19, 2008
I'm having some trouble getting to grips with using/calling variables and other sub routines.
View 9 Replies
View Related
Jan 26, 2009
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies
View Related
Nov 3, 2009
I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
View 3 Replies
View Related
Sep 10, 2012
VBA - Exit all Subs not just current
View 2 Replies
View Related
Oct 30, 2013
I have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
View 5 Replies
View Related
Jul 28, 2007
Calling a Sub from another Sub is quite common, how about a Sub that does nothing else but call all the subs to execute the entire job?
Will the subs run sequentially after one completes, or will there be the likley hood they will bump into each other causing all sorts of chaos.
View 9 Replies
View Related
Jan 12, 2007
How can it be that this works?
Sub AA()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
And this doesn't work?
Sub BB()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
CC
End Sub
Sub CC()
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub
Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?
View 4 Replies
View Related
