I have written a number of macros, and I typically use Application.Screenupdating = False at the start, and set it True at the end of each macro. However, I now have some big macros which call others as subroutines. This results in the screen updating for each pass thru a called macro, as the Application.Screenupdating = True statement is executed. Is there a means to repress this at the top level, or is there a better approach to take in writing code which will stop screen update for a macro, but not restart it each time the macro is called as a subroutine?
I have a very vast code with several subroutines based on selections from a userform.
I believe I have identified a loop that might be slowing down the process in the below:
Code: Dim n As LongFor n = 23 To 65 If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "INT" Then UserForm2.ListBox2.AddItem ThisWorkbook.Worksheets("record").Cells(2, n).Value End If If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "EXT" Then
Basically the code will go through each value (that can be only of those four instances) and put the title inside a different listbox.
This works, but it seems to be maybe too "step by step" and direct? Is there a way for it to skip after it found the corresponding value to the next N without checking for a match with the other items?
As you can see below, I've written code that writes random numbers into three columns of a spreadsheet (10 numbers in each column).
What I want to do is create code that will run the random number generator for a period of 1 minute and then stop. I know that I will need to write a timer subroutine to do this but I'm how unsure how to do this.
My problem today, is actually being able to call the routines. I have tried to make a generic setup, so that each subroutine to be called is named "Macro_[number here]". So Macro_1, Macro_2 etc. By doing this I am hoping to be able to call all the subroutines using a For Each code.
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed. Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?
I've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.
For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells Set wbDest = Workbooks.Open(rcell.Value) Set wbSrc = Workbooks.Open(rcell(1, 2).Value) wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress) 'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)" 'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"
VB: Private Sub Workbook_Open() Call Meetdata End Sub
It calls this macro which is in a standard module:
VB: Public firstvariablename As String Public secondvariablename As String Sub Meetdata() regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West") meetdate = InputBox("Enter the date of the Meet.", "Date of Meet") End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
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.
I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.
The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?
I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
My code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the
It seems to work. Are there any problems with it? Is there a better way?
Code: Function myRangePassExample(RangeA As Range) As Double Dim ArrayA As Variant, ArrayB As Variant Dim iLo As Integer, iHi As Integer, i As Integer Dim jLo As Integer, jHi As Integer, j As Integer Dim Sum As Single ArrayA = RangeA.Value iLo = LBound(ArrayA, 1) iHi = UBound(ArrayA, 1)
I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table. The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.
I have used my Excel Bible as well as MrExcel.com and tek-tips.com to write the current code I have... but it doesnt work! I think I am just missing a small piece, but I dont see what it is.
I have a macro that calls a userform - from the userform, a Customer is chosen. When the "extract" button is clicked on in the userform, it should pass the customer name to the next macro to do a bunch of stuff - including filtering the data by the customer chosen in the userform.
Here is the code - take a look and see if you can figure out why it doesnt work.
Code for "extract" button (I have tried several differnt variations - this is the current one):