This action will reset your project, proceed anyway?

[JenniferMurphy]

I am getting this message after I made a couple of edits to a macro and then tried to rerun it. Behind the message, all of the code in that module is selected. If I click Ok, it is all deleted. If I click Cancel, the code is still selected. If click anywhere, the selection goes away and I see that the macro declaration is highlighted in yellow indicating break mode. If I click Run and Reset, break mode is cleared, but if I rerun the macro, it all happens again.

Any clues to what I did this time to cause VBA to torture me this way?

 

[Tom.Jones]

You have to click reset button then you can run again any VBA code.
If you run again same VBA cod and If you not corrected the error, that message will appear again.

 

[JenniferMurphy]

You have to click reset button then you can run again any VBA code.
If you run again same VBA cod and If you not corrected the error, that message will appear again.

Maybe I wasn't clear. I did do a reset 2-3 times. I also backed out the last 2-3 edits. The error keeps coming back.

 

[JenniferMurphy]

I exported a copy of the code to a .bas file. I saved the code and the sheet. This is an .xlsm file and the code is in a workbook module. Then I closed all workbooks. When I reopened that workbook and tried to run the code, I got the same error.

Can anyone tell me what is going on?

 

[RoryA]

Did you restart Excel or just close all the workbooks?

Also, what does the code do?

 

[JenniferMurphy]

Did you restart Excel or just close all the workbooks?

I don't understand. What's the difference?

Anyway, I first closed all workbooks and Excel. I then restarted Excel, opened that workbook, and reran the code.

When that failed, I closed everything and rebooted Windows. When I restarted Excel, same error.

If I try to close VBA without closing Excel, I get a message saying "This command will stip the debugger".

Next I am going to delete the module and then restore it from a saved .bas file.

Also, what does the code do?

The main routine calls a recursive routine that generates all possible combinations of wins and losses in a 7-game series:

WWWW
WWWLW
WWWLLW
WWWLLLW
WWLWW
. . .

 

[RoryA]

I ask about the code because I seem to recall you asking a question about code to clear the immediate window and you had some code that involved Sendkeys. Is that code being called by any chance?

 

[JenniferMurphy]

I ask about the code because I seem to recall you asking a question about code to clear the immediate window and you had some code that involved Sendkeys. Is that code being called by any chance?

Yes, it is.

' Start
Application.SendKeys "^g ^a {DEL}"  'Clear the immediate window
Debug.Print " ----- New Run -----"

After I cleared the error message, I noticed that one of the statements was highlighted in yellow indicating that it was in break mode. I tried to step through, but got an error. I deleted the button control and added a new one and set a breakpoint in the code. When that worked, I found that one of the function calls had an extra parameter. When I fixed that, it worked.

Should I delete that Sendkey code? Is there a better way to clear the Immediate window?

 

[JenniferMurphy]

Here's the code that is now working.

'======================================================================================
'               Generate all possible Win/Loss combinations recursively

'     Change Log
' 03/19/23 Created & tested
'======================================================================================
Sub WinLossCombs()

'Const rnUpRtHd As String = "Corner" 'Name of upper right corner of table
'Const NumGames As Long = 7          'Number of games in the series
Const MaxWins As Long = 4           'Number of wins to win the series
Const WinChar As Variant = "W"      'The "win" character
Const LssChar As Variant = "L"      'The "loss" character

Dim Series As String    'The actual series (W's & L's)
Dim iSeries As Long     'The series number (1 to whatever)
Dim NumWins As Long     'Number of wins so far
Dim NumLsss As Long     'Number of losses so far

' Start
'Application.SendKeys "^g ^a {DEL}"  'Clear the immediate window
Debug.Print " ----- New Run -----"

iSeries = 1       'Start with the first series
Call WinLoss(iSeries, NumWins, NumLsss, Series, MaxWins)

End Sub


'---------------------------------------------------------------------
'               Recursive Code to Add Wins & Losses
'---------------------------------------------------------------------
Sub WinLoss(ByRef iSeries _
          , ByVal NumWins, ByVal NumLsss, ByVal Series, ByVal MaxWins)

' Save parameters that need to be backed out for the loss code
Dim SeriesIn As String: SeriesIn = Series   'The series W/L so far
Dim NumWinsIn As Long:  NumWinsIn = NumWins 'The number of wins

' Add a win
NumWins = NumWins + 1
Series = Series & "W"
If NumWins = MaxWins Then
  Debug.Print iSeries & " " & Series
  iSeries = iSeries + 1
Else
  Call WinLoss(iSeries, NumWins, NumLsss, Series, MaxWins)
End If
NumWins = NumWinsIn 'Restore the number of wins
Series = SeriesIn   'Restore the series

' Add a loss
NumLsss = NumLsss + 1
Series = Series & "L"
If NumLsss = MaxWins Then
  Debug.Print iSeries & " " & Series
  iSeries = iSeries + 1
Else
  Call WinLoss(iSeries, NumWins, NumLsss, Series, MaxWins)
End If

End Sub

I would appreciate any comments on the code or logic. This is just a test to see that the recursion is working correctly. The real code will do more calculations.

 

[RoryA]

The behaviour you described was exactly what would happen if the focus didn't go to the immediate window following the ^g part of the sendkeys code. I would never use sendkeys unless there is literally no option.