Frequent Troubleshooting
If you are just starting out with VBA, you will probably often find yourself in a situation that you don’t know how to get out of or perhaps you don’t understand how you got yourself in this position in the first place. Luckily, Excel is such a versatile program that everything can be easily fixed, so you really don’t have to worry about things going horribly wrong. However, because we want to help you have all the information that you need in one place, we will now go through the most frequently asked questions and troubleshooting that most beginners have problems with.
“I get a message asking me if I want to enable or disable a macro”
This will happen if there was an error during the creation of a macro, or perhaps if there was an error when you were closing the worksheet the last time you were working on it. What happens in this case is that there remains an empty module from the corrupted macro, in which case Excel is no longer sure if you want to run the macro or not.
To fix this problem, right click somewhere on your sheet and select View Code. This will open the VBA editor and show you the project explorer. Look for the workbook that you are currently working in, and then on the left side, look for the module that was associated with this worksheet but is now not working. Right click on the module that is no longer working and then click on Remove Module. You can repeat this task for any module where you need help removing a macro that has somehow corrupted. When you do this correctly, Excel will no longer ask you whether or not you wish to run macro.
“How do I run a macro automatically when a cell changes content?”
Instead of always having to check if a macro has run when a cell changes its value, you can create a macro that will do this automatically for you. As long as you assign the correct range for the macro to keep track of what is happening in a particular cell, the macro will run accordingly as soon as anything in the cell changes. This will provide you with a lot of help and saved time so that you will never forget to properly complete a new cell, because the macro code will do that for you.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
End If
End Sub
“How do I stop other people from seeing my macros?”
For privacy reasons, you won’t always want people to see your macros or how you have created them. This is because there is a possibility that people could mess with your macros and there by mess with the content of your worksheet. This is definitely not something that you want to experience, because you will not know where the changes were made or which part of your worksheet is no longer accurate. The way that you can avoid this situation happening is by locking your macros from anyone else being able to see them.
To do this, make sure that you are in the VBA editor, and then click on Tools. Click on Properties and then look for the tab that says Protection. Then, click on “Lock project for viewing”. You will then be prompted to enter a password, which will protect all of your macros from anyone who does not have the password. However, make sure that you remember the password. Because if you cannot remember it, then the macros will also be hidden from you, which is not what you want to happen.
“How do I quickly find an empty cell in a column?”
Instead of having to scroll for ages to find a new empty cell in which you can enter new content, you can quickly find the first empty cell by using a very simple code to help you out. This is the code that you should create with a macro to help you out with this process:
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If
“How do I skip to the last row in my worksheet?”
Similarly to the previous question, you don’t want to scroll for ages to find the first empty row that you can use to continue entering new information. You can also achieve this wit a short and simple code that will complete the task for you with a single click.
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
“How do I perform the same operation across all workbooks?”
This is a super useful code that will help you to do one and the same thing across all workbooks. This will save you a lot of time, because if you were to do this manually, you would have to go through each workbook and do the same tasks over and over again. And if there are many cells for you to go through, this will take hours of your time.
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir
Loop
End Sub
“What do I do when some of my files become read-only?”
This is a frequent thing that happens in Excel, which makes many people stressed when they try to fix it. Read-only files are those which you can no longer edit, which means that you can no longer enter content in them. This can be frustrating when you are in the middle of work and how have to deal with this. Luckily, there is a very simple way around this problem without having to worry too much about it.
Simply clear out the folder in this location: c:\windows\temp directory, and then restart your machine.
“Excel keeps crashing, telling me that there are not enough resources?”
This is something that can happen if you have a worksheet with truly a lot of content in it. Excel is certainly a great piece of software, but even software can become overwhelmed. Anything can cause this message to appear, so there is not one particular location that you will need to look for in order to solve this problem. Instead, follow these steps to fix the issue:
- Close all Excel windows
- Restart your machine
- Use the Search option on your machine to search for all files that end with the extension .xlb
- Rename each file with a new extension .old
- Now open Excel again
“How do I reset a range that has been used?”
When you have a spreadsheet that has a lot of information, to the amount of hundreds of cells, there may come a time when an entire range needs to be reset to its original number. Perhaps this would happen if you made a mistake somewhere along the way, or if there was an error in one of the macros that caused this disturbance. As you can imagine, this is not something that you would ever want to do manually, nor would you be able to. You cannot possible remember what was in each cell range that needs to be restarted. And even if you could, it would take you hours to complete this task. Instead, use the code below to fix the issue.
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub
'================================
Sub TestForMergedCells()
Dim AnyMerged As Variant
AnyMerged = ActiveSheet.UsedRange.MergeCells
If AnyMerged = False Then
MsgBox "no merged"
ElseIf AnyMerged = True Then
MsgBox "all merged"
ElseIf IsNull(AnyMerged) Then
MsgBox "mixture"
Else
MsgBox "never gets here--only 3 options"
End If
End Sub
'=====================================
“How do I pick random items from a selected list?”
Depending on how many things you need to pick randomly, this could potentially take forever to complete. Luckily, there is a code that will help you to achieve this in an instant.
- Enter the item selection range, e.g. C1:C80
- Then, in D1:D80 enter =RAND()
- Sort the list in column D
- Press F9 to generate the numbers
- Repeat this again if you want to get new numbers