Problem using code involving Excel VBO - 'It may be inaccessible due to its protection level'

[MichelleBest]

I'm creating an action that will clear existing data from a range of cells in Excel. I've used a Code tool and tried it with the code on this site.



When I test the code, I get error messages on 'Columns' and 'GetWorkbook' saying 'they are not declared. It may be inaccessible due to its protection level.' 



I used the info on this site to add Microsoft.VisualBasic.dll as an entry in the "External References" section of the Code Options tab. Microsoft.VisualBasic was already entered under 'Namespace imports'.



It didn't work though... Is there another dll I should add for these functions? Thank you.

------------------------------
Michelle
------------------------------

[PabloSarabia]

Hi Michelle!

Can you check if you have the function "GetWorkbook" in the Global Code tab of the Business Object Properties?

I check and if exists in this tab, this should works.

And... the column variable... I think that "Column" is referring the property from the WorkSheet object, but you need to put object name first with a full stop. In your case: "ws.Columns(1)"



Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[MichelleBest]

Thanks, Pablo. Yes, youre right about the Column needing ws. in front. That compiles ok now.

I still have an error on the getWorkbook though. I checked and the function is included in the Global Code...



------------------------------
Michelle
------------------------------

[MichelleBest]

Ah right, sorry. Those functions were not in the Global Code section of the Business Object I was working on. I've copied them across now and the code compiles properly. So was this because the functions were labelled as Protected in the Excel VBO? And so they could only be used within there?  Why would some of the functions be useable in other business objects and some be protected?  Thanks again for your help!

------------------------------
Michelle
------------------------------

[PabloSarabia]

Hi Michelle!

The "Protected" label is referring about the accessibility of the function or variable. It's hard to explain the meaning of this... but... basically that means that the function is accessible within its Business Object.

If you want to know more, here you have some links from Microsoft

Access Modifiers
Protected 

Hope this clarify your questions


Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[MichelleBest]

Thanks. I don't have any errors when I Check Code now, but when that snippet runs, I get an error message saying 'The given key was not present in the dictionary.'  Any ideas?

------------------------------
Michelle
------------------------------

[PabloSarabia]

Hi,

This kind of errors ocurrs when you try to access a list or dictionary and the key does not exists. 

Maybe you are getting this error somewhere in the line 14.

Remember that, when we speak about keys and index, we start counting in 0. So... if you have 14 columns, your "lastdatacolumn" need to be 13. 

Can you check that? 


Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[ewilson]

@MichelleBest,

There's an internal dictionary that keeps track of which workbooks are linked to which handles (i.e. Excel instance). Are you trying to work across two different Excel VBOs or are you doing all of this within the standard VBO and just created your own action within it?

Cheers,
​

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[MichelleBest]

@PabloSarabia It was failing right at the beginning on the sixth line, however I think this might be something related to a fudge I had made that I've now undone (see below)
​​​​​

------------------------------
Michelle
------------------------------

[MichelleBest]

@ewilson I think it was something to do with Excel instances and something that I had tried to implement to fix a different problem I was having. Perhaps I should start from the beginning, but if you think I should start a new thread, just let me know and I'll paste this into a new request.

In Studio, I have the MS Excel VBO and then I started to create my own with little reusable Excel snippets in a new object, thusly:


Everything was going swimmingly until I had need to add a Code action to one of the pages in my object (the code was to clear data from a number of columns). I added the code and got the messages about protection levels as above.

With Pablo's help, I then copied the relevant 'protected' parts of the Excel VBO to the Global Code tab of my object Initialise page... I had a number of new issues after that, but rather than go into them just yet - should I just create my custom bit of code within the Excel VBO?  Are you allowed to do that?

------------------------------
Michelle
------------------------------