Function dosn't update value

[Cass]

Hi!

My created function dosn't update the value if source data be changed
Excel > tools > Options > Calculation is Automatic calculation.

[Bob Phillips]

Hi!

My created function dosn't update the value if source data be changed
Excel > tools > Options > Calculation is Automatic calculation.

What is the function?

[Cass]

Search a little help and found the mistake.
[VBA]Function FindMonth(Find_what, Range_ As Excel.Range, Optional Column)
Application.Volatile False ' Mistek Here! was True
Dim rCell As Excel.Range
Dim Frida As Double
Dim Kokku As Double
Dim vahe As Double
For Each rCell In Range_
If Not IsEmpty(Find_what) Then
If Month(rCell.Value) = Month(Find_what) Then
Frida = rCell.Row
vahe = Sheets("Calender").Cells(Frida, Column)
Kokku = Kokku + vahe
Debug.Print Kokku
End If
End If
Next
FindMonth = Kokku
Application.Volatile True ' Mistek Here! Was False
End Function
[/VBA]
but is any fastest way resolve this?!
I cant use VLOOKUP because sourche table are many same rows then i loop all cells and SUM the value

[Bob Phillips]

Search a little help and found the mistake.
[VBA]Function FindMonth(Find_what, Range_ As Excel.Range, Optional Column)
Application.Volatile False ' Mistek Here! was True
Dim rCell As Excel.Range
Dim Frida As Double
Dim Kokku As Double
Dim vahe As Double
For Each rCell In Range_
If Not IsEmpty(Find_what) Then
If Month(rCell.Value) = Month(Find_what) Then
Frida = rCell.Row
vahe = Sheets("Calender").Cells(Frida, Column)
Kokku = Kokku + vahe
Debug.Print Kokku
End If
End If
Next
FindMonth = Kokku
Application.Volatile True ' Mistek Here! Was False
End Function
[/VBA]
but is any fastest way resolve this?!
I cant use VLOOKUP because sourche table are many same rows then i loop all cells and SUM the value

Why not use Find and FindNext.

Also, why do you make non-volatile, what advantage do you see there? No need to reset at end, it only applies to that function.

[Cass]

I'm not so good in VBA and i found example code and modify for my needs.
Is find .... find next faster than For each?!

[Bob Phillips]

I'm not so good in VBA and i found example code and modify for my needs.
Is find .... find next faster than For each?!

It depends upon the size of the range, th bigger it is, the quicker Find/FindNext gets.

[Rembo]

Hello Cas,

My created function dosn't update the value if source data be changed
Excel > tools > Options > Calculation is Automatic calculation.

No pun intended but the code is a bit sloppy because you don't explicitly specify all the data types although it's pretty obvious what they should be. If you don't explicitly specify a datatype VBA automatically assigns the Variant type to it. Variant's take a lot of overhead to evaluate; don't use them unless you have to. Using Variants can sometimes give unexpected results. In your code you use a variable 'Find_what' to pass on an expression of which you extract a month number in your routine. If that expression is a string, then change Find_what As Variant to Find_what As String.

Also, in your function header you have an 'Optional column'. (an Integer or Long type) There's nothing optional about it because you use call that column in your routine. If you don't assing a value to the variable column it it will return a value of '0' and column 0 does not exist. That will give you an error.

A similar issue is with the line ' Kokku = Kokku + vahe '. You didn't assign a value to it in your routine. If kokku is a global variable, this line is correct but you declared it as a local variable. VBA is kind enough to give it the default value of '0'. Hence Kokku = Kokku + vahe is the same as Kokku = vahe. Not sure what you tried to do there but you might want to lookin to that.

In your code you wrote ' vahe = Sheets("Calender").Cells(Frida, Column) '. If you meant to use the value in that cell it's a good habit to write that down: ' vahe = Sheets("Calender").Cells(Frida, Column).Value '
In this case it's pretty obvious but as your routines grow bigger it becomes more difficult to see the big line in them. Making your code readable is a good idea and one way to do that is to write down what you are actually using. If I scan a sub routine it's easy to spot a 'Value' and it gives me an instant idea of what that line of code is all about. What you wrote isn't wrong, just trying to give some pointers on how you could improve it.

I rewritten your code a little, perhaps you want to try and see if it works. If you receive an error kindly me know wich one and I'll have a look at it again.

Ok,the rewritten function:
[VBA]
Function FindMonth(Find_what As Variant, Range_ As Excel.Range, Column As Long) As Double
Dim rCell As Excel.Range
Dim Frida As Long
Dim Kokku As Double
Dim vahe As Double
For Each rCell In Range_
If Not IsEmpty(Find_what) Then
If Month(rCell.Value) = Month(Find_what) Then
Frida = rCell.Row
vahe = Sheets("Calender").Cells(Frida, Column).Value
Kokku = Kokku + vahe
End If
End If
Next
FindMonth = Kokku
End Function[/VBA]


Hope that helps,

Rembo

[Bob Phillips]

In your code you use a variable 'Find_what' to pass on an expression of which you extract a month number in your routine. If that expression is a string, then change Find_what As Variant to Find_what As String.

Sometimes it is a good idea to use variants and allow various data types. But if typing and testing against a month number, Long seems more appropriate to me.

A similar issue is with the line ' Kokku = Kokku + vahe '. You didn't assign a value to it in your routine. If kokku is a global variable, this line is correct but you declared it as a local variable. VBA is kind enough to give it the default value of '0'. Hence Kokku = Kokku + vahe is the same as Kokku = vahe.

Absolutely not. That is a standard practice in a loop, where upon entering the loop Kokku is zero, but it increments as the loop progresses. If you use Kokku = vahe, you will not increment it, but just update it on each iteration .

[Rembo]

Sometimes it is a good idea to use variants and allow various data types. But if typing and testing against a month number, Long seems more appropriate to me.

The result of Month will be an Integer. The argument can be a Variant or an other expression that makes sense as a date. (not sure what the correct English words for these expressions are).

Absolutely not. That is a standard practice in a loop, where upon entering the loop Kokku is zero, but it increments as the loop progresses. If you use Kokku = vahe, you will not increment it, but just update it on each iteration .

Oops.. have to agree with you there xld, good thing you correcte me. Totally overlooked the loop there. The statement does make sense.

Rembo