INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get routine not working

RE: Get routine not working

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.

CODE

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 

RE: Get routine not working

(OP)
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

RE: Get routine not working

(OP)
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

RE: Get routine not working

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

Do you have something like:

CODE

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.

RE: Get routine not working

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.a...
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.

RE: Get routine not working

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close