Blog Archives

Macro Mondays – How To Add A Break Point To Your Excel VBA Code

November 27, 2022 by Barbara —Leave a Comment

Hello, Excellers. Welcome to another #MacroMonday blog post in my Excel series. Today let’s look at breaking up our VBA code by adding a breakpoint to your Excel VBA code. If you’re an Excel user, you know that VBA code can be incredibly useful for automating tasks and customizing your spreadsheets. But what happens when your code isn’t working the way you want it to? One of the best ways to troubleshoot and fix your code is to add breakpoints so that you can see where in the code execution things are going wrong. In this blog post, we’ll show you how to add a pause or breakpoint to your Excel VBA code. Stay tuned!

First Things First – What Is A Breakpoint Or Pause In VBA?.

A breakpoint is a selected line of code that once it has been reached, your macro will be suspended. At this point you use the debugging environment to step through each line of code, halt the codes execution or view the status of your program. It is a great way to step though and debug your code without having to restart your code from the very beginning each time you want to debug.

There is no limit to how many breakpoints you have in your code. You cannot however add breakpoints to the following lines of VBA

nothing in them (blank lines).
Only code comments, usually wrtitten in green and begin with an apostrophe.
Dimensioning statements. (lines starting with DIM).

How To Add Break Points To Your VBA Code.

Let’s get down to adding pauses or breakpoints into our VBA code. First, open the VBA environment by either hitting the Excel keyboard shortcut ALT+F11 or hit the Developer Tab and then select Visual Basic.

To set a breakpoint in your VBA Code, find the line you want to add to break to. Then, follow the instructions below.

Left-click in the grey bar to the left of the code.
A red dot should appear, subsequently the line of code will be highlighted in red.

MACRO MONDAYS ADDING pause or BREAKPOINTS TO YOUR VBA CODE

Your breakpoint will be inserted.

How To Remove Break Points From Your VBA Code.

Once you have finished debugging your VBA code it is really easy to remove the breakpoints you have inserted.

Left click on the breakpoint you want to remove
Your breakpoint will automatically be removed

MACRO MONDAYS CREATE AND REMOVE BREAKPOINTS IN EXCEL VBA CODE

How To Clear Multiple Break Points From Your Excel VBA Code.

If you have a lot of breakpoints in your VBA code as there is no restriction on how many you can use, then it maybe a challenge to individually go through your code and remove them one by one. If you want to remove all of the breakpoints at once then you can use the debugging menu options.

MACRO MONDAYS ADDING BREAKPOINTS TO YOUR VBA CODE1

Select the Debug Menu
Select Clear All Breakpoints
Or use the keyboard shortcut CTRL+SHIFT+F9

So there you have it! How to add a break point or pause in your Excel VBA code. This should help you troubleshoot and fix any errors in your code. If you want more tips like this, sign up for my newsletter below and I’ll send them straight to your inbox. Thanks for reading!

If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not bookmark it?, Yes, it is updated EVERY Monday.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

Delete The First Two Digits In My Cells.

November 25, 2022 by Barbara —1 Comment

Hello Excellers and welcome back to another blog post in my series of #FormulaFiday #ExcelTips. Today, let’s answer an Excel question from one of my subscribers. They want to be able to delete the first two digits of a string in a column of data. No other instances instances of the digits in other parts of the cells are to be deleted. With about 5000 rows of data, only the first two digits of the string which are ’98’ need to be removed. No other instances of these digits should be deleted from the data set. Here is a hint how to solve this. We will use both the RIGHT function and the LEN function.

So, we cannot use the Find and Replace process as this would replace all of the instances of 98 in the cells. A data sample is below. We will be using the RIGHT function and the LEN function in this solution.

Delete the first two digits in my Excel cells. use Right Function. Use LEN Function.

Using Right and LEN Functions.

So, as already stated, the solution to this problem is to use both the RIGHT and the LEN Functions in Excel. The first part of the solution is to use the RIGHT function. The purpose of this function is to extract text from the right of a string. This sounds like this would be useful for this solution. The syntax of the RIGHT function as a quick recap is:

=RIGHT (text, [num_chars])

Arguments are as follows-

text – The text from which to extract characters on the right.
num_chars – [optional] The number of characters to extract, starting on the right. Optional, default = 1.

