Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 72 of 72

Thread: Excel Slow performance

  1. #61
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,842
    Location
    Quote Originally Posted by Svmaxcel View Post
    I got error Subscript out of range
    Well, that helps a lot. There's over twenty subscripts in that code.

    How about showing the code you're testing with a comment to tell where the error is occurring?
    Please take the time to read the Forum FAQ

  2. #62
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,600
    Location
    Quote Originally Posted by Svmaxcel View Post
    You are Brain Child using your programming language skills, I am learning VBA and keep on learning by looking at codes.
    I cannot be as smart as you in snb,
    I removed Option Explicit
    There's been numerous 'discussions' about using Option Explicit and explicitly Dim-ing all variables with a specific Type when ever possible

    Option Explicit tell VBA that all variable have to be Dim-ed and to me that helps eliminate silly errors

    With out Option Explicit this will return nothing when you run the macro since 'asn' is NOT 'ans'

    ans = 2 x A(1,2)
    MsgBox asn
    With Option Explicit that would generate an error that could be fixed before running the macro

    Option Explicit
    
    Dim ans as Long
    
    ans = 2 x A(1,2)
    
    MsgBox asn   ' <<<<<< Compile time error
    
    MsgBox ans   ' <<<<<< OK

    Same error proofing applies to always Dim-ing variables with the type that the are

    It's easy to just Dim everything as Variant, or just leave off the 'As String' part, but if the macro is relying on a variable being a number and you accidently assign a string to it, many lines later


    In 'One' the error doesn't show up until many likes after the bad input
    In 'Two' the error shows up on the line that generated the error
    In 'Three' the code works the first time, but fails the second time

    To me, it's easier to debug a problem with Option Explicit and accurate Dim-ing. Maybe eventually I'll get so good I don't make errors like that but intel then, I like to use them

    Option Explicit
    
    Sub One()
        Dim a, b, c As Variant
        
        a = "asfsadf"
        b = 123
        
        'many. many, many lines
            
        c = b * a   ' run time Type mis-match error
        MsgBox c
    End Sub
    
    Sub Two()
        Dim a As Long, b As Long, c As Long
        
        a = "asfsadf" ' run time Type mis-match error
        b = 123
        
        'many. many, many lines
            
        c = b * a
        MsgBox c
    End Sub
    
    Sub Three()
        Dim a As Long, b As Long, c As Long
        
        Range("A1").Value = 10
        a = Range("A1").Value ' No run time Type mis-match error
        b = 123
        
        'many. many, many lines
            
        c = b * a
        MsgBox c
        
        'later
        
        Range("A1").Value = "asdfasdf"
        a = Range("A1").Value ' run time Type mis-match error
        b = 123
        
        'many. many, many lines
            
        c = b * a
        MsgBox c
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #63
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,600
    Location
    Quote Originally Posted by Svmaxcel View Post
    Reports which I would be sending would be very confidential.
    I wanted a (CONFIDENTIAL) Stamp with high transparency and little Slanted to be in between the report.
    How can that be done.
    I mean on several documents we have seen things like Confidential or Destroy after use.
    Excel doesn't have a Watermark capability (like MS Word).

    The workaround is to use an image

    In Help search for 'Add Watermark' - just in case, I added directions in the attachment
    Attached Images Attached Images
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #64
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,496
    Quote Originally Posted by Svmaxcel View Post
    I got error Subscript out of range
    Your comment isn't helpful.

  5. #65
    That was really helpful.
    Now I understand better about Option Explicit, Dim-ing, Variants
    Thanks a ton Paul.

  6. #66
    Quote Originally Posted by Paul_Hossler View Post
    In my approach, it's only update rows in the selection that get updated.
    Since you have so much data and it seems to be added daily, there did not seem to be a reason to re-compute thousands of lines of data. Just process the new data


    I selected A2:A51 and ran the macro and all 2:51 gray areas were filled in

    If you select A17 and run the macro, only row 17 is processes


    If you really want to re-process all the data, that's easy to change

    Deleted dups in roster also
    Can you also tell me the option to recompute entire sheet?

  7. #67
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,842
    Location
    option to recompute entire sheet?
    Select the entire sheet.
    Please take the time to read the Forum FAQ

  8. #68
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,600
    Location
    Quote Originally Posted by Svmaxcel View Post
    Can you also tell me the option to recompute entire sheet?
    I don't think Selecting the entire sheet will do it

    Try


    Worksheets("Sheet1").Calculate
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #69
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,842
    Location
    I thought "re-compute" meant re-process and your code processed the Selection.
    Please take the time to read the Forum FAQ

  10. #70
    I mean that I don't want to select the range and excel should automatically check last used row and fill in the results

  11. #71
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,842
    Location
    Let us call what Paul's code does a "Process," or "Processing" so we can avoid confusion.

    Applicable Range is that Range that was Processed, and/or needs Processing. I think what you meant when you said, "Entire sheet."

    As I understand, the Process converts formulas to values.

    One can set up Paul's code to Process: A Selection; The entire applicable Range; Or only the unprocessed, (new,) Cells in the applicable Range.
    Set Start = Range("A:A").Find(What:="=", LookIn:=xlFormulas)
    Please take the time to read the Forum FAQ

  12. #72
    Fabulous!!!
    Marking as Solved

Posting Permissions

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