Boolean Statement Returning Incorrectly

  • Hi all,


    I am relatively new to VBA but I have typically been able to spot my errors based on google and forum searches. This time, however, I cannot figure out what I am doing incorrectly and this piece of code is important for me to continue.


    The following is the code:


    [VBA]
    If Cells(((numStats + 2) * X) + 5 + Y, 4).Value = "Annual GLWB Payout" Then
    'MsgBox Cells(2, 150) & " + " & issueAge(X) & " = " & Cells(2, 150) + issueAge(X) & " > " & annuitizationAge(X)
    For z = 5 To 304
    Cells(((numStats + 2) * X) + 5 + Y, z).NumberFormat = "$0,000"
    'Cells(((numStats + 2) * X) + 5 + Y, z).Value = Cells(2, z).Value + issueAge(X)
    'Cells(((numStats + 2) * X) + 5 + Y + 1, z).Value = annuitizationAge(X)
    'Cells(((numStats + 2) * X) + 5 + Y + 2, z).Value = (Cells(2, z).Value + issueAge(X) < annuitizationAge(X))
    If (Cells(2, z).Value + issueAge(X)) < annuitizationAge(X) Then
    Cells(((numStats + 2) * X) + 5 + Y, z).Value = 0
    Else
    Cells(((numStats + 2) * X) + 5 + Y, z).Value = Cells(((numStats + 2) * X) + 5 + Y - 1, z).Value * lwp(X)
    End If
    Next z
    End If


    [/VBA]


    Sorry if my formatting is incorrect, this is my first time posting. The commented cells were done as a test.
    issueAge(X) = (60, 51) and annuitizationAge(X) = (71, 57).


    The z variable ranges over months and so Cells(2,z) tracks the years so, for example, after 12 years, Cells(2,149) + issueAge(0) should return > annuitizationAge(0) but it does not.
    If I replace either issueAge or annAge with their values it returns correctly, but I need it to work for all X's.


    Added extra detail in case it's necessary. Do you guys know why this is not working? Thanks!!

  • The z variable ranges over months and so Cells(2,z) tracks the years so, for example, after 12 years, Cells(2,149) + issueAge(0) should return > annuitizationAge(0) but it does not.
    If I replace either issueAge or annAge with their values it returns correctly, but I need it to work for all X's.


    Do you guys know why this is not working? Thanks!!


    No where in your code snippet above, especially within the FOR loop have you changed the value of variable X. So X will only ever be the value that it is set to somewhere else in this procedure or maybe some other module. We also dont know if the function IssueAge or AnnAge are returning the correct value. We also dont know what X is supposed to represent. But for now, the value of X isnt being set or adjusted in your code snippet above, so it deifnitely wont work for "All X" as you claim you require.


    Maybe post back the worksheet, with all the code, and an explanation of what the code needs to do in simple english ;)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • No where in your code snippet above, especially within the FOR loop have you changed the value of variable X. So X will only ever be the value that it is set to somewhere else in this procedure or maybe some other module. We also dont know if the function IssueAge or AnnAge are returning the correct value. We also dont know what X is supposed to represent. But for now, the value of X isnt being set or adjusted in your code snippet above, so it deifnitely wont work for "All X" as you claim you require.


    Maybe post back the worksheet, with all the code, and an explanation of what the code needs to do in simple english ;)


    Hi, yes sorry for the vagueness there. This snippet is from a larger loop that varies both x and y as well. I do not believe I am able to post the entire sheet or workbook due to company policies. However, X does vary and its length depends upon an input by the user. Basically, the user puts in up to 5 options of issue age, wait period, annuitization age, and death age. I've done message boxes which have confirmed that the X's are varying. The Y's are as well (those track the different statistics being compared based on the inputs. Here "Annual GLWB Payout" is one of the Y's.


    I will see if I can simplify the code and reiterate the problem.

  • :) Desanitise your worksheet completely and your code.


    If X is a period of time entered by the user (days or months or whatever), and if you argue that replace the issue age and annage function calls with actual values will fix the problem, then the problem in my head seems to be that you are not prompting the user often enough for new values of X, or the functions themselves are incorrect.


    Its hard to debug when we dont have the full picture. Its like showing a corner of a painting to someone and asking them if they like the look of the entire thing :)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Okay, this file should be sufficient and causes the same problem.


    Note that it worked properly when I directly made issueAge(0), issueAge(1), annuitizationAge(0), and annuitizationAge(1) be their specific values; but then does not work when I link them to the userform included.


    But I have not had trouble with the userform otherwise.

  • Looking now... sorry for the delay, I was out of town. Thanks for the sample... be right back

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Its hard to say if this is the correct answer, because dont know what its supposed to be, but my guess is that you are getting a stream of zero's for "Annual GLWB Payout" where no zeros were expected :)


    There are a few things wrong that I would suggest changing.



    The arrays you defined are as follows (I dont know if this is the case in your real code, so forgive me if its unnecessary):
    Dim issueAge(4), waitPeriod(4), annuitizationAge(4), deathAge(4) As String


    This means that IssueAge, WaitPeriod, AnnuitzationAge are all arrays of type VARIANT. The final Array - DeathAge is an array of type STRING. My guess is you intended all arrays to be of type string. This is a common mistake made by new VBA'ers and it differs from say Java... where you could list a number of comma separated variables and a variable type.... not the case in VBA, you have to be explicit about each indiviual variable... for example
    Dim x as string, y as string is OK, but Dim X, y as string means X is a variant and Y is a String...


    (I Dont meant to be patronising when I say "new VBAers", but you'd be surprised the number of non-VBA "experienced" developers who dont realise or understand this behaviour).


    Next, leading on from this, and more importantly, why would define a string array to store Integer or single values? I believe you should define them as:
    Dim issueAge(4) as single, waitPeriod(4) as single, annuitizationAge(4) as single, deathAge(4) As single (or Integers if you wont be storing any decimal values)


    However, doing this now means you have to typecast the values from the userform to ensure they are converted to actual values... e.g.
    issueAge(2) = Val(exampleForm.tbIssueAge3.Value) this means that if it is a blank in tblIssueAge3 it wont "fit" into an array of single or integers, so you have to use the VAL(tblIssueAge3) which will return 0 for blanks, and this can be assigned to the array of type integer



    Confused yet?
    The simplest solution, leaving all the above aside, if you dont want to change any of your existing variable declarations - and go straight to something that works, then simply change the offending if statement and force the correct type casting. Because in here you currently have an array of strings, and you are trying to do comparisons against numerical data and VBA will not work as expected... so this line


    If (Cells(2, z).Value + issueAge(x)) < annuitizationAge(x)


    is evaluating to:
    1+"60" < "71"


    The " mark is indicate the value is a string.


    So convert your string variables to numbers with the VAL function:

    Code
    1. If (Cells(2, z).Value + Val(issueAge(x))) < Val(annuitizationAge(x)) Then


    And it seems to work just fine.


    Long story short - always use VAL when converting between string or variant types into integers/singles.



    Regards
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Thank you very much for this! I will try it here shortly.


    It is not patronizing at all; I've done some coding in other languages but this is my first crack into VBA.


    I will change how I have the variables defined, I didn't realize the comma separation was changing the type.


    I'm sure adding the VAL function will work, but it's still slightly confusing to me because the second line of my original post did the summation "Cells(2, 150) + issueAge(X) " properly, so I assumed it was not a problem of variable type.


    I will give this a try, much appreciated.


    Edit: I have made corrections to the code and it does appear to work. Thanks again and yes I was getting a stream of zeros where I did not want them!