PDA

View Full Version : Solved: Excel VBA Userform - Accounting Calculator



1ATATIME
11-18-2008, 02:33 PM
Hello!

I'm new to this forum and thought this would be a great way to learn more about VBA. I'm quite experienced with Excel, but VBA is a weakness of mine.
Right now, I'm trying to make a make-shift accounting calculator. We send out bills and receive payment on those bills. Because of the kind of business, we usually don't ever receive the exact amount billed. We allow a 2% margin of difference (or $1000, whichever is greater) on the billed amount, otherwise reconciliation work needs to be done. I would like this calculator to automatically be able to tell me if reconciliation needs to be done and how much needs to be reconciled to get within the 2% threshold. I've already made a calculator on a regular Excel sheet, but would like to pu this on a userform so I could easily pull it up from anywhere (while working in Excel).
I'm using a userform and have already got it started. I was able to make the form and use a custom button to open the form and a Close button on the form to close it. It's the actually functionality of the form and coding that I need help with.

On the form are two input fields for a user to enter a billed amount and a paid amount. What I would like to do is have the form automatically provide some calcualtions that appear as 'text' on the same form.

I would like the following fields:

Remaining Balance: =sum([billed amount] - [paid amount])
Over/Short: =if([billed amount]<[paid amount],"Over","Short")
Variance %: =([Remaining Balance] / [billed amount])
Reconciliation Required? (Yes/No): =IF([Remaining Balance]-[Allowable Difference]>0,"Yes!","No!")
Allowable Difference: =IF(AND([billed amount]*.02>-1000,[billed amount]*.02<1000),1000,[billed amount]*.02)
Difference To Reconcile: =[Allowable Difference]-[Remaining Balance]

I know those forumlas wouldn't actually be the REAL formulas, but I replaced the cell references with the field names so you could see what I was trying to do.
I hope this all makes sense and isn't too big of a headache.

Thank you VERY much for the help!

lucas
11-18-2008, 03:39 PM
I think something in your post triggered the moderation by the board.......

MaximS
11-18-2008, 04:14 PM
check my attachement for details and tell me if results are correct.

ps. i've tested it for a bit and seems to work fine.

1ATATIME
11-19-2008, 01:08 PM
I'll give that a look and will let you know! Thank you SO much for the help!

1ATATIME
11-20-2008, 12:19 PM
MaximS,

That's pretty much it! Thank you so much. I wasn't able to pull up the VBA code, however. Can you paste that perhaps? I need to tweak the allowable difference formula just a bit (after testing it).

Thanks again!

lucas
11-20-2008, 12:53 PM
Why weren't you able to pull up the code. Select the userform and right click on it and choose view code. In the visual basic editor.....

If you can't see the code how will you implement it if its give to you here on the board?


Private Sub CommandButton1_Click()
Dim x, y, x1, x3, x5, x6 As Long
Dim x2, x4 As String
x = Int(TextBox1.Value)
y = Int(TextBox2.Value)
x1 = Format(x - y, "Currency")
If x < y Then
x2 = "Over"
Else
x2 = "Short"
End If
x3 = x1 / x
If x * 0.02 > -1000 And x * 0.02 < 1000 Then
x5 = 1000
Else
x5 = x * 0.02
End If
If x1 - x5 > 0 Then
x4 = "Yes"
Else
x4 = "No"
End If
x6 = x5 - x1
TextBox3.Value = Format(x1, "Currency")
TextBox4.Value = x2
TextBox5.Value = Format(x3, "Percent")
TextBox6.Value = x4
TextBox7.Value = Format(x5, "Currency")
TextBox8.Value = Format(x6, "Currency")
End Sub

1ATATIME
11-20-2008, 01:09 PM
Ok, I got it.

I tried updating the code with what I already have set up on my form:


Private Sub frmReconciliationCalculator_Click()
Dim x, y, x1, x3, x5, x6 As Long
Dim x2, x4 As String
x = Int(TotalRebateBilled)
y = Int(TotalRebatePaid)
x1 = Format(x - y, "Currency")
If x < y Then
x2 = "Over"
Else
x2 = "Short"
End If
x3 = x1 / x
If x * 0.02 > -1000 And x * 0.02 < 1000 Then
x5 = 1000
Else
x5 = x * 0.02
End If
If x1 - x5 > 0 Then
x4 = "Yes"
Else
x4 = "No"
End If
x6 = x5 - x1
TextBox3.Value = Format(x1, "Currency")
TextBox4.Value = x2
TextBox5.Value = Format(x3, "Percent")
TextBox6.Value = x4
TextBox7.Value = Format(x5, "Currency")
TextBox8.Value = Format(x6, "Currency")
End Sub


When I tried to run this, it gave me an overflow error.

