+ Reply to Thread
Results 1 to 15 of 15

Running Macro Overrides Manual Commands

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Running Macro Overrides Manual Commands

    I have the following macro that copies and pastes live stock market data all day on my computer. If I am working in another workbook while this macro is running, then it is very difficult to copy and paste anything because the macro overrides my manual commands. Is there a way to modify the code to avoid this?


    Please Login or Register  to view this content.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Running Macro Overrides Manual Commands

    I guess this suffices.

    Please Login or Register  to view this content.
    Last edited by snb; 09-24-2010 at 08:54 AM.



  3. #3
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Running Macro Overrides Manual Commands

    I inserted the code and the following part is resulting in a syntax error.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Running Macro Overrides Manual Commands

    There should not be a period after Workbooks.
    If you run a macro every ten seconds, it stands to reason it is going to interfere with other work. Can you not run it in a separate instance of Excel?
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Running Macro Overrides Manual Commands

    I would need to run it on a different computer correct? The macro effects my work in MS Word, Explorer ect..

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Running Macro Overrides Manual Commands

    In that case, yes.

  7. #7
    Registered User
    Join Date
    09-18-2010
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Running Macro Overrides Manual Commands

    Actually I am currently working on solving the same problem. So I know what you are referring to. The problem (at least in my case) is the “clipboard” is used during any copy command. I believe there is only 1 clipboard per computer. When you use the word “copy” in your code it uses the clipboard. The time it takes to get to paste it is when it is vulnerable to any other program also using the “copy” command. Or also if you happen to be copying manually with CTRL-C. It may trash your manual copy procedure or worse yet crash or trash your macro running without your knowing all the effects. Sometimes it will crash my macros running or corrupt their data (perhaps without my knowing it).

    There are a couple ways to solve this. None are easy (at least not for me). One is to rewrite all your copy commands so that that do not use the “copy” command (which uses the clipboard) and do cell reference transfers only. Such as :
    Please Login or Register  to view this content.
    The above row and column copying version does not use the clipboard, and would replace the usual code below:
    Please Login or Register  to view this content.
    If you do not have a lot of code to rewrite, that would be the easiest way. There are many adaptations to that where you can copy large ranges too using “resize” etc. If using this version, I so far have learned that you will then be able to use your manual version of copying as much as you like “without” any interference at all from your macros running all day downloading stock data and copying (transferring) it many places. But the first time you use code with that word “copy” the clipboard again will become open season for getting trashed or your manual copying getting trashed.

    In my case I have many different Excel macro’s running all day. My code uses literally hundreds of copy commands in very complicated ways. So the task to rewrite and debug them all is too large to do. So I am now working on a way to keep the clipboard copying version, but turn off the clipboard access by other programs. But during those milliseconds (or whatever seconds) of copying being done, this will not allow my manual copying to work (just copies nothing when I paste). That manual copy is no big deal as I just manually recopy it again and it is ok. The code for this was supplied to me by someone on an other forum. I am currently testing it and so far it works really well. Here is the link for the code and discussion to protect the clipboard copying when done from a macro:

    http://www.mrexcel.com/forum/showthread.php?t=495746

    But I also have another problem, and that is I have many different Excel workbooks running all day that if I protect the clipboard in one Excel program and another Excel program at the same time needs the clipboard, it will crash and stop because it is not able to copy.

    So I am now trying to find the code to be able to see if any other workbooks are currently running a macro. And if they find a macro running, then they simply wait for any other macro to finish before running its macro. This will work because my macro query every minute, but only run for 5 seconds at a time.

    I have not found the code yet that will let me know if any other workbooks are currently running a macro.

    Hope what I have found and the very helpful code supplied to me will be of assistance to you too.

    Chuck
    Last edited by chuckchuckit; 09-25-2010 at 02:43 AM.

  8. #8
    Registered User
    Join Date
    09-18-2010
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Running Macro Overrides Manual Commands

    We resolved the problem through that link I noted which is at:

    http://www.mrexcel.com/forum/showthread.php?t=495746

    It might take a bit to understand all that is going on there, but I do not need to try to see if macros are running etc. Last code there has a part where it waits until clipboard is freed up before it uses it.

    Whne using that code, the only thing will be that your manual copy and paste will not work during the clipboard protection time (milliseconds to seconds depending upon how you use it). It may or may not let you know. Sometimes I get a message saying could not copy when I try to manually use CTRL-C. But no big deal for me, I just copy it again.

    The important thing for me was that the automated macros would continue to run without being corrupted. And that code does fix that really well.

    Hope it helps.

    Jaafar did a lot of work on it for us. Our thanks to him.

    Chuck
    Last edited by chuckchuckit; 09-25-2010 at 02:49 PM.

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Macro Overrides Manual Commands

    And does this also solve your other posts?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  10. #10
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Running Macro Overrides Manual Commands

    chuckchuckit- Thanks for your input. I will have to take a closer look at my code and try to re write it with your suggestions. As you can see my code is not very long so I will try to change it to this format Cells.Item(1, 2) = Cells.Item(2, 2). I am not a vba expert so this may take me some time but thanks for your help!

  11. #11
    Registered User
    Join Date
    09-18-2010
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Running Macro Overrides Manual Commands

    rhudgins - I did rewrite certain sections of my code that were the most vunderable to being corrupted because they were using the "copy" command. And here is link to discussions by people on this forum who helped me to learn how to copy "without using the clipboard".

    http://www.excelforum.com/excel-prog...same-time.html

    It goes into many different ways to do the data transfer between cells and ranges without using the clipboard (not using the "copy" command).

    Here are some examples they gave to me without clipboard use:
    Please Login or Register  to view this content.
    This next one uses row and column cells format:
    Please Login or Register  to view this content.
    And this next one they gave me was likely the most powerful use one. But I never fully tested it as I ran out of time to learn it. But I think it resizes the destination too per the source size. If so then this one likely the most efficient:
    Please Login or Register  to view this content.
    I've only been coding about 6 months (used to program in C about 15 years ago), so am new again. Wish I had found this forum months ago, but this is the book I was able to find everything in:

    "Excel 2007 VBA Programmer's Reference" 4 authors last names are Green, Bullen, Bovey, Alexander.

    Programming is a blast. (That's what I keep telling my wife...).

    Have fun!

    Chuck

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Running Macro Overrides Manual Commands

    To adjust the destination range to the source range use:

    Please Login or Register  to view this content.
    applied to a range
    Please Login or Register  to view this content.
    applied to currentregion
    Please Login or Register  to view this content.
    Last edited by snb; 09-30-2010 at 04:24 AM.

  13. #13
    Registered User
    Join Date
    09-18-2010
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Running Macro Overrides Manual Commands

    snb – Thanks very much for your examples. That is very helpful as I tested it a bit more and the “with” approach works extremely well. I think I shall adopt it as my standard copying way now without using the clipboard during copying.

    Using your “Range” example, “Range” can also be applied to destination ("E3") such as:
    Please Login or Register  to view this content.
    Extremely easy to use (now that I fully understand how it is used). All a person has to do there is change the Range locations to use this code to copy any range to a different worksheet. Top line Range is source, and 2nd line Range is destination, and destination only needs just the upper left location for its range. As the rest of the code does the resizing of destination automatically. Very efficient code it seems.

    All 3 examples above can be modified the same way.

    Thanks much.

    Chuck

  14. #14
    Registered User
    Join Date
    09-18-2010
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Running Macro Overrides Manual Commands

    I tested these 3 versions of copy and paste "without using the clipboard" and they have the same result.

    Please Login or Register  to view this content.
    They all work well!

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Running Macro Overrides Manual Commands

    Cfr. the second post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1