VBA Homework help

[rlcaudill]

My current code is as follows. I based it off an example given by the instructor. I'm so lost! It doesn't want to work. Any thoughts on this? =/


'WARNING: DO NOT DELETE OR CHANGE ANYTHING IN THIS FILE EXCEPT
'FOR YOUR OWN CODE. TO DO SO IS CERTAIN DEATH.
'Constants: Define your constants here
'Variables: Place your Dim statements here
Dim FloorPrice As Currency
Dim DeliveryFee As String
Dim TradeInCredit As String
Dim FinalCost As Currency
Dim TradeInAmount As Currency
Dim DeliveryAmount As Currency
Dim NoDiscount As String
Dim DiscountAmt As Currency
Option Explicit '<<< DO NOT CHANGE THIS LINE
Private Sub FinalCost_Click()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE

'Driver Module Name: FinalCost <--- Enter the driver module name from your logic solution.

'Place your code here
Call OpenFile
Call ProcessData
Call CloseFile



Call GETDATA
Call CALCDISCOUNT
Call CALCTRADEIN
Call CALCDELIVERY
Call CALCFINALCOST
Call SHOWRESULTS

Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***

ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub OpenFile()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE


'Place your code here

Open ActiveDocument.Path & "\Lab4BasicIn.txt" For Input As 4


Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***

ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub ProcessData()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE


'Place your code here
Do While Not EOF(4)
Call GETDATA
Call CALCDISCOUNT
Call CALCTRADEIN
Call CALCDELIVERY
Call CALCFINALCOST
Call SHOWRESULTS
Loop
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***

ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub CloseFile()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE


'Place your code here

Close #4


Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***

ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub GETDATA()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE
'Place your code here
Input #4, FloorPrice, DeliveryFee, TradeInCredit
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub CALCDISCOUNT()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE
'Place your code here
If FloorPrice > 100 Then
DiscountAmt = FloorPrice * 0.15
ElseIf FloorPrice > 90 Then
DiscountAmt = FloorPrice * 0.14
ElseIf FloorPrice > 80 Then
DiscountAmt = FloorPrice * 0.13
ElseIf FloorPrice > 70 Then
DiscountAmt = FloorPrice * 0.12
ElseIf FloorPrice > 60 Then
DiscountAmt = FloorPrice * 0.11
ElseIf FloorPrice > 50 Then
DiscountAmt = FloorPrice * 0.1
ElseIf FloorPrice <= 50 Then
DiscountAmt = 0
End If
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub CALCTRADEIN()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE

'Place your code here

If (TradeInCredit = "Y") Or (TradeInCredit = "y") Then
TradeInAmount = 5
Else
TradeInAmount = 0
End If
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub CALCDELIVERY()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE
'Place your code here

If (DeliveryFee = "Y") Or (DeliveryFee = "y") Then
DeliveryAmount = 20
Else
DeliveryAmount = 0
End If
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub
Private Sub CALCFINALCOST()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE
'Place your code here
FinalCost = FloorPrice - DiscountAmt + DeliveryAmount - TradeInAmount
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

Private Sub SHOWRESULTS()
On Error GoTo ErrHandler '<<< DO NOT CHANGE THIS LINE
'Place your code here
LabelOut.Caption = LabelOut.Caption & vbNewLine & vbNewLine & _
"The cost to you for the bed is " & Money(FinalCost) & _
". " & vbNewLine & "The original price was " & Money(FloorPrice)
If FloorPrice <= 50 Then
LabelOut.Caption = LabelOut.Caption & _
NoDiscount
End If
Exit Sub '*** DO NOT CHANGE ANYTHING FROM HERE TO End Sub ***
ErrHandler:
If Not g_bErrorMsgDisplayed Then
Call DisplayStdError("Lab2")
End If
Stop '*** PRESS THE F8 KEY TWICE TO SEE WHICH LINE OF CODE IS CAUSING YOUR ERROR ***
Resume
End Sub

[rlcaudill]

Wow it lost all my indentions! lol

[lucas]

Hi, If you select your code when posting and hit the vba button it will be formatted for the forum. I edited your code in post #1 for you.

We have a policy on homework that says we can't just solve your problem for you. See our FAQ.

We can however answer specific questions to assist in your learning experience and give hints and direction when appropriate.

Can you describe what you are trying to do and what seems to be the major malfuntion with your code?

Also, what version of Word are you using?

[Paul_Hossler]

This looks like Excel

If it is Word, then the rest of the document would be helpful, along with SPECIFICALLY what the problem is so maybe we can nudge you in the right direction to solve the problem

Actually, even if it's Excel, a spredsheet would help

Paul

[rlcaudill]

My problem is it doesn't run. I'm not sure where I messed up as I followed along with the instructor video.



Output List
FinalCost
FloorPrice
DiscountPrice
DeliveryAmt
TradeInAmt

