Is there anyway to lap the execution of the 3 lines in the below code (sample) say by 10 seconds each ?
Sub Macro1()
'
ActiveCell.FormulaR1C1 = "AAAAAA"
ActiveCell.FormulaR1C1 = "BBBBBB"
ActiveCell.FormulaR1C1 = "CCCCCC"
End Sub
I want to establish a loop in VB and within the loop I would like to pause execution of the loop and wait for input from a cell then resume execution of the loop once input has been entered.
View 9 Replies View RelatedI have macros that often pause for the user to take some action -- for instance, to confirm that the correct cells are selected, or to delete something. Usually I use the "Stop" command, which opens the VBA editor; when I finish doing the manual action, I just resume the macro.
When writing macros for actual users, I'd like to do the same thing without their having to deal with the VBA window. Is there a way to pause and resume action and remain in the Excel window?
I am looking for a way to measure how long it takes to complete a sub routine in VBA. I have code which posts data to matlab and then calls it back. What I need to know is how long does it take to complete each subroutine.
create a code which will measure this? I need to measure the time taken in 100ths of a second.
Is there any simple code to complete this? I am pasting a sample of my code which is using Matlab as a COM server.
Dim Matlab As Object
Dim MReal(10, 0) As Double
Dim i As Integer
Dim j As Integer
Dim MImag() As Double
Dim value As Double
Dim RealValue As Double
I am sure I woould need to declare the timer but I do not know where and I am not sure of the syntax.
I have this codes which will only trigger if I manually execute it. What do I need to do to trigger it automatically whenever the worksheet change.
Below is the codes:
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34
Case Is = 4, 5, 10, 20: myCol = 43
Case Is = 30, 40, 50: myCol = 6
Case Is = 70, 100, 140, 150
myCol = 5
myFontCol = 2
I have a problem with multiple, similar formulas in a macro. Each of these formulas, depending on the active cell, selects a certain range of cells and prints it.
The problem is, no matter which active cell I'm on, the macro activates all lines of the formula and separately prints each range. Is there a way to "separate" these lines in the formula so each range will print separately, depending on the active cell?
Here's the
Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:
View 7 Replies View RelatedHow can I add the pause between count & boxes?
VB:
s.Speak count & "boxes for scanning!"
I've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.
View 2 Replies View RelatedI need to pause for 0.5 seconds (half a second). How can I do that. applicatiom.Wait can only be used for whole seconds and a FOR ... NEXT loop is CPU dependant.
View 3 Replies View Relatedis there a code i can have where i can a pause a macro and then run it after 10 mins from that point?
View 5 Replies View RelatedI am running a macro that populates the same chart with variable data. I have approximately 40 sets of data and wish to pause or stop the macro to view one of the sets of data more closely. The pause option would be better but Stop option is acceptable
View 2 Replies View RelatedI'm looking for a simple solution to pause a VBA loop and then restart it from where it stopped.
Is it with a boolean value, i dont know the code.
I have an excel workbook right now with a 75x75 correlation matrix. It has 75 stock prices and uses bloomberg to pull in the correlation between each of the holdings. There is a lot of data so it takes roughly 35 seconds for it all to get pulled in from bloomberg. The input value that I can change is the date. The output value is a specific correlation coefficient. Thus, if I input today's date into a cell in the file, it will run the correlation matrix and tell me the correlation between all the holdings using the past year's data. Then it will produce a single output value.
I am now looking to use VBA to make this more robust. What I have been trying to do is have a macro which will -
(1) input the current date into the input cell
(2) have the code wait 35 seconds (and since it is using bloomberg pull in the traditional wait methods do not work because they actually stop the data pull in also)
(3) copy the output value
(4) paste special that value in a different cell
(5) repeat the process but this time use the date from a day before and have the output value copied and pasted to a cell below the previous one.
This loop would continue over 20 times.
Below I have some code that allows me to do this process once but is not looped and I have been stuck trying to make this work with a loop given the bloomberg delay. When I add a loop, it does not wait the 35 seconds I need before doing the whole process again.
[Code] .....
I've written a macro for work that will work with our billing process. After part of it has run, I'd like to pause it for the user to check a column for errors and possibly update a table to get rid of those errors, then after those errors are fixed, have the ability to continue with the rest of the code. I tried to make a message box to remind the user to look for the errors, but it wouldn't allow me to click anywhere except for on the buttons.
View 4 Replies View RelatedIs there a way a macro can be paused then resume running the macro by a keystroke, not a time delay.
View 3 Replies View RelatedI would like to add a pause to the following macro so that it inserts the word CANCEL 1 second after triggering. I know I need to use the Application.Ontime function but cannot work out how to integrate it.
Code:
Sub Cancel() Application.ScreenUpdating = False
Sheets("Sheet1").Range("T5") = "CANCEL"
Application.ScreenUpdating = True
End Sub
I'm recording a Macro but I want to test certain section before I complete it, so can I pause the recording and then restart the recording again?
View 9 Replies View RelatedI have a workbook with several hundred row the VBA code loops through. It takes about an hour to run all the calculations. Whenever I need to pause the script I hit ESC, which works well for me, but for other users it would be nice to have a command button that halts code. Even better would be another button to resume code.
View 9 Replies View RelatedI have a situation where I have an msg box pop up that asks the user to identify whether or not the file they have chosen meets the standards needed to continue running the macro. Specifically, I need them to answer "Yes" if the file has a list of data in the first column, if there are no blanks in that list and if the first row of data begins on Row 4. If they answer yes, the macro continues ahead and does what it needs to. If the person selects "no," another input box pops up that asks the user to make the edits. This msg box asks the user to click "ok" when they have made those edits. I want the macro to pause after the person has selected "no" from the first msg box, so that the person can make those corrections. Then, I want the macro to resume when the user selects "OK" on the second msg box. How might I do this? I have included the piece of my code below.
strresponse2 = MsgBox("Please confirm that the file you have selected meets the following standards:" & vbNewLine & "1. The information in the first column of this file is all of the job titles or job codes associated with this profile." & vbNewLine & "2. From the first job code or title to the last, there are no blank rows in this first column of data." & vbNewLine & "3. The first job title or code appears on Row 4, Column 1." & vbNewLine & "If the file you selected meets these standards, select Yes. If the file you selected does not meet these requirements, select No.", vbYesNo, "Yes/No")
If strresponse = 6 Then
Call getjobtitle
End If
If strresponse = 7 Then
'pause macro
strresponse2 = MsgBox("Please make the necessary edits to this file. When you are done, select OK to continue generating your job profile.", vbOKOnly, "OK")
If strresponse = 0 Then
'resume macro
Call getjobtitle
End If
End If
I am running a Showcase query from inside excel. I want the query to update, drop the data in excel save it and then close. The problem is that the save command is executing before the data is dropped. The Wait command pauses all processes so it won't work. Is there a way to have a minute of filler until the data is dropped so it will save?
View 5 Replies View RelatedI need to PAUSE my VBA code (about halfway through) in order to verify some calculations before I can allow the macro to finish running. If the numbers are correct, then I'd like the macro to finish running accordingly. However, if the numbers are incorrect, I need to be able to search my Excel file for the error (the error would be due to a format issue with the data that I pulled in), fix the error, and then let the macro finish running from the point it was paused.
Right now, I've got a Message Box that pops up asking me if the numbers are right and I have to answer YES or NO. I always answer NO so that the macro crashes -- this allows me to check the excel file without having to stop the macro. Once I've fixed the data problem, then I go back into my code and let it run to its conclusion.
I have searched and not found a solution. I have a project that requires the item to be printed on two different types of paper from a printer. The only way i have found to change trays is the sendkeys option. But it looks like sendkeys cannot send, then pause, then send. ex:
' first select the correct printer
Application.ActivePrinter = "\hagenCopy Room Savin 4045 PCL6 on Ne05:"
' next set change printing options, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}~%c3~", True
' next set change printing options to other color tray, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}{DOWN}~%c1~", True
How would one insert a working pause inbetween the two sendkeys (a wait just waits before the beginning of the first sendkey. and then the 2 try to execute at the same time calcelling both)
been checking out 1/2 a second wait instead of a 1 sec wait
and would like to know it you can do this
timer thing
Dim x As Single
x = Timer
While Timer - x < 0.5
Wend
more than once
it works the first time and then it sort of skips it
or is there ANOTHER way of having a delay of less than a second?
I have an inputbox that asks a question. If the user enters an incorrect response, a messagebox appears telling them to enter the correct word and returns to the inputbox. The code works fine, however the messagebox doesn't disappear after they click OK. Is there a way hide or get rid of the messagebox once they click OK or Cancel? My code is below:
inputboxVorD:
VorD = Application.InputBox("Is this for Voice or Data?", "Voice or Data", Type:=2)
Application.DisplayAlerts = False
Select Case VorD
[Code]....
I am trying to fix a problem that I am having with Bloomberg add-in for Excel. Each day I populate 31 days of historical price data for 500+ stocks.
$A$K3 = "ACE US EQUITY"
$AL$3 = "=BDH(AK3,"PX_LAST",$D$1,$C$1,"DTS=h","dir=h")"
$C$1 = "=WORKDAY(TODAY(),0)"
$D$1 = "=BAddPeriods(C1,"NumberOfPeriods=-31","Per=wd","CDR=#A")"
What is supposed to happen is that in $AL$3:$BQ$3 there will be prices for 31 days.
I leave the workbook open as the Bloomberg DDE session remains open for 3 days even if the Bloomberg executable is not running.
Sometimes there is no problem and when I come into work the following day, the data is shifted forward by one day. Other times, much of the data is missing and I am forced to select and refresh each $AL$_ field. The problem with this is that for some reason the refresh is happening synchronously and I think this is causing havoc on the DDE connection and/or Excel. Resulting in slow/no updates. Also, if I refresh the worksheet some formulas will be overwritten with static data!
What I would like to do is create a macro that will:
1) clear all data in $AL$_:$BQ_$ & $BS$_:$CX_$
2) set the value of $AL$_ = "=BDH(AK$_,"PX_LAST",$D$1,$C$1,"DTS=h","dir=h")"
3) wait until the data is populated into $AL$_:$BQ$_ before going to $AL$(+1):$BQ$(+1); if $AM$_ has a value then most likely the data has populated in that row.
4) then when $AL$_:$BQ$_ is finished, do the same for $BS$_:$CX$_
I have been waiting for Bloomberg support to fulfill this request for over one month now with no results.
I have a very minute amount of code below:
PHP Code:
Sub PopulateData() PopulateData Macro Range("AL2:BQ2").Select
Selection.ClearContentsRange("AL2").SelectActiveCell.FormulaR1C1 = _ "=BDH(RC[-1],
""PX_LAST"",R1C4,R1C3,""DTS=h"",""dir=h"")"Range("AL2").SelectEnd Sub
I have the following piece of
Sub Summarise_Variations()
Dim myRange As Range
Dim Write_Cell As Long
With Application
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
End With
It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.
Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.
The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.
I have a multipage form that I'm using to create a wizard that accepts data from the active workbook via a refedit box, however, when I try to execute the entire form freezes at runtime. The editor will bring up the workbook but the entire workbook freezes.
I cannot click any of the buttons on the form, exit the form, input the range into the refedit box, or even click on any cells in the worksheet. Everything was working perfectly fine until I started adding command buttons to other pages of the multipage form, but no code has been added.
I don't even have code to link the pages together, all I did was add command buttons and change the names on them. I'm having a hard time believing this is what's causing excel to freeze up, but I haven't done anything else. I've checked my loops and they are fine.
I've also gone back to another copy and deleted everything on the other pages and left only page 1, but this is still failing.
I have a problem with the following code
Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok
' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok
This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.
If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.
I have a macro that opens various files (via a loop) and then goes to each worksheet (via another loop) and looks through each row of data (a third loop).
Each worksheet can have over a thousand rows of data. Once I hit the button for the macro to run, it runs without any error messages, but it seems to take forever.
When I hit escape twice to interrupt the macro and then press the 'play' button, it zips through and finished running the macro really quickly. The interruption point is generally somewhere within the third loop.
It's almost as though it falls asleep and I have to wake it up.