How can I tell if VBA is running in an .XLSM?

[Plasmech]

I have a .xlsm with embedded VBA code. I believe the VBA code has stopped running.

How can I tell, definitively, if VBA is running?

Also, I would like to add something to the VBA code to show an error on the spreadsheet if VBA fails to run. What would the easiest way to go about this be? I'm thinking of a line of code that hides a "VBA FAULT" cell.

Thanks for any help, as always.

Please let me know if I'm not using the proper terminology.

 

[SanjayGMusafir]

How can I tell, definitively, if VBA is running?

When VBA is done "Ready" appears at the lower Left end of the status bar

 

[SanjayGMusafir]

Also, I would like to add something to the VBA code to show an error on the spreadsheet if VBA fails to run. What would the easiest way to go about this be? I'm thinking of a line of code that hides a "VBA FAULT" cell.

Generally if the VBA fails to run it shows up error...

 

[Joe4]

Note that one simple step you can do is to add message boxes at various points in your code.
For example, you can put this at the very top:

MsgBox "VBA code has started to run"

and maybe something like this at the end of the code:

MsgBox "VBA code complete"

So, if the code is running, you will get those Message boxes to pop up.

Are you, perchance, working with Event procedures?
If you are, perhaps you have accidentally disabled them, which means they will not run/fire anymore in your session of Excel. This often happens when you have VBA code that temporarily turns off events to make some updates, and turns it on again at the end, but you get some sort of error or other abend where the code at the end to turn it back on never runs.
You can turn them on again by manually running this procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Plasmech]

When VBA is done "Ready" appears at the lower Left end of the status bar
View attachment 100569

Hi Sanjay,

I am seeing this "Ready":

 

[Plasmech]

Note that one simple step you can do is to add message boxes at various points in your code.
For example, you can put this at the very top:

MsgBox "VBA code has started to run"

and maybe something like this at the end of the code:

MsgBox "VBA code complete"

So, if the code is running, you will get those Message boxes to pop up.

Are you, perchance, working with Event procedures?
If you are, perhaps you have accidentally disabled them, which means they will not run/fire anymore in your session of Excel. This often happens when you have VBA code that temporarily turns off events to make some updates, and turns it on again at the end, but you get some sort of error or other abend where the code at the end to turn it back on never runs.
You can turn them on again by manually running this procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Hi Joe,

Does VBA code run as one-and-done, or does it constantly loop? When would the "end" occur?

Thanks.

Also, AFIK, I am not using Event Procedures.

 

[Joe4]

Hi Joe,

Does VBA code run as one-and-done, or does it constantly loop? When would the "end" occur?

Thanks.

Also, AFIK, I am not using Event Procedures.

I cannot evaluate your code if you do not post it.

 

[Plasmech]

I cannot evaluate your code if you do not post it.

Sorry, here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4")) Is Nothing And Not IsEmpty(Range("C4").Value) Then
If Range("C4").Value <> Sheets("lookup data").Range("E6").Value Then
Application.EnableEvents = False
'
With Range("C9")
If Range("C4").Value = "Metric" Then
.Value = .Value * 0.45359237
Else
.Value = .Value * 2.20462262
End If
End With
'
With Range("C12")
If Range("C4").Value = "Metric" Then
.Value = .Value * 16.0185
Else
.Value = .Value / 16.0185
End If
End With
'
Sheets("lookup data").Range("E6").Value = Range("C4").Value
Application.EnableEvents = True
End If
End If

 

[Joe4]

"Worksheet_Change" is 100% definitely an Event Procedure!!!
An "Event Procedure" VBA code is one that runs automatically upon some event happening, like the updating of a cell.

So you could have accidentally disabled events.
Try copying that little "ReEnableEvents" procedure I wrote for you and then run it manually, and see if that fixes your issue.

And you can temporarily add the following line to your code, just to ensure that the code is running:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Code is being called"
If Not Intersect(Target, Range("C4")) Is Nothing And Not IsEmpty(Range("C4").Value) Then
If Range("C4").Value <> Sheets("lookup data").Range("E6").Value Then
Application.EnableEvents = False
'
With Range("C9")
If Range("C4").Value = "Metric" Then
.Value = .Value * 0.45359237
Else
.Value = .Value * 2.20462262
End If
End With
'
With Range("C12")
If Range("C4").Value = "Metric" Then
.Value = .Value * 16.0185
Else
.Value = .Value / 16.0185
End If
End With
'
Sheets("lookup data").Range("E6").Value = Range("C4").Value
Application.EnableEvents = True
End If
End If

Now, whenever you make a change anywhere on that sheet, you should get a message box pop-up saying "Code is being called".
If not, then you probably have this code in the wrong place.

 

[Plasmech]

"Worksheet_Change" is 100% definitely an Event Procedure!!!
An "Event Procedure" VBA code is one that runs automatically upon some event happening, like the updating of a cell.

So you could have accidentally disabled events.
Try copying that little "ReEnableEvents" procedure I wrote for you and then run it manually, and see if that fixes your issue.

And you can temporarily add the following line to your code, just to ensure that the code is running:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Code is being called"
If Not Intersect(Target, Range("C4")) Is Nothing And Not IsEmpty(Range("C4").Value) Then
If Range("C4").Value <> Sheets("lookup data").Range("E6").Value Then
Application.EnableEvents = False
'
With Range("C9")
If Range("C4").Value = "Metric" Then
.Value = .Value * 0.45359237
Else
.Value = .Value * 2.20462262
End If
End With
'
With Range("C12")
If Range("C4").Value = "Metric" Then
.Value = .Value * 16.0185
Else
.Value = .Value / 16.0185
End If
End With
'
Sheets("lookup data").Range("E6").Value = Range("C4").Value
Application.EnableEvents = True
End If
End If

Now, whenever you make a change anywhere on that sheet, you should get a message box pop-up saying "Code is being called".
If not, then you probably have this code in the wrong place.

I got VBA running. Not sure why it wasn't before. There was nothing wrong with the code. I even rebooted yesterday. Strange. I tried your

MsgBox "Code is being called"

It works. Thanks.

I would like to have VBA actively suppress a "VBA Fault" message (or cell contents). Is this possible?

Thanks again for all the help you have provided.