• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

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
  • 3
1 Solution
Perhaps along the lines of:

Sub Example()
Dim dblSPI As Double, dtSMI As Date
Call StockHigh1(102.1)
dblSPI = Application.InputBox("Enter Price of Interest", "Price", Type:=1)
Call StockHigh1(dblSPI)
Call StockHigh2(DateSerial(2002, 5, 0))
'using Input
dtSMI = Application.Max(DateSerial(2000, 2, 0), Application.InputBox("Enter Month of Interest", "Price", Type:=1))
Call StockHigh2(dtSMI)
End Sub

Sub StockHigh1(dblSP As Double)
Dim dt As Date
dt = Evaluate("MIN(IF(B3:B100>" & dblSP & ",A3:A100))")
If dt Then
    MsgBox "The first date X stock price exceeded " & Format(dblSP, "0.00") & " was " & Format(dt, "mmm-yyyy"), vbInformation, "Found"
    MsgBox "Price Never Exceeded", vbInformation, "Not Found"
End If
End Sub

Sub StockHigh2(specifiedMth As Date)
Dim dblVal As Double, dt As Date
dblVal = Evaluate("MAX(IF(A3:A100<=" & CLng(specifiedMth) & ",B3:B100))")
dt = Evaluate("INDEX(A3:A100,MATCH(" & dblVal & ",B3:B100,0))")
MsgBox "The most recent record, up until " & Format(specifiedMth, "mmm-yyyy") & ", was in " & Format(dt, "mmm-yyyy") & ", when the price reached " & Format(dblVal, "0.00")
End Sub

You might want to adjust the ranges of course.
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")
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
MasterOfTheSkyAuthor Commented:
Thank you very much!! Yes I will adjust the ranges.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now