How to simplify vba code?

[idnoidno]

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



19311
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.

[mdmackillop]

Why not add explanatory comments to your code to make life a little easier.

[Paul_Hossler]

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

[werafa]

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.

[werafa]

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