Hi Jared,
(Edit: note that I hadn't read any of the replies today before I posted this, so some points have already been mentioned)
I had a look at your file, more specifically the code behind your userform in Private Sub CommandButton1_Click()
There are a couple things that I can see that will need correcting for the code to work properly.
1.
Your declaration at the top is a common mistake that is made when first learning VB. I've done it, most people have done it, and you don't realise until something goes wrong. You have:
which you would intuitively expect to declare all the variables as integers. However the comma separates each statement, so everything between the commas is in effect a separate statement. So only "F as Integer" classes as integer. When you do not specify a variable type it will default to Variant which lets VB choose the variable type based on the input. In general, unless you specifically need a type Variant then you should avoid this and explicity state each variables type. So A, B, C, D, E are all type Variant by default. You will need to change this section to:
or
Personally I find the first style is easier to upkeep and read. Programmatically they function the same.
2.
In your SELECT CASE statements you are setting your variables to numbers surrounded by quotation marks. Quotation marks tell VB that whatever is inside should be treated as a text string. For example,
should instead be:
This sets the variable A with a true number (not a number stored as text). The problem you were having with your (A + B) statement was the result of the combination of points 1 and 2. Because A and B were both set to type Variant, when you then set e.g. A = "10", VB saw that you told it that "10" is a text string, and assigned the type String to variable A. The same thing happened with B. Then you tried to 'add' two strings together which results in concatenation, one string after another, i.e. "10" + "5" = "105".
3.
In your long calculation formula you are taking a values from a TextBox, which will return strings. You must make sure you are inputting the correct data type into your formula, or errors such as above will occur. So for example instead of
which you want to be a number, you should use
which ensures that you are using a number and not a text string.
Likewise, as bakerman2 said in his post, it is better to change
which you want to be a date (but the user can input anything at all), you should use
which ensures that what the user entered can be converted to a date serial.
If it can't be converted then it will throw an error. As an upgrade you can insert some code at the beginning that checks this, and then lets the user retry if they have inserted an invalid date.
After these tweaks your full equation should look like:
although in this form it will be a nightmare to maintain, as you may have found hehe 
(Optional) Personally I would upgrade this to make it more human readable. I would set variables to each part with a readable name, build the parts, and then execute the equation. So I'd do something similar to this:
It all depends on how you interpret your own equation.
Note that Double is a number that can have decimal places, unlike Integer which cannot.
If you correct the non-optional points 1, 2, and 3 then your code should work
(Optional) As a tip, when writing SELECT CASE statements you can group cases together when you want multiple cases to have the same function. For example, you wrote:
which can be shortened (and corrected) to:
It is a lot easier to see the criteria for when a certain case is applied this way. Also notice that I indented each level of the statement (by selecting all the lines and pressing TAB) to make it easier to read, which is good practice and very helpful when your statements start getting larger and more complicated.
I would also rename all the input boxes in the UserForm to have meaningful names, as ComboBox3 does not tell you which box it relates to unless you go back to the UserForm and select it.
I hope this helps to increase your VB knowledge
LinkBack URL
About LinkBacks
Register To Reply
below the post.
Originally Posted by Norie
Bookmarks