lucas
11-20-2008, 01:25 PM
What is this:

TotalRebateBilled
& this:
TotalRebatePaid

seems undefined......

1ATATIME
11-20-2008, 01:31 PM
I'm sorry; this is where my ignorance is really going to show so you'll have to bear with me.

On the form I made, I have the two input fields and that's what I named both of the fields. In the properties of the text boxes, that's what I defined as the name. Hope that makes sense....:dunno

lucas
11-20-2008, 01:37 PM
You'll have to give it a value there and probably call it by a range or cell reference if it's in a sheet....

can you post your workbook?

hit post reply at the bottom left of the last post.....write your reply and then scroll down and look for the button that says "manage attachments"

a little more detail of where the data will be coming from will help also.

1ATATIME
11-20-2008, 01:42 PM
Well that's where I'm kind of complicating matters I think. I'm just putting this in my personal macro workbook and putting the code straight into VBA and then using a macro button on the toolbar so I don't actually ever put anything in the actual cells of the workbook. So as far as I know/understand, there aren't any cells or ranges to reference. Does that make sense? I can still attach the workbook if you'd like, just let me know.

lucas
11-20-2008, 01:48 PM
No, I think I understand. Can I make a suggestion?

If you want this to be available to you any time you have Excel open then you should create an addin out of maxim's file.

If Maxims file does what you want and you just want to use it no matter what excel file you have open then this sounds like the best solution.......does that describe your situation?

1ATATIME
11-20-2008, 01:49 PM
Ha, ha, that's what I thought I was doing!

lucas
11-20-2008, 02:09 PM
Glad to help you out, especially since we started out on such a rocky path......

Save this attachment and when you get it on your hard drive open it up.

Go to file and select save as

In the dialog that opens look at the very bottom window.....the one that says "Save as type"

drop that box and select "microsoft office Excel addin" it should be at the bottom of the list so you will have to scroll down to the bottom to select it.

Once you select it you will be offered automatically the default folder for your excel addins......just save it there.

now go to tools and select addins.

put a check next to the one that says calculator and say ok.

close the file and open a new blank excel file and look at the main menu, next to help for a menu item named "calculator" click that and it should run your form......

It should now be available in any Excel file that you open.......

Try it and let me know.

1ATATIME
11-20-2008, 02:32 PM
That is fantastic!!!

Just a few final things. Is there a way to format the input text as currency? Also, I would like to increase the font size on the input and result text as well. Finally, is there a way to make the "Difference To Reconcile" amount an absolute value?

lucas
11-20-2008, 02:43 PM
Well, if you're going to learn anything then you must do some reading.

Tackle one problem at a time. Use the excel file that you downloaded or a copy of it and go to the visual basic editor where the code is and use help to look up formatting......this one should be pretty easy for you to figure out but if you get stumped you can post here with specific questions.

tip on the text box fonts.....in the vbe go to view and select properties window. Then select a textbox and you will be presented with many properties for the textbox, including fonts........

You will have to clarify your last question.

1ATATIME
11-20-2008, 02:47 PM
I actually tried that before posting that last response. I saw where the results were formatted as currency, but didn't see where to format the input text. Also, on the properties, I saw where you could change the font type, but didn't see anything about size. Am I just blind?
Another way to put my last question - Is there a way to make the Diff To Reconcile amount always a positive number?

I'll take another look at the code in the meantime and see if I can figure it out....

1ATATIME
11-20-2008, 02:54 PM
Ha, ha, ha, well THAT didn't work. No matter what number I entered, it would change to "currency." :think:

1ATATIME
11-20-2008, 03:06 PM
Uh-oh. I just found a calcualtion error. If I enter 5000 billed and 4950 paid, it says the allowable difference is $1,000 which is correct, but it says that the amount to be reconciled is $950, when it should be zero.

MaximS
11-21-2008, 11:23 PM
as it for calculation error use this and put that in marked places:


If x1 - x5 > 0 Then
x4 = "Yes"
Else
x4 = "No"
End If

' After that put below code

If x5 - x1 <= 1000 Then
x6 = 0
Else
x6 = x5 - x1
End If

' put above before that code
TextBox3.Value = Format(x1, "Currency")

MaximS
11-21-2008, 11:58 PM
and thats should do formating for the first 2 fields:


Private Sub UserForm_Initialize()
TextBox1.Value = Format(0, "Currency")
TextBox2.Value = Format(0, "Currency")
End Sub

1ATATIME
11-24-2008, 02:56 PM
and thats should do formating for the first 2 fields:


Private Sub UserForm_Initialize()
TextBox1.Value = Format(0, "Currency")
TextBox2.Value = Format(0, "Currency")
End Sub



Great, thank you so much for the help!