+ Reply to Thread
Results 1 to 6 of 6

How to streamline userform coding for faster run time.

  1. #1
    Registered User
    Join Date
    07-12-2023
    Location
    Sulligent, AL
    MS-Off Ver
    2023 Office 365
    Posts
    2

    Unhappy How to streamline userform coding for faster run time.

    Hello,

    I made a userform that allows a person to input a Carrier#/Color/Size/Part# and save it to an excel table. I have it setup to where the person running the code is unable to access the data table or make changes to it, it also saves the excel sheet after each input is submitted. On my laptop the form runs fine, but when I place it on the operators computer; it is laggy and slow to run and will show "Not Responding" due to the load times between submissions.

    I have ScreenUpdating set to False but it did not help. I attached the excel sheet, I am not sure what can be done to make the code more efficient.


    Any advice would by great! Thanks!
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: How to streamline userform coding for faster run time.

    Usually when there are efficiency issues the first place I look are loops. You do not have any loops in your code.
    On my laptop the form runs fine, but when I place it on the operators computer; it is laggy and slow
    I suspect this says more about the operator's computer than your code. Do you know the specs of the computer?

    By the way the indentation in this code is a bit eccentric, making it difficult to read. I also recommend you rework your code to get rid of the GoTo statements. That is probably not causing your issue but is a poor coding practice. Here is a rewrite of one of your subs.
    Please Login or Register  to view this content.
    The Call statement is obsolete and retained for backwards compatibility. It is not required to call a sub. You can just use the name of the sub.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-12-2023
    Location
    Sulligent, AL
    MS-Off Ver
    2023 Office 365
    Posts
    2

    Re: How to streamline userform coding for faster run time.

    Hey Jeff thanks for the advice! The computer is an older lenovo mini cpu, it is not very powerful at all. Which is why I wanted to try and make the program as streamline as possible.

    I have the
    Please Login or Register  to view this content.
    functions at the end of the checks because before it would move through and check each input but it it was missing one of the middle values (color or size) it would still submit the input. This was just a quick fix to ensure no field would be left blank.

    Also for the call functions all I would need to do is type "Save" or "Clear" to call the function?

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: How to streamline userform coding for faster run time.

    Quote Originally Posted by 6StringJazzer View Post
    ...The Call statement is obsolete... You can just use the name of the sub.
    One small proviso: if you need to pass parameters to the called sub or function, the two methods vary slightly.
    CALL MyFunction(a, b) requires parenthesis
    vs.
    MyFunction a, b no parenthesis... unless you're using the return value
    vs.
    t = MyFunction(a, b)
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: How to streamline userform coding for faster run time.

    Quote Originally Posted by paintman View Post
    Also for the call functions all I would need to do is type "Save" or "Clear" to call the function?
    Yes but I'm a little concerned about having a Sub named Save since that is also a method of Workbook. If what you have works I wouldn't remove the Call.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,915

    Re: How to streamline userform coding for faster run time.

    Quote Originally Posted by 6StringJazzer View Post
    If what you have works I wouldn't remove the Call.
    There will be no difference either way. It would be better to rename the routine though, IMO.
    Rory

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Form Control Start time & End time button enable or disable in sequence coding issue
    By sboy72817 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-06-2016, 07:05 AM
  2. Extracted Coding Much Faster When Isolated (Memory Problem?)
    By Doug Nguyen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2014, 05:04 AM
  3. First time UserForm user, need help coding my userform so I can collect data.
    By Crunketh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2013, 02:40 AM
  4. [SOLVED] Calculating Time in Userform Textbox and Filtering Comboboxes Coding Help
    By hiddenupnorth in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2012, 01:12 PM
  5. Coding Styles - Making VBA "FASTER SMALLER BETTER"
    By exc4libur in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-02-2011, 01:18 PM
  6. faster method for saving data from userform to worksheet
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-01-2010, 02:21 PM
  7. time:enter time faster for instance
    By darkbearpooh1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-15-2006, 12:19 PM

Tags for this Thread

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