Excel VBA continually returning 0

Question

I'm trying to calculate a cross currency rate by simply summing the forex rates for A/B and B/C and multiplying the means to find the A/C rate and I keep getting 0 in return. This is the code:

Function forex(audData As Range, euData As Range)

    a = Application.Count(audData)
    e = Application.Count(euData)
    'Counts how many values are in the data

    aSum = 0
    eSum = 0
    aMean = 0
    eMean = 0

    For i = 1 To aud ' This sums the 1st forex rate and finds the mean
        aSum = aSum + audData(i)
    Next i

    aMean = aSum / a

    For i = 1 To eu ' This sums the 2nd forex rate and finds the mean
        eSum = eSum + euData(i)
    Next i

    eMean = eSum / e

    forex = (aMean * eMean)

End Function

user3598756 · Accepted Answer · 2017-03-01 23:59:36Z

2

You could use the Average() function:

Function forex(audData As Range, euData As Range)
    With WorksheetFunction
        forex = .Average(audData) * .Average(euData)
    End With  
End Function

answered Mar 1, 2017 at 23:59

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Coleman Over a year ago

This is both shorter and faster than what OP was trying to do, though they still need to be made aware of the dangers of using undeclared variables, so I'll keep my more verbose answer, although this is clearly superior.

user3598756 Over a year ago

@ArenGallagher, did you get through it?

user3598756 Over a year ago

@ArenGallagher, any chance to get feedback from you?

John Coleman · Accepted Answer · 2017-03-02 00:07:33Z

2

You seem to be a victim of undeclared variables. You have two loops,

For i = 1 To aud

and (later on)

For i = 1 To eu

where neither aud nor eu are declared. Thus, they default to variants with an implicit value of 0, hence neither of these loops ever execute and all your variables stay at 0.

You really should get in the habit of using Option Explicit at the top of all of your modules. This can be done automatically by enabling the option Require Variable Declarations in the VBA editor options. In the long run, it will save you hours of debugging time.

I can't test your code, but if you declare your variables and replace aud and eu by what I think you meant you would get:

Function forex(audData As Range, euData As Range) As Double
   Dim a As Long, e As Long, aSum As Double, eSum As Double, aMean As Double, eMean As Double, i As Long
    a = Application.Count(audData)
    e = Application.Count(euData)
    'Counts how many values are in the data

    For i = 1 To a ' This sums the 1st forex rate and finds the mean
        aSum = aSum + audData(i)
    Next i

    aMean = aSum / a

    For i = 1 To e ' This sums the 2nd forex rate and finds the mean
        eSum = eSum + euData(i)
    Next i

    eMean = eSum / e

    forex = (aMean * eMean)

End Function

I skipped the lines like aSum = 0 since properly declared VBA variables have reasonable default values.

edited Mar 2, 2017 at 0:07

answered Mar 2, 2017 at 0:01

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

1 Comment

YowE3K Over a year ago

I think the OP has even more problems by using a loop than you have noticed - i.e. if some cells within audData or euData are empty then the Count will be less than the number of cells, and the loop will only iterate over the first a or e cells (even though that will potentially include empty cells) and will ignore some of the final cells in the range. (It probably should be audData.Count instead of Application.Count(audData), with an appropriate If Not IsEmpty(..) Then in the loop.) But +1 for pointing out the merits of Option Explicit!

Collectives™ on Stack Overflow

Excel VBA continually returning 0

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related