Get routine not working

[billheath]

This has worked forever. Yesterday, it quit. It is probably simple, but I'm not seeing it. The routine is calculating but net returning the answer.

 

[MajP]

Need to learn how to debug your own code. One of the easiest things is to put in debug.print statements so you can follow what is or is not happening.

I cleaned up your code to simplify it and tested it. One thing I recommend is if using a NZ funtion you supply the second argument so vba does not have to guess.

Thesecond argument in the NZ function is the value you want to return if null. If you do not specify you are going to get a 0 or "", and vba has to guess. For the Booleans you probably want to set to False, for numerics 0, for variants Null, and for strings “” an empty string. VBA may resolve this all correctly, but I never leave it to chance.
Also it can be any variant you want "some string", 1999, True, 1.00, -1, Date(), so the name should really be Null to Something not null to zero which is misleading.

Public Function GetPerGain(booClosed As Boolean, dblLossGain As Double, _
      dblBoughtCst As Double, dblCostBasis As Double, dblDiv As Double) As Double
    'Verify you are passing the in the correct values
    'Add some debug.print here to see if all paramaters are being passed in correctly
     Debug.Print "Boo " & booClosed & " LossGain " & dblLossGain & " BoughtCst " & dblBoughtCst & " cost basis " & dblCostBasis & " Div " & dblDiv
If booClosed = -1 Then
     Debug.Print "BooClosed = True"
     GetPerGain = [dblLossGain] / [dblBoughtCst]
Else
   Debug.Print "BooClosed = false"
  If dblCostBasis = 0 Then
      GetPerGain = 0
  ElseIf dblCostBasis >= dblDiv Then
       GetPerGain = dblLossGain / (dblCostBasis - dblDiv)
  Else
     GetPerGain = dblLossGain / (dblCostBasis + dblDiv)
  End If
End If
End Function

Public Sub TestGetPerGain()
  Debug.Print "  GetPerGain " & GetPerGain(True, 100, 50, 25, 0)
  Debug.Print "  GetPerGain " & GetPerGain(False, 100, 50, 25, 20)
  Debug.Print "  GetPerGain " & GetPerGain(False, 100, 50, 25, 30)
End Sub

 

[billheath]

Thank you for your help. As you can see, I am an amateur. I will try your suggestions -just not sure why this has been working for years and all of a sudden quit!
Thanks again Bill

 

[billheath]

I tried everything you suggested. All the parameters were sent correctly. I set the values you suggested in the NZ functions. In the sub routine GetPercentGain is being calculated properly. However, it is not being transferred back to the calling routine. For instance, the one I just ran read 2.00 before the action. The sub routine correctly calculated the value to be -.16. When I stepped back through the calling routine, the value was still 2.00.
I am completely confused!
Thanks for your help

 

[Andrzejek]

billheath, how do you call/use this function in your code?

Do you have something like:

Dim dblMyNumber As Double
...
dblMyNumber = GetPerGain(True, 100, 50, 25, 0)
MsgBox "My number is " & dblMyNumber
...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

If I understand correctly the function GetPerGain is working fine but the calling code is incorrect. Code does not stop working. Your calling code is very messy and you have likely hit a condition in the data that you have not encountered before. We need to clean that code up.
Please read this article and clean up that calling code declaring all of your variables then repost. This will give me a chance to try to figure out what you are trying to do

http://www.cpearson.com/excel/declaringvariables.aspx

Without declaring your variables I have no idea what is going on.

Next explain in words what you are trying to do with each and every one of the if checks. I am lost with what you are trying to do with the record navigation. My guess is that it is a logic flow so what you think is supposed to happen is not based on your if checks.

 

[billheath]

thanks. I figured it out finally. I made a change in the program a while back which was preventing the function from returning the number. Originally, the text box [GetPerGain] was unbound. I had bound it to a field in the underlying table which was (I think) overriding the calculated value.

It is working again now.

Thanks again, Bill