Home > other >  VBA - Loop until answer = VbNo
VBA - Loop until answer = VbNo

Time:12-14

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
  • Related