New to VBA, why won't my function work all the time?

[chenyu768]

Hi folks,
So im just learning VBA and i thought i could use it to replace one of my more complicated formulas.

So basically what this does is calculates the mark to market of various contracts depending on type, pipe, and date.

It ran fine for a while and all of a sudden i'm getting gaps, funny thing is when i renamed the Function, it worked, then stopped again.

HELP WHAT AM I SCREWING UP?

Dim k_type As String
Dim pipe As String
Dim Fixed As Currency
Dim Adder As Currency
Dim adder_per As Integer
Dim cap As Currency
Dim floor As Currency
Dim part_per As Integer
Dim Volume As Integer
Dim date_val As Date
Dim capped_k As Integer
Dim capped_v As Integer
Dim Target_k As Integer
 
 

Function Mark(k_type, pipe, Fixed, Adder, adder_per, cap, floor, part_per, Volume, date_val)
Application.Volatile
index_val = WorksheetFunction.Index(Range("monthly_index"), WorksheetFunction.Match(pipe, Range("indexes_names"), 0), WorksheetFunction.Match(date_val, Range("Index_date"), 0))
Target_k = (((adder_per * 0.001) * index_val) + Adder) * Volume
fixed_k = (Fixed - index_val) * Volume
capped_v = (cap - (((adder_per * 0.001) * index_val) + Adder) - index_val)
If capped_v > 0 Then
capped_k = 0
Else
capped_k = capped_v * Volume
End If

floor_v = (floor - (((adder_per * 0.001) * index_val) + Adder) - index_val)
If floor_v < 0 Then
Floor_k = 0
Else
Floor_k = floor_v * Volume
End If
part_k = part_per * Target_k + (1 - part_per) * fixed_k
 
 
 
 
    
If k_type = "TARGET" Then
Mark = Target_k
Else
    If k_type = "FIXED" Then
    Mark = fixed_k
    Else
        If k_type = "COLLARED" Then
        Mark = Floor_k + capped_k + Target_k
        
        Else
            If k_type = "PARTNER" Then
            Mark = part_k
            Else
                If k_type = "CAPPED" Then
                Mark = capped_k
                Else
                    If k_type = "CAPPED GTD SAVE" Then
                    Mark = Target_k
                    Else
                        If k_type = "FIXED PRICE VOL REQ" Then
                        Mark = fixed_k
                        Else
                            If k_type = "GUARANTEED" Then
                            Mark = Target_k
                            Else
                                If k_type = "MHA ES" Then
                                Mark = fixed_k + (capped_k * 0.5) + (floored_k * 0.5)
                                Else
                                    If k_type = "MHA COLL" Then
                                    Mark = Floor_k + capped_k + Target_k
                    
            
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Function

 

[repairman615]

Hello,

See if this helps to make the debugging process easier:

<font face=Courier New><br> <br> <br><br><SPAN style="color:#00007F">Function</SPAN> Mark(k_type <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, pipe <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Fixed <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>, Adder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>, _<br>        adder_per <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, cap <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>, floor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>, part_per <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Volume <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, date_val <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>)<br><br><SPAN style="color:#00007F">Dim</SPAN> capped_k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> capped_v <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Target_k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>Application.Volatile<br><br>index_val = WorksheetFunction.Index(Range("monthly_index"), WorksheetFunction.Match(pipe, Range("indexes_names"), 0), _<br>        WorksheetFunction.Match(date_val, Range("Index_date"), 0))<br>Target_k = (((adder_per * 0.001) * index_val) + Adder) * Volume<br>fixed_k = (Fixed - index_val) * Volume<br>capped_v = (cap - (((adder_per * 0.001) * index_val) + Adder) - index_val)<br>floor_v = (floor - (((adder_per * 0.001) * index_val) + Adder) - index_val)<br>part_k = part_per * Target_k + (1 - part_per) * fixed_k<br>    <br>    <SPAN style="color:#00007F">If</SPAN> capped_v > 0 <SPAN style="color:#00007F">Then</SPAN><br>        capped_k = 0<br>    <SPAN style="color:#00007F">Else</SPAN><br>        capped_k = capped_v * Volume<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> floor_v < 0 <SPAN style="color:#00007F">Then</SPAN><br>        Floor_k = 0<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Floor_k = floor_v * Volume<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>     <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> k_type<br>        <SPAN style="color:#00007F">Case</SPAN> "TARGET"<br>            Mark = Target_k<br>        <SPAN style="color:#00007F">Case</SPAN> "FIXED"<br>            Mark = fixed_k<br>        <SPAN style="color:#00007F">Case</SPAN> "COLLARED"<br>            Mark = Floor_k + capped_k + Target_k<br>        <SPAN style="color:#00007F">Case</SPAN> "PARTNER"<br>            Mark = part_k<br>        <SPAN style="color:#00007F">Case</SPAN> "CAPPED"<br>            Mark = capped_k<br>        <SPAN style="color:#00007F">Case</SPAN> "CAPPED GTD SAVE"<br>            Mark = Target_k<br>        <SPAN style="color:#00007F">Case</SPAN> "FIXED PRICE VOL REQ"<br>            Mark = fixed_k<br>        <SPAN style="color:#00007F">Case</SPAN> "GUARANTEED"<br>            Mark = Target_k<br>        <SPAN style="color:#00007F">Case</SPAN> "MHA ES"<br>            Mark = fixed_k + (capped_k * 0.5) + (floored_k * 0.5)<br>        <SPAN style="color:#00007F">Case</SPAN> "MHA COLL"<br>            Mark = Floor_k + capped_k + Target_k<br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#007F00">''' Use this for entries not listed above</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[chenyu768]

