Running custom commands in any xlsx file

[excelos]

Hello

How can I run custom macros/scripts/functions in any excel file I open?

For example, I want to click a button that will identify the column with the dates and filter the sheet by the current date.

I want to be able to run this code anytime and in any worksheet without having to store it as xlsm or do any change in the sheet. In other words, the code needs to be portable and 'outside of the file'.

Do you have any idea?

Ideally I would like not to use any addin but if that is the only option, is there any that you can specify your own code and can be installed in corporate Excel versions (with the relevant limitations)?

Thanks!

 

[rlv01]

If this is just for your own use, then that is what the Excel Personal Macro Workbook is for. You can create macros there that you can run on other workbooks without having to store the macros in those workbooks.

Create and save all your macros in a single workbook - Microsoft Support

Create and save all your macros in a single workbook by creating them in Personal.xlsb, a hidden workbook that opens every time you open Excel. Macros created in Personal.xlsb are available every time you open a workbook in Excel.

support.microsoft.com

 

[excelos]

Thanks! Is there anything similar for JS too given that VBA will be probably replaced by JS?

 

[rlv01]

Thanks! Is there anything similar for JS too given that VBA will be probably replaced by JS?

No idea. People have been predicting the demise of VBA for a long time and I'm sure some bleeding edge types at Microsoft who look down their noses at the VBA masses would like that to happen. But VBA is is still very popular with a very large number of people.

 

[excelos]

Thanks, I used this but I some times get a warning 'you need to unhide the worksheet before using it' i.e. the Personal worksheet. Is there a way to prevent that? I do not want to have an empty Personal worksheet visible to be able to run my personal VBA.

 

[rlv01]

Thanks, I used this but I some times get a warning 'you need to unhide the worksheet before using it' i.e. the Personal worksheet. Is there a way to prevent that? I do not want to have an empty Personal worksheet visible to be able to run my personal VBA.

That means you are doing something that is attempting to act on a worksheet in the personal macro workbook itself, which is a big mistake. The personal macro workbook is a special hidden workbook and should not be visible. Do not attempt to use any worksheets in the Personal Macro Workbook. Don't write code that references or attempts to manipulate the Personal Macro Workbook. Macros in the Personal Macro Workbook are meant to operate on other worksheets located in other workbooks. Any code using the "ThisWorkbook" object should be avoided.