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?
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
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.
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.
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 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.
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?
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?
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?
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"
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.
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 -
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.
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
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")
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)
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
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
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?
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.
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.
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.
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?