Thanks mr. repairman, but now none of my calculations are showing up. Is there an libary that i'm supposed to refrence to? oh and i probably should have mentioned that i'm on excel 2010.

On a side note, is "case" a better use than "if else"?

 

[repairman615]

Hello chenyu,

Notice how all of the variables are now defined within the function for their data types. Your original post shows the Dim statements outside of the function and I do not think this defines the variables and instead defaults each to variant.

Most likely the variables are incorrectly dimmed. Possible pipe should be range instead of string:



Function Mark(k_type As String, pipe As Range,.......




I see no need for an additional library needed.


Using IF(s) gets confusing when testing one variable for multiple results. Since there is only a need to return a result based on the variable k_type, I feel a select case is well suited and much easier to understand.

Try changeing "pipe as string" to "pipe as range" shown above in red.

If that doesn't help I have another Idea.
I don't have a way to test, so bear with me and I think this can be solved.

 

[chenyu768]

I tried changing the dim for the pipe and nothing. in fact nothing shows up now.
Man i really thought i had the hang of it too, it's baffling that it only works in certain cells. here's a link to my report and the tab in question is called [Volume_k] if there is a number in column BG to CH than there should be a corressponding value in CP to DS. so if line 2414 has a value in October or Column BG then there should be a value in October MTM section under column CP.

The issue is that if i just do 100 accounts they show up fine, but when i apply it to the 2500 customers i start seeing blanks. Any help would be greatly appreciated.

http://www.box.net/shared/z29l6hd61511y6e5p1av

 

[chenyu768]

ok so i took out the dim part and it works, but i know next time i open it it won't work again. i labeled that one with the mtm fixed extension. Is there a limit to the number of cells i can use a function?

 

[repairman615]

Function Mark(k_type As String, pipe As String, Fixed As Double, Adder As Double, _
        adder_per As Integer, cap As Currency, floor As Currency, part_per As Integer, Volume As Integer, date_val As Variant)
Dim capped_k As Integer
Dim capped_v As Integer
Dim Target_k As Integer
Application.Volatile
index_val = WorksheetFunction.Index(Range("monthly_index"), WorksheetFunction.Match(pipe, Range("indexes_names"), 0), _
        WorksheetFunction.Match(date_val, Range("Index_date"), 0))
        
Target_k = (((adder_per * 0.001) * index_val) + Adder) * Volume
fixed_k = (Fixed - index_val) * Volume
capped_v = (cap - (((adder_per * 0.001) * index_val) + Adder) - index_val)
floor_v = (floor - (((adder_per * 0.001) * index_val) + Adder) - index_val)
part_k = part_per * Target_k + (1 - part_per) * fixed_k
    
    If capped_v > 0 Then
        capped_k = 0
    Else
        capped_k = capped_v * Volume
    End If
    If floor_v < 0 Then
        Floor_k = 0
    Else
        Floor_k = floor_v * Volume
    End If
     
    Select Case k_type
        Case "TARGET"
            Mark = Target_k
        Case "FIXED"
            Mark = fixed_k
        Case "COLLARED"
            Mark = Floor_k + capped_k + Target_k
        Case "PARTNER"
            Mark = part_k
        Case "CAPPED"
            Mark = capped_k
        Case "CAPPED GTD SAVE"
            Mark = Target_k
        Case "FIXED PRICE VOL REQ"
            Mark = fixed_k
        Case "GUARANTEED"
            Mark = Target_k
        Case "MHA ES"
            Mark = fixed_k + (capped_k * 0.5) + (floored_k * 0.5)
        Case "MHA COLL"
            Mark = Floor_k + capped_k + Target_k
        Case Else
            ''' Use this for entries not listed above
    End Select
End Function

Hello,

I noticed calculations was set to manual. The above was as far as I got.