Translate VB Code into Office Script

[AliGW]

I have a tiny code snippet that allows me to click a button, select a range and convert it to values:

Please Login or Register  to view this content.

I understand that it's now possible to create a button for an Office Script, so I'm wondering if anyone would know:

1. how to translate this code to an Office Script?
2. how to assign it to an Office Script button (although I can probably work this bit out myself: https://support.microsoft.com/en-gb/...c94cdb625766)?

Thanks for any insights.

[AliGW]

So I recorded a script:

Please Login or Register  to view this content.

I am now wondering how to make it work on the current selection isntead of a fixed one. I'll keep searching ...

[TMS]

I think you can use, for example:

Please Login or Register  to view this content.

That said, I have not yet had the opportunity to play around with this stuff. Only realised it was available to me a couple of days ago. Thought I needed a business subscription

[AliGW]

It arrived in the Insider desktop version some time ago.

I have also found this script:

Please Login or Register  to view this content.

It replaces values in the cirrent selection with "Yes" - I need to work out how to edit this so that it converts formulae to values.

[TMS]

Maybe this ...

Please Login or Register  to view this content.

[TMS]

If I have understood correctly, you cannot share scripts with a Personal Account, nor can you create script buttons.

You can add Office Scripts to the QAT. You still need to access All Scripts and then select your Paste Values script and run it.

Lots to learn.

[TMS]

Your code for setting a selected range to "Yes" is a little dangerous (as I discovered).

Your code selects Sheet1 so, regardless of which sheet you have selected, Sheet1 will be selected anda cell, or cells, could be corrupted (if Sheet1 exists).

Please Login or Register  to view this content.

[AliGW]

Maybe this ...

I'd worked out the first two sections - thanks, I'll give it a go.

... nor can you create script buttons.

So it would seem - an oversight on MS' part, I feel - I've submitted feedback.

Your code for setting a selected range to "Yes" is a little dangerous (as I discovered).

Same, and I wasn't proposing using it as it stood, merely as a template. I found that one online, too.

You still need to access All Scripts and then select your Paste Values script and run it.

Yes - without the ability to add a button, it's a bit of a waste of time, however I suspect that will be added down the line. Still a useful learning exercise, though.

Thanks for having a play - I'll report back later.

[AliGW]

Yes, that does it - thank you.

I just need to find out now how to add the script to the selection available in the Automate ribbon - not sure it's currently possible.

Thank you!

[TMS]

You're welcome. Thanks for the rep.

[TMS]

Forgot to say, thanks for the question. I struggle to learn and apply stuff without a challenge. I now know a lot more about Office Scripts than I did.

Can't see me ever using it personally. And, to be honest, I’m not sure who will use it IRL. IMO, it's not as easy to record, edit and adapt as VBA. And the interface is a bit clunky.

The examples I have seen so far aren't necessarily that intuitive and, ideally, you need to be (or get) familiar with JavaScript. Having a programming background might help, but how many of us have that?

Anyway, looks like hours of fun! Thanks again.

[AliGW]

The issue is that VB Script is being deprecated at some point down the line, and we already have a situation were VB Code won't run in the browser version or on mobile devices.

There are many fairly simple VB routines currently assigned to workbook buttons that could be replaced by an OfficeScript assigned to a button on the worksheet - like mine - making the routine usable online and on mobile devices. So I can see it taking off if they add the button option for all users.

I already know some JS from web design - just need to brush up on it!