So, let’s now move onto the second function we are using in this solution. THe LEN function, which returns the length of a given text string as the number of characters. LEN counts characters in numbers, but number formatting is not included. The syntax of the LEN function as another quick recap is:

=LEN (text)

Arguments are as follows –

text – The text for which to calculate length.

Writing The Excel Formula To Delete The First Two Digits In Excel.

Finally, it is time to bring all of this together and delete the first two digits of our cells. As already discussed, we cannot use Find and Replace as it would replace all instances of 98 in the cells. So, let’s build the formula and walk it through.

The RIGHT function takes the string in A2, this is the text to extract the characters from the right. The number of characters that need to be extracted (the second argument in the RIGHT function), is calculated by the LEN function. This is calculated as 7 minus 2 which leaves us with the five digits from the right.

delete first two digits using RIGHT and LEN function

So, how cool is that?. This leaves any other instances of the 98 combination in place. A simple but clever way to delete the first two digits in my Excel cells.

Copy A File From One Folder To Another – Macro

November 20, 2022 by Barbara —Leave a Comment

Have you ever needed to copy a file from one folder to another? If so, you’re in luck. In this blog post, we’ll show you how to do it using a macro. Stay tuned for our step-by-step instructions! I have to copy files from one folder to another every week. I find this a simple and effective way to carry out this task. So, today I want to show you how to write a simple Excel VBA Macro to copy a file from one location to another, or from a source folder to a destination folder. I run this code from Excel it does the hard work for me.

Right, let’s get started on writing the simple VBA code.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice,. Either create a module to store your code either in your Personal Macro Workbook or store the code in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. Seeing as I want to reuse this over and over again, and only want excel to be open to rin the macro (not the workbooks I have to copy), then it makes sense to stores the code in my Personal Macro Workbook.

If you want to read more about your PMW then feel free to read my read my other Excel articles below.

Creating and Updating Your Personal Macro Workbook

Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Step 1. Starting The Macro.

First, insert a new module to store the code. In the VBA Editor hit Insert Module in your PMW as in the screen shot below.

vba to copy files from one folder to another

Once the module is inserted, then it is time to name the macro. Type Sub the the name of the macro. In this example I name the macro Copy_My_File. Notice once you name the macro Excel inserts the End Sub line of code into the editor. All other lines of code are stores between these two lines.

Here is a screen grab of the the full VBA macro. you can copy the full set of code at the end of this article to use. Always back up your work before running any code.

Step 2. Declare Variables.

The first piece of code is a DIM statement. Two variables are declared. The declaration of variables simply creates a memory container for these values in Excel. I am declaring the Source folder and the Destination folder as below.

Dim SoureFile As String
Dim DestFile As String

Step 3. Set The Source File.

The next step in the macro is to set the source file. This is the full file path and extension to file you want to copy. Update this line of code to reuse in your scenario with the full file path of the file you want to copy from one folder to another.

SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx

Step 4. Set The Destination File.

Next, set the destination for the copied file. This again is the full file path of the folder you want to copy the file to. Also update this line of code for your particular scenario.

DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx

Step 5. VBA Copy Files From One Folder To Another.

Step 5 is the line of code that copies the file from one folder to another. The variables of the source and destination file are supplied already. The FileCopy VBA statement has two arguments. The Source and Destination. This is the statement that name copies the file from one location to another location.

Note If you try to use the FileCopy statement on a file that is currently open, an error occurs. This is a really useful statement as I do not need to open the source or destination file open to carry out this process.

vba copy file from one folder to another.

Step 6. Ending The Macro.

The final step in the code is tp end the macro with the End Sub line of code once the file has been copied. The final line of code is already in the editor. Now, it is time to test the macro. My favourite part. Let’s go!.

So, this articles covered how to use vba copy files from one folder to another. I do hope you found this useful. The code is below to copy and use for your own Excel projects. Simply replace the Source and Desintation variables withyour own filepaths. Always backup your work before running any VBA code.

‘Macro by how to excel at excel.com
Sub Copy_My_File
Dim SoureFile As String
Dim DestFile As String
SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx
DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx
FileCopy SourceFile, DestFile
End Sub

Formula Friday – Find And Hide All Formulas On An Excel Worksheet

November 18, 2022 by Barbara —Leave a Comment

Happy Formula Friday, Excellers. Formulas are a huge part of Excel, and they can be very helpful in organizing and analyzing data. However, sometimes you may need to hide them from view so that your worksheet is easier to read. In this blog post, we will show you how to find and hide all formulas on an Excel worksheet. Stay tuned!