Input List
DeliveryFee
TradeInCredit
FloorPrice

Pseudocode

Bed module
FILEOPEN
PROCESSDATA
FILECLOSE
End module

FILEOPEN module
Open file
End module

PROCESSDATA module
Do pre-test Until End Of File
GETDATA
CALCDISCOUNT
CALCTRADEIN
CALCDELIVERY
CALCFINALCOST
SHOWRESULTS
End pre-test
End Module

FILECLOSE module
Close file
End module

SHOWRESULTS module
Output FinalCost, FloorPrice, DiscountPrice, DeliveryAmt,
TradeInAmt
End module


GETDATA module
Input FloorPrice, DeliveryFee, TradeInCredit
End module

CALCDISCOUNT module
If FloorPrice > 100
DiscountAmt = FloorPrice * .15
ElseIf FloorPrice > 90
DiscountAmt = FloorPrice * .14
ElseIf FloorPrice > 80
DiscountAmt = FloorPrice * .13
ElseIf FloorPrice > 70
DiscountAmt = FloorPrice * .12
ElseIf FloorPrice > 60
DiscountAmt = FloorPrice * .11
ElseIf FloorPrice > 50
DiscountAmt = FloorPrice * .1
ElseIf FloorPrice <= 50
DiscountAmt = 0
EndIf
End module

CALCTRADEIN module
If TradeInCredit = “Y” OR “y”
TradeInAmount = 5
Else
TradeInAmount = 0
EndIf
End module

CALCDELIVERY module
If DeliveryFee = “Y” OR “y”
DeliveryAmount = 20
Else
DeliveryAmount = 0
EndIf
End module

CALCFINALCOST module
FinalCost = FloorPrice - DiscountAmt + DeliveryAmount – TradeInAmount
End module

[Paul_Hossler]

First the good news --

You just need to save a document with macros as a .DOCM file

FileSaveAs and in 'Save As Type" select 'Word Macro Enable Document'


Next the bad news ---

Assignment.docx has none of your code in it. Saving as plain document removes the VBA ... gone


But the good news ---

Open Assignment.docs, go to the VBA editor, insert a module in it, and then paste the VBA from your forum post, and SAVE IT AS a DOCM file

Last bad new ---

A number of variables, e.g. g_bErrorMsgDisplayed are not Dim-ed, so you'll need to do that also


Paul

[lucas]

It doesn't run is not a question that we can help you with.

You will have to be more specific. For instance, which module do you run first(I know what I think, I want to know what you think).

Next we will want to know what "it doesn't run" means. What happens? Do you get an error? If so, what error and on what line in which sub.

Paul, I can't tell from the code whether it runs from Word or Excel. It looks like it has a userform that does all the work so it could be either. It would be nice if our poster would clear that up for us too.

You have to answer our questions before we can help you.

[lucas]

Paul, I don't think the file he posted is the project file. I think it's just the assignment.

Poster, please clarify.

[Paul_Hossler]

Ahh - I was thinking about the "My problem is it doesn't run" and the macro-less DOCX file

Paul

[fumei]

Paul, why do you think it is Excel? There are no Excel objects used.

rlcaudill:

1. generally, put Option Explicit at the start, and declare your variables after.

2. It does indeed look like a userform, as there is a click event (and apparently values coming from some control...BUT....

Look at the logic flow. Assuming FinalCost_Click() is the initial firing procedure, the following is the order other procedures are called:

Call OpenFile
Call ProcessData
Call CloseFile
Call GETDATA
Call CALCDISCOUNT
....and others.

Look at the first one, Call OpenFile. It just opens the file:
Open ActiveDocument.Path & "\Lab4BasicIn.txt" For Input As 4

Exit Sub
and terminates. Soooooo, the next procedure (Call ProcessData) executes, and its first instruction calls yet another procedure:
Do While Not EOF(4)
Call GETDATA
OK, so now GETDATA executes:

Input #4, FloorPrice, DeliveryFee, TradeInCredit
Oh-oh. FloorPrice, DeliveryFee, TradeInCredit have NO values, as....you have not given them any value.

[Paul_Hossler]

Paul, why do you think it is Excel? There are no Excel objects used.

Just asking because it was mostly computation and I didn't see any Word objects used either -- I didn't see that ActiveDocument


Paul

[fumei]

Except for:
Open ActiveDocument.Path & "\Lab4BasicIn.txt"
Although...............

Open like that is a syntax error!

[Paul_Hossler]

Open ActiveDocument.Path & "\Lab4BasicIn.txt"


Yea, I didn't look closly enough and missed that 'ActiveDocument' part.

Score: Fumei -- 1
Paul -- 0

Paul

[fumei]

Hey, I am NOT keeping any scores!

Except for Olympic gold medal hockey games....ahem...cough...cough.

JUST kidding.

Back to business. rdcaudill, it does not work because it does not do anything. That really is the bottom line.