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 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.
I have a function which will allow me to search for combination of numbers which will sum to zero. However, because the range could be as long as 300 rows or even the possibility of not finding the combination that will sum to zero, the macro will take ages to complete or even causes the whole excel to hang. So What i want is to instruct macro to stop doing the search if it cannot find the results after say 20 minutes of searching.
I have created two userforms which allows people to log-in to the worksheet.
The code works great on the first go, sometimes even twice, but after a while I get taken to the VBE and shown the message "Code execution has been interrupted". And highlights a certain part of my code (highlighted in red below).
I will copy all of the code relating to the userform below.
Code: Private Sub CancelButton_Click() Unload Me End Sub Private Sub ClearButton_Click() TextUsername.Value = "" TextPassword.Value = "" End Sub
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.
Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.
Sub Refresh_Queries() Dim SheetNum As Integer, ListSheet As Integer Dim eqtQT As New EventfulQTable Dim QueriesDone As Boolean
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:
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
Now..when I run any of my macros, I get the following message.. "Code Execution has been interrupted".
I'm not sure why I'm getting this message but it happens everytime I run ANY macro. Note that if I hit "Continue" every time it gives me the option, I am able to successfully run the macro, but obviously, I shouldn't have to do this.
I wrote a very simple macro in an Excel file a few months ago. Worked great. Worked on my computer, and worked on a dozen other people's computers perfectly. Now, months later, ONE person is getting the error message 'Code execution has been interrupted' every time she runs the macro. I've tested it on my computer with Excel 2007, and it worked. Tested it on another computer with Excel 2003 (offending computer has Excel 2003), and it worked again. So I tested a third computer on Excel 2003, and it worked again. I'm 100% positive the macro code isn't causing this error. This makes me think that it's related to an application setting that was changed somehow.
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.
Basically, in the "Thisworkbook" code , i have some code in the Workbook_BeforeClose section. Currently , it autosaves the workbook in a folder i have specified.
However, i need to add some code.I want to check that a certain cell has a value in it before the user closes the workbook, and if the cell is empty, show a messagebox asking him to enter a value.
I know how to get a messagebox to pop up, the only thing is once the user clicks the OK button, i need the rest of the code execution to pause, allowing him to make the change then if he clicks the "X" (top right of the screen) to close the file or application, the filesave dialog appears and he can then save the document.
how to go about this because at the moment when user clicks ok, the messagebox just disappears and filesave dialog appears and he doesn't have a chance to edit the cell.
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
I have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click() Dim Val Val = "6" Dim valand As Range Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0) valand = Val Dim A As Range, B, C, d, E, F, G, H, I, J Set A = ThisWorkbook.Worksheets("Stageing").Range("K2") Set B = A.Offset(1, 0) Set C = B.Offset(1, 0) Set d = C.Offset(1, 0) Set E = d.Offset(1, 0) Set F = E.Offset(1, 0) Set G = F.Offset(1, 0) Set H = G.Offset(1, 0) Set I = H.Offset(1, 0) Set J = I.Offset(1, 0) TextBox1.Value = A & B & C & d & E & F & G & H & I End Sub
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade() Dim icolor, Navn As Integer Dim TargetRow, LastRow, Previous As Long Dim Target As Range MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen" Range("A5").Activate Range("A5"). CurrentRegion.Select 'Set range ActiveCell.Offset(Selection.Rows.Count, 0).Activate LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
I am using the below to open a text file and copy the used range to my working workbook. The opening of the file makes the code slow and and also it may be bcoz i am trying to paste 11800 lines of data from one file to another.
Also i can see the flickering of the screen when the files are open. How to make the code faster and more efficient.
Code: Sub readSimFile() Application.ScreenUpdating = False '//code// Application.Calculation = xlCalculationManual
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros() Dim WorkbookRust As String WorkbookRust = ActiveWorkbook.Name ChDir "C:Documents and Settings vogtMy DocumentsRŁstplausch" Workbooks.Open Filename:= _ "C:Documents and Settings vogtMy DocumentsRŁstplauschCH_Revenue_2008.xls" Sheets("Main_Overview").Select Windows(WorkbookRust).Activate Application.run ActiveWorkbook.Name & "!UpdateEntries" Application.run ActiveWorkbook.Name & "!FilterMain" 'not ask to overwrite existing file Application.DisplayAlerts = False Workbooks("CH_Revenue_2008.xls").Save Workbooks("CH_Revenue_2008.xls").Close End Sub
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
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.