This tutorial is a response to a question asked by a colleague recently. How to hide all Excel formulas in an Excel workbook?. The workbook due to be shared contained a lot of formulas that the end-users did not need to view. So, let’s hide any Excel formulas found in the workbook.

Why Hide Excel Formulas?.

I often hide Excel formulas. There are a few reasons behind this.

I do not want other users to see the formulas.
Protection of the Excel formulas. I do not want users to change them in any way, maliciously or by accident.

How To Hide Excel Formulas.

So, here is a quick and efficient way to identify the cells with formulas and hide them. There are a couple of (easy) steps to it.

Step 1.

Select all of your data on your worksheet.
Hit F5
Click Special
Select Formulas

learn how to find and hide Excel formulas

Now all cells with formulas are selected. I have formulas in several areas in the example I am using, which are all highlighted.

Step 2.

Now to hide the formulas. All Excel cells have two attributes. Locked and hidden. Locked cells are just that, locked and cannot be changed by an end-user, whereas the hidden attribute means that formulas contained in those cells will not be displayed in the formula bar at the top of the worksheet.

We do however need to also remember that none of these attributes are in force until a worksheet is protected. So, onto to the detail of Step 2. We already have our cells with formulas selected –

Home Tab – Cells Group
Format – Protection – Protect Sheet
Ensure that Protect Worksheet and Contents are selected
Enter a Password when promtped
Job Done!

Now, if we click or select one of the cells that contained a formula, see if you can see your Excel formulas now?…..nope…thought not……!

In this blog post, we have discussed how to find and hide Excel formulas. The steps are quite simple if you follow the article’s instructions. We hope that this information was useful for you!

So, how do YOU prevent users seeing or accessing your Excel formulas? Leave a comment below.

More Excel Tips.

Formula Friday – Why is my formula showing in a cell and not the result??

November 11, 2022 by Barbara —Leave a Comment

Hello Excellers. Welcome to another blog post article in my #Excel series. When you create a formula in Excel, you may expect to see the result of that formula immediately show up in the cell where you typed it in. But sometimes, instead of seeing the result, you see the actual formula displayed in the cell. So what’s going on, and how can you fix it? In this blog post, we’ll look at why formulas appear in cells instead of the results and how to fix them. Stay tuned! You can also watch the video to accompany this article. Why not subscribe to my YouTube channel and get immediate access to all of my playlists.

So, you may have already come across this issue in Excel. If you have not, you probably will at some point. Yes, the mysterious case of Excel shows the formula not the result. Your Excel worksheet cell will look something like this below.

Let’s work through the possible solutions to this problem and the reasons Excel is behaving like this.

Reason 1. Show Formulas Option Is Enabled.

The first reason Excel shows formulas, not results, is that you have Show Formula selected. Yes, that is correct. Have you accidentally set the Show Formula Option? It may be the reason Excel is showing formulas rather than the result of the formulas. You may have accidentally hit the Excel shortcut CTRL+’. To return this to normal view try hotting CTRL+’ again. Show formulas are available on the Formula Tab | Formula Auditing | Show Formulas.

Reason 2. Something In Front Of The Equals Sign.

The second reason Excel maybe shows formulas instead of results is that there is a space or some other symbol before the equals sign. If this is the case, then Excel will treat the formula as text and not evaluate it as a formula. So, Excel displays the actual cell contents.

Reason 3. Cells Are Formatted As Text.

So, if the formula looks good and you have checked for reasons one and two, there is another reason. Check that your cells’ formatting is not set as text. If your cell formatting is set to text then Excel will treat the formula as a text string. So, instead of evaluating the formula and generating the result. A text string of the formula is displayed. This however is a really easy fix, select the cell and reset the formatting. First, set the format to General, try changing the cell mode to edit (click in the formula bar or use F2, then enter. Excel will recognise the formatting change, resolving the text formatting issue

We hope this article was helpful in explaining why your Excel formula is not displaying the result you expect. If you have any further questions, please don’t hesitate to leave a comment and ask questions.

Need An Excel Problem Solved?. Try Excel Rescue.

Dont forget to sign up to the Excel at Excel Newsletter for THREE FREE Excel tips every month. Just click on the Sign Up Form to the right or use the link below.