2
\$\begingroup\$

Sadly enough I have a bit of code that is really slow and being a newbie in programmation, I don't have the faintest clue on how to optimize. Is there any way to make those calculations faster?

This code calculates the worst case of some parameters cycling through AktBeam and FileCounter (those cycles contain a LOT of other stuff, and I'm sorry to say that I cannot include the whole code) and inside a circumference (have a look at the select case block), and stores them in the BT and EC arrays.

AmplCo, AmplCr, PhaseCo and PhaseCr are arrays containing double variables.

I'm looking for all the possibilities here to make my processor perform the least amount of operations (e.g. if x^2 is worse than x * x, I'd use x * x).

Please notice that I cannot split the arrays in any way.

For PhiDrehung = 0 To 2*Sector-1 Step 3
    A = 0
    B = 0
    C = 0
    D = 0
    EC_Worst = 0
    BT_Worst = 0
    For k = 0 To FensterTheta
        For i = PhiDrehung-FensterPhi/2 To PhiDrehung+FensterPhi/2
            Select Case i
                Case Is < 0
                    l = 360+i
                Case Is > 359
                    l = i-360
                Case Else
                    l = i
            End Select
            A = A + AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k) * Cos(PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)) + AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k) * Cos(PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k))
            B = B + AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k) * Sin(PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)) + AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k) * Sin(PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k))
            C = C + (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2)
            D = D + (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)
            BT_Worst = BT_Worst + (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2) / (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)
        Next
    Next
    EC_Worst = ((A^2 + B^2) / (C*D))^0.5 * 100
    If EC_Worst > EC((FileCounter-1) / 2, AktBeam) Then EC((FileCounter-1) / 2, AktBeam) = EC_Worst

    BT_Worst = 20 * Log( BT_Worst / (((FensterTheta+1)*(FensterPhi+1)) * ((PRad(FileCounter-1, AktBeam) + PRad(FileCounter, AktBeam))/2)) ) / Log(10)
    If BT_Worst > BT((FileCounter-1) / 2, AktBeam) Then BT((FileCounter-1) / 2, AktBeam) = BT_Worst
Next

FensterTheta and FensterPhi are user-inserted values, let Sector = 180.

\$\endgroup\$
5
  • 2
    \$\begingroup\$ Welcome to Code Review! As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. \$\endgroup\$ Commented Oct 18, 2016 at 6:31
  • 1
    \$\begingroup\$ Please read Vogel612's comment, as well as How to Ask. Titles such as "How do I optimize X?" are not acceptable on Code Review as they apply to nearly every single question. The desire for optimization is implied for all questions on the site. The site standard is to state what your code accomplishes, rather than your concern about the code. \$\endgroup\$ Commented Oct 18, 2016 at 8:13
  • \$\begingroup\$ As you guys wish. My only problem here, though, is that this cycle takes a long time to be executed and, let me stress it again, that I'd like to optimize in a certain way: not the syntax, not the style, but the least amount of operations required. \$\endgroup\$ Commented Oct 18, 2016 at 8:31
  • 1
    \$\begingroup\$ I'm still not clear on what a "worst case angle" is. Is this the smallest angle? The largest? Something else? What constrains it? What do you know going into the problem? This is likely to be especially important here, as I suspect many of us won't recognize the meaning of things like Drehung and Fenster. \$\endgroup\$ Commented Oct 18, 2016 at 10:34
  • 1
    \$\begingroup\$ I think you misread the title. It's not a worst case angle but the worst case of two values (EC and BT) in a certain angle (which is normalized to 360° - so that for example -30° = 330°). Phi Drehung, FensterPhi and FensterTheta are manual inputs \$\endgroup\$ Commented Oct 18, 2016 at 10:49

1 Answer 1

Reset to default
3
\$\begingroup\$

Your code already seems to do only the steps necessary, slow performance seems to be the limitation of VBA.

You may do some minor improvements however:

Select Case i

You don't need to check conditions here, it's a simple Mod operation:
l = Application.Evaluate("=MOD(" & i & ",360)")

Formulas

Your calculations contains a lot of duplicates, you can use temporary variables to reduce number of calculations:

t1 = AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k)
t2 = PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)
t3 = AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k)
t4 = PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k)

A = A + t1 * Cos(t2) + t3 * Cos(t4)
B = B + t1 * Sin(t2) + t3 * Sin(t4)

t5 = (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2)
t6 = (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)

C = C + t5
D = D + t6

BT_Worst = BT_Worst + t5 / t6

This halves the number of array accesses in your code (so best case you can get double speed).

As I've written already, there are limitations in VBA too. Further improvement could be done implementing your code in e.g. Visual Studio and compile as an Excel add-in. (That case you could avoid run time compilation and debugging which are the biggest issue in VBA).

\$\endgroup\$
5
  • \$\begingroup\$ These are some great tips, thanks a lot! Before I select your answer as the correct one, may I ask you if you know (I couldn't find anything anywhere) if x * x is better than x^2 ? Also, I have no clue if it is possible to compile che code in Visual Studio, but I'm not using Excel (that begs the question: will the Evaluate command work?)... Instead I'm working on a proprietary program, that allows VBA programmation. This program puts at disposal a number of libraries that I must use to interpret their format of file... \$\endgroup\$ Commented Oct 19, 2016 at 6:35
  • \$\begingroup\$ Ok, just found out that the Evaluate doesn't work, and also the operation x Mod y is possible but doesn't take in account the negative angles... So I had to insert an If l < 0 Then l = l + 360. I wonder at this point if the Select Case is more efficient. \$\endgroup\$ Commented Oct 19, 2016 at 7:17
  • 3
    \$\begingroup\$ You don't need to use Evaluate to get the Mod function. See VBA equivalent to Excel's Mod function \$\endgroup\$ Commented Oct 19, 2016 at 18:01
  • \$\begingroup\$ Read the comment above please. \$\endgroup\$ Commented Oct 20, 2016 at 5:46
  • \$\begingroup\$ "if x * x is better than x^2 " - as you've also more complex calculations, even if there's slight difference I don't think that would make any real improvement. \$\endgroup\$ Commented Oct 20, 2016 at 5:59

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.