Create custom function which contains other functions

[okela]

Hi there,

I'm looking for something really peculiar. This is my first time creating a function and I'm having trouble building it.
To give a bit of context, I'm creating an Excel spreadsheet with macros in it. I put a password to access the macro because there are hidden sheets. But it already happened to me that one of my students opened the Excel File in Google Sheets to see the hidden sheets...

In order to prevent a case like that from happening, I'm looking at creating a function that would contain all other functions in my Workbook. I started with

Please Login or Register  to view this content.

The idea of creating my custom function is that it cannot be read by Google Sheets.

But I cannot manage to build it. In my case, the final result would be:
=TestFormula(SUM(A1:A4)) or
=TestFormula(TEXT(A1,"hh:mm"))

I want it to work both with text and numbers. My guess was to go with both N() and T() functions.

Could you please help with this one?

Thanks!

Okela

[CK76]

You can't use worksheet functions directly in VBA.

You'll need to use WorksheetFunction.FunctionName or Application.FunctionName.

However, N() and T() can't be used in this manner. For list of functions, type WorksheetFunction. in VBE and check options available.

If I guessed correctly, you'd want to convert both numeric value and text value to numeric?

If that's the case... try using CDbl().

Please Login or Register  to view this content.

[okela]

Hi CK76,

Many thanks for your answer. I've understood your point.
We're almost there.

With the function you gave me, it works with numeric results but I also need it to work with text results. Could you please help me?

Thanks!

Okela

[okela]

Ok I managed to do it with the following code:

Please Login or Register  to view this content.

Many thanks for your guidance CK76!

Okela

[CK76]

You are welcome

If your issue is resolved. Please mark the thread as solved using thread tools found at top of your initial post.