I am building a little code to track some stocks, and I was wondering if there is a way to turn the code below into a "do until answer = vbNo" loop
Sub Test()
Dim answer As Integer
Dim Frequency As Double
Set Rng = Range("A:A") 'this is for a later part of the code where the input box is supposed to spit out the ticker the user input
Ticker1 = InputBox("Please input your first stock ticker!", "Test", AAPL)
If Ticker1 = "" Then MsgBox ("Sorry, you need to input at least one ticker symbol! Goodbye!") 'how to end this here if the user didn't input 1 ticker
If Not Ticker1 = "" Then answer = MsgBox("Do you want to input another ticker?", vbQuestion vbYesNo)
If answer = vbYes Then Ticker2 = InputBox("PLease input another stock ticker!", "Test", AAPL) 'it runs fine until right here, and then the loop is messing me up.
If answer = vbNo Then Frequency = InputBox("After how many minutes do you want to check again?", "Test", 60)
Do Until answer = vbNo
Loop
End Sub
I am trying to make is so that it can be any number of tickers instead of ticker1, ticker2, etc and I am trying to loop it until the user says "no" to "Do you want to input another ticker?"
I am a total beginner at vba and I am truly stuck on this. I tried to loop it myself but unfortunately I am doing something wrong and excel just hangs itself whenever I run the loop.
CodePudding user response:
A Tricky Do...Loop
Sub CollectTickers()
Const PROC_TITLE As String = "Collect Tickers" ' adjust!
Const AAPL As String = "Default" ' adjust!
Dim Tickers As Object: Set Tickers = CreateObject("Scripting.Dictionary")
Tickers.CompareMode = vbTextCompare
Dim Answer As Long
Dim Ticker As Variant ' so it can be used in a 'For Each...Next' loop
Dim FirstAdded As Boolean
Do Until Answer = vbNo
If Not FirstAdded Then ' first
Ticker = InputBox("Please input your first stock ticker!", _
PROC_TITLE, AAPL)
If Len(Ticker) > 0 Then ' the 1st ticker is not an empty string
FirstAdded = True
Else ' the 1st ticker is an empty string
MsgBox "Sorry, you need to input at least one ticker symbol! " _
& "Goodbye!", vbExclamation, PROC_TITLE
Exit Sub
End If
Else ' not the first
Ticker = InputBox("Please input another stock ticker!", _
PROC_TITLE, AAPL)
End If
If Len(Ticker) > 0 Then ' ticker is not an empty string
If Not Tickers.Exists(Ticker) Then
Tickers(Ticker) = Empty
Else
MsgBox "You already added the '" & Ticker & "' ticker.", _
vbExclamation, PROC_TITLE
End If
Else ' ticker is an empty string but it's not the first
' do nothing!?
End If
Answer = MsgBox("Do you want to input another ticker?", _
vbQuestion vbYesNo, PROC_TITLE)
Loop
MsgBox "You added the following " _
& IIf(Tickers.Count = 1, "", Tickers.Count & " ") & "stock ticker" _
& IIf(Tickers.Count = 1, "", "s") & ":" & vbLf & vbLf _
& Join(Tickers.Keys, vbLf), vbInformation, PROC_TITLE
For Each Ticker In Tickers
' Do your thing with the tickers, e.g.
Debug.Print Ticker
Next Ticker
End Sub