Consulting

Results 1 to 5 of 5

Thread: How to simplify vba code?

  1. #1

    How to simplify vba code?

    1 2 3 4 5    
                 
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
    2 1 2 1 10 76 0.083333
              912 1

    ARRAY.xlsm
    I used three times of arrays in the worksheet to complete my work, may I ask someone to guide me to simplify this code?

    If a3>a1, a3*a1;
    If a3<a2,a3*a2*2 ..............


    76=a3*a1+b3*b1*2+c3*c1*2+d3*d1*2+e3*e1

    0.083333=76/912

    I hope it is clear.
    Last edited by idnoidno; 05-28-2017 at 06:47 AM.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Why not add explanatory comments to your code to make life a little easier.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,337
    Location
    Another thing that would greatly help other people help you would be to

    1. use meaningful and self-documenting variable names (what is 'taa' and 'mrr' and 'rg'?) 'tempsum' I can guess at

    2. Use explicit Types (Dim mrr As Variant, Dim rg as Range, etc.)

    3. Use indenting and blank lines to 'block out' the lines that make up a logical piece of functionality



    Option Explicit
    
    Sub taa()
    
    Dim mrr
    Dim i As Integer, j As Integer
    Dim rg As Object
    Dim tempsum
    
    With Worksheets("Sheet1")
        Range("f:f").ClearContents
    
        Set rg = Range("a1:f" & Cells(Rows.Count, "A").End(xlUp).Row)
        mrr = rg
        
        For i = 3 To UBound(mrr)
            tempsum = 0
            For j = 1 To UBound(mrr, 2)
                If mrr(i, j) > mrr(1, j) Then
                    mrr(i, UBound(mrr, 2)) = mrr(i, UBound(mrr, 2)) + mrr(i, j) * mrr(1, j)
                Else
                    mrr(i, UBound(mrr, 2)) = mrr(i, UBound(mrr, 2)) + mrr(i, j) * mrr(1, j) * 2
                End If
            Next j
            tempsum = tempsum + mrr(i, UBound(mrr, 2))
        Next I
        
        rg = mrr
        Set rg = Nothing
    End With
        
    Dim mrg As Object
    Dim brr As Variant
        
    With Worksheets("Sheet1")
        Set mrg = Range("f1:g" & Cells(Rows.Count, "f").End(xlUp).Row + 1)
        brr = mrg
        For i = 3 To UBound(brr) - 1
            brr(UBound(brr), 1) = brr(UBound(brr), 1) + brr(i, 1)
        Next I
        mrg = brr
        Set mrg = Nothing
    End With
        
    Dim nrg As Object
    Dim crr As Variant
        
    With Worksheets("Sheet1")
        Set nrg = Range("f1:g" & Cells(Rows.Count, "f").End(xlUp).Row)
        crr = nrg
        For i = 3 To UBound(crr)
            crr(i, 2) = crr(i, 1) / crr(UBound(crr), 1)
        Next I
        nrg = crr
        Set nrg = Nothing
    End With
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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. #4
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    362
    Location
    does the code work?

    move all of your Dim statements to the top of the code module
    declare them once only, and then use 'set ... = .... to repurpose them. comment the line as well to make sure you can track the change to object easily

    put your two formulas into two functions, and call them from the main routine. this will declutter your main module and make it easier to debug
    -> create variables for each formula input and pass the variables to the function. this again will help with any debugging.

    you could move your two 'with worksheets' blocks into functions. you will need something like 'private function MyFunction1(var1 as long, var2 as long, etc) as object' and set mrg = myfunction1(var1, var2 etc). this will declutter, as well as modularize your code, and again help with readability and debugging.

    you also need to add comments. at minimum, use comments as 'chapter headers' - ie state what each section of code should do before you begin to write it. Readable code does not need as many comments, and good code makes extensive use of subroutines - with one subroutine doing 1 step of the code. (short modules are more readable too)

    You can play with passing variables byref instead of byval. this can be powerful, but I'm not sure of pitfalls and best practice - you might like to read more on this.
    Remember: it is the second mouse that gets the cheese.....

  5. #5
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    362
    Location
    for the dimming and setting of variables, I didn't read this properly - you don't reuse them

    give your variables more descriptive names, and use 'snake case' with the first letter a lowercase letter.
    I regularly use
    -mySheet, srcSheet, destSheet
    -myRow, lastRow
    -myCol
    -myRange1, myRange2 etc.

    always type this in as lowercase, and if the word does not autocapitalise, you have a typo.
    the variables then become self-commenting as well
    Remember: it is the second mouse that gets the cheese.....

Posting Permissions

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