MasterOfTheSky
asked on
Excel VBA Question
Two part question
Part a. I am trying write a sub call StockHigh1 that will takes a single argument called searchPrice. It will search down the list of prices for the first price that exceeds the searchPrice argument. If it finds one, it displays the corresponding date in a message, something similar to, “The first date X stock price exceeded______ was____.” If the price never exceeded the argument searchPrice, it displays a message to this effect. Next, I am trying to write a Main sub that uses an input box to ask the user for a price and then calls StockHigh1 with this price as the argument.
Part b. I am also trying to write another sub call StockHigh2 that takes a single argument called specifiedMonth. This sub will search down the list of prices for the last time up until (and including) the specified month where the stock reached a record high (that is, it was larger than all prices before it, going back to the beginning of 2000). It will then display in a message, “The most recent record, up until ____, was in ____, when the price reached____.” (Note that Jan-2000 is a record high by default, so at least one record high will always be found.) Change the Main sub from part a so that the input box now ask for a month and then calls StockHigh2 with this month as an argument.
Please check the attachment for closing prices
Tester-b-v1.0.xls
Part a. I am trying write a sub call StockHigh1 that will takes a single argument called searchPrice. It will search down the list of prices for the first price that exceeds the searchPrice argument. If it finds one, it displays the corresponding date in a message, something similar to, “The first date X stock price exceeded______ was____.” If the price never exceeded the argument searchPrice, it displays a message to this effect. Next, I am trying to write a Main sub that uses an input box to ask the user for a price and then calls StockHigh1 with this price as the argument.
Part b. I am also trying to write another sub call StockHigh2 that takes a single argument called specifiedMonth. This sub will search down the list of prices for the last time up until (and including) the specified month where the stock reached a record high (that is, it was larger than all prices before it, going back to the beginning of 2000). It will then display in a message, “The most recent record, up until ____, was in ____, when the price reached____.” (Note that Jan-2000 is a record high by default, so at least one record high will always be found.) Change the Main sub from part a so that the input box now ask for a month and then calls StockHigh2 with this month as an argument.
Please check the attachment for closing prices
Tester-b-v1.0.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I should have added - the Example routine shows the subsequent routines being invoked with manually defined variables and subsequently via values as generated via Input Dialogs.
This will bring an input dialogue:
xyz = InputBox("Enter the value")
xyz = InputBox("Enter the value")
senthurpandian - better to use Application.InputBox (as already illustrated) given data type can be restricted to valid type:
see: http://msdn.microsoft.com/en-us/library/aa195768(office.11).aspx
see: http://msdn.microsoft.com/en-us/library/aa195768(office.11).aspx
ASKER
Thank you very much!! Yes I will adjust the ranges.