How do you write a macro with an IF Function

[Scheuerman1987]

I need to write a macro that does the following. I'm not very good with macros or VBA and I think my text book words this step really confusing. This is an extra credit assignment but I would still like to complete it. I have attached my workbook below so if anyone can help me write the code for the macro I'd really appreciate it.



Write a macro named Insert_Transaction to copy the transaction data from the Transaction worksheet and insert it at the top of the list of transactions in the Auction Account worksheet. (Hint: You can use the macro recorder to record the steps to copy sample data from the Transaction sheet to the Auction Account sheet.) The macro should include the following:

An If statement that verifies that there is enough money in the auction account to cover the transaction. If there is not enough money, the macro should prevent the transaction from being entered into the auction account and notify the user with a message box, indicating how much money is in the account.

If the transaction is covered by the account, copy the values from the Transaction worksheet into the Auction Account worksheet. After the values have been copied, use a message box to notify the user of a successful transaction along with the current account balance. Replace the values in the Transaction worksheet with blanks.

Test the macro by entering the data shown below in the order shown. Sales are entered as positive values and purchases are entered as negative values.

Date Description Item Amount

4/2/2013 Purchase Bach Trumpet with Mouth Piece - $165
4/4/2013 Sale King 1250 Flugelhorn $210
4/7/2013 Purchase Schiller Lightweight Trumpet - $350

https://docs.google.com/file/d/0B6iC...it?usp=sharing

[BobBridges]

I had to look at the worksheet and your copy of the instructions to figure this out, but I see now what they want. So you think you know what you have to do, except the part about the If statement, right?

I don't know whether you meant it literally or you just speaking loosely, but you wrote "IF function" in the subject of this thread while the assignment says "If statement". The assignment said it right. Technically you could use an Excel IF function in VBA, but that's going around your elbow to get to your thumb.

You already know what an IF function looks like in Excel. In VBA, an If statement looks different but does much the same thing. Let's say that you've already got your program to look at the current balance (Ending Balance in row 4 of the Account Auction worksheet), and put that value in a variable named Balance; and that you've put the next transaction amount in a variable named XctAmt. Your program is supposed to create a new row in AccountAuction—but before it does, it needs to make sure that there's enough money in the account. That is, once you add a negative transaction amount to a positive balance, the new value should still be positive. In programming there is always more than one way to do that, but the way I choose to demonstrate here goes like this:

If Balance + XctAmt < 0 Then
MsgBox "Error!"
Exit Sub
End If

Note what happens: The program adds Balance and XctAmt, then compares the result to 0. If it's less than 0, then there isn't enough money in the account to cover the transaction, in which case (so say your instructions), you should generate an error message and stop the program.

My error message said simply "Error!", but of course that's not enough information; you'll have to decide just how to word the message to satisfy the requirements of te assignment. After displaying that message is the Exit Sub statement, which stops execution of the subroutine.

If there is enough money in the account, then the program skips over those instructions and just goes on with whatever you tell it to do next, the rest of the program in other words.

If you're "not very good" with VBA then no doubt you'll have other questions, but this should help with the If statement. If it's worse than that—if you've never written anything in VBA before, nor in any other language—then we should back up and proceed one statement at a time, so that you can see what you're building and how each piece works with the rest. But I'll leave it to you to ask the right questions, at first.

[CGM3]

Actually, if Balance is the available amount, and XctAmt is the (positive) "cost" that will be deducted, methinks the code should be:

   If Balance < XctAmt Then
      ' error message, etc
   Else
      ' deduct amount from balance
   End If

[BobBridges]

Normally, yes, CGM3. But I looked at the sample workbook; purchases are negative and sales are positive, so it's the other way around.

[CGM3]

Originally Posted by BobBridges

Normally, yes, CGM3. But I looked at the sample workbook; purchases are negative and sales are positive, so it's the other way around.

So use:

If Balance < ABS(XctAmt) Then

(Has the advantage of implying, for the unfamiliar user, that XctAmt might be negative)

[BobBridges]

Sure, if the transaction amount was always negative. But sometimes the transaction is positive, so that test would forbid, for example, selling a trumpet for $120 on the grounds that there's only $45 in the account.