How do I use a Private Sub in Excel?

Question

I'm want to use the Private Sub functions provided here.

Usually a put VBA code in the Visual Basic Editor, I then close the editor and I run the code using "Run this Macro" on the Developer tab. But in this case, the function doesn't appear.

Where do I need to put this code to run it?

Answer 1

To use the code you found on the website:

In the Visual Basic editor, on the left hand side under Project-VBA Project, go to where it says VBAProject (<Your workbook name here>) and under Microsoft Excel Objects double click one of the sheets, and a blank code file will open up.

On the dropdown on the left side, select Worksheet and then select SelectionChange on the right, fill in the code from the website you linked. This is an event driven subroutine, which means when you change the selection, this code will be run automatically.

For a private subroutine that's not event related, create a public Sub() which Calls the private one.

Answer 2

The private macros doesn't show in the macro options by default, as they are marked private (Private keyword prevents a macro from showing in the macro list).

Usually, the only macros that need to be private in Excel are the worksheet/workbook events, or macros refered by other macros that don't need to be accessed by the user. As you are not in this case, you may try to make the macros public by removing the Private at the beginning of the sub definition).