+ Reply to Thread
Results 1 to 15 of 15

Turning off visuals when I use a macro button.

  1. #1
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Turning off visuals when I use a macro button.

    I am new to VBA so on my last project I used a macro to get parts of the code I am using. However, the code causes the screen to flicker back and forth between worksheets as some copying and pasting takes place. Is it possible to turn this flickering off and just have the code "do its thing?"

    Below is some of the code I used.
    Please Login or Register  to view this content.
    EDIT: I should mention I am using a button to initiate my code.
    Last edited by Brad4444; 04-05-2011 at 11:47 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Turning off visuals when I use a macro button.

    You can turn screen updating off but you would also need to get rid of all your select commands as they really aren't needed and avoiding them will make your code run a lot more smoothly. Can you upload a sample workbook or explain more clearly what the code is doing and can show you how it can be re-written.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Turning off visuals when I use a macro button.

    First and foremost get rid of Selects and Selections in you code ... it's bad programming that leads to lot's of problems.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Turning off visuals when I use a macro button.

    Nimrod,

    Re Selection I have quite a few bits of code in my personal.xls that run on the selected range so I wouldn't say that should be avoided if it's suitable.

    Dom

  5. #5
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Turning off visuals when I use a macro button.

    I have 4 worksheets that I deal with in the code. "FORMULAS, INPUTS, OUTPUTS, LOAD."

    In "LOAD" I take 3 user defined specifications (3 columns) and allow the user to make as many rows as they would like. Currently there are restrictions but I'm not ready to deal with them yet. As of now the code works.

    The "FORMULAS" worksheet takes one row at a time from "LOAD" and changes all necessary data. Then I copy that data from "FORMULAS" to "INPUTS" or "OUTPUTS."

    Please Login or Register  to view this content.
    EDIT: The point of my program is taking a little bit of user information and generating a specific sheet that manually would take hours to generate.
    Last edited by Brad4444; 04-05-2011 at 11:33 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Turning off visuals when I use a macro button.

    Hello Domski:
    People new to VBA use it because they don't know any better. Selects should be used when you want to take the user to a certain cell or sheet but beyond that the programmer should learn to reference without it. "Select" are the training wheels that new developers need to be encouraged to remove .

  7. #7
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Turning off visuals when I use a macro button.

    Quote Originally Posted by nimrod View Post
    Hello Domski:
    People new to VBA use it because they don't know any better. Selects should be used when you want to take the user to a certain cell or sheet but beyond that the programmer should learn to reference without it. "Select" are the training wheels that new developers need to be encouraged to remove .
    I will keep this in mind and try to update my code tonight after work. I'd prefer to do it the best way as I have moderate experience with other programming languages. (ie my job involves some programming.)

  8. #8
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Turning off visuals when I use a macro button.

    I agree learning to avoid select it is one of the revelations of learning VBA!

    Got to go now so can hopefully can leave the OP in your capable hands :-)

    Dom

  9. #9
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Turning off visuals when I use a macro button.

    Hello Brad:
    I've notice you looping with select and thought this demo might help. To try this out take a fresh sheet and put numbers in column A and then run macro. Notice the speed and no flicker.

    The two things to focus on in , in your first learning VBA , is to refer to books, sheets, cells without select .. and looping.

    Please Login or Register  to view this content.
    Last edited by nimrod; 04-05-2011 at 11:43 AM.

  10. #10
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Turning off visuals when I use a macro button.

    I added

    Application.ScreenUpdating = False

    and the program went from 15 seconds to half a second or less. I expected as much as I had done similar things in C++ such as displaying each step of the program.

    I will keep an eye on this thread if anyone has more advice and also I will work on getting rid of the selects. Thanks for your guys help.

  11. #11
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Turning off visuals when I use a macro button.

    nimrod: I've notice you looping with select and thought this demo might help. To try this out take a fresh sheet and put numbers in column A and then run macro. Notice the speed and no flicker.
    Thanks for that code. Very useful I can see.

  12. #12
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Turning off visuals when I use a macro button.

    Here's more example ... for this one make Sheet1 the activesheet, and put numbers in column A. When you run macro values will be modified and transfered to Sheet2.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    04-04-2011
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Turning off visuals when I use a macro button.

    Quote Originally Posted by nimrod View Post
    Here's more example ... for this one make Sheet1 the activesheet, and put numbers in column A. When you run macro values will be modified and transfered to Sheet2.

    Please Login or Register  to view this content.
    Cool. Just got back from lunch so I will be working on this project again. Can you explain the "xlUp" and are you calling it iRow because thats a standard name?

  14. #14
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Turning off visuals when I use a macro button.

    LastRow = Cells(65536, "A").End(xlUp).Row

    Start at Row 65536 Column "A" and go "up" until you get to data ... then take the "Row" number and assign it to the Variable "LastRow". Basically this is the same as type "End" and "up arrow" on your keyboard.

    iRow was the name I gave the Variable . You want to give variables name that make sense ... since the variable indicates a changing Row number I called it "Row" and since I had declared it a variable of type Integer I gave started it with a "i" ...BUT I could have called the variable xDonaldDuck and it still would have worked ... but then it wouldn't be as readable.

    BY-THE-WAY
    LastRow = Cells(65536, "A").End(xlUp).Row would be for active sheet while LastRow = sheets("Sheet1").Cells(65536, "A").End(xlUp).Row would be for the sheet named "Sheet1"..... when a specific sheet is not indicated it is assumed you mean the activesheet.
    Last edited by nimrod; 04-05-2011 at 01:31 PM.

  15. #15
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Turning off visuals when I use a macro button.

    Best to use Rows.Count rather than 65536 when determining the last row like that to take into account versions of Excel post 2003.

    Dom

+ 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