Macros & alternatives

[Mangesh Yadav]

Hi Everyone,

I have a spreadsheet which uses macros extensively. Now if a particular
company has a policy which does not allow macros, what is the alternative
for my spreadsheet.

I know the question is bit unclear regarding the policies, but even I don't
know how this is done. Another question I have is: when a particular company
says that it does not allow macros, what is it that they do to prevent the
user from allowing a macro to run. (Is it something like they disable the
Tools > Security feature...?)

Thanks in advance.

 

[Anne Troy]

Sorry, but I'm of the opinion that any company that disallows macros is
stupid. I wouldn't want to work for them even part time.
*******************
~Anne Troy

www.OfficeArticles.com

 

[Tim Williams]

What do your macros do?

If a company doesn't allow macros then it would seem you are probably
out of luck. You could try signing your workbook with a digital
certificate and they may then accept it.

Disabling the user's ability to change their macro security level is
probably how they would "not allow" macros, but that seems a little
short-sighted if applied as a global setting.

Tim

 

[Mangesh Yadav]

Hi Tim,

Thanks for the response. Even I thought on the same lines regarding the
digital certificate. But other than that there seems no way out.

As for my macros, its basic calculation and other stuff which is repetitive
in nature. Till then will wait for some more responses.


Mangesh

 

[Guest]

You could instantaite Excel in VB or C#, then with the Rxcel objetc, open and
manipulate the workbook.
Relatively painless in VB as the Excel object library should be referenced
and thus the VBA that you are used to becomes available.

VB Example
Open Visula Studio / Visual Basic 6.0
start a new starndard exe
set a refenerence to Microsoft Excel 10.0 Object Library
add a button & the following code:

Option Explicit

Private Sub Command1_Click()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xl = New Excel.Application

Set wb = xl.Workbooks.Add

Set ws = wb.ActiveSheet

ws.Range("B2") = "Hello World!"

xl.Visible = True

'clean up & quit -- leave excel visible
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing

End
End Sub

 

[Mangesh Yadav]

Hi Patrick,

Thanks for your suggestion. Have never done something like this. But will
definitily try out a small example now. And also wait for some more
suggestions before I take some decision.

But thanks all the same once again.

Mangesh

 

[DM Unseen]

Mangesh,

Maybe they would allow office COM addins(they are not actually macro's
but compiled code). you still might need to sign them digitally though.

DM Unseen

 

[Bob Phillips]

Mangesh,

Many system administrators will restrict the functions of products and the
OS, so that the average user is functionally crippled, as part of company
policy. Such restrictions include the ability to set date and time, to add
system variables, etc., and within Excel to run macros by setting the
security setting to high and stopping the ability to change that.

In this instance, if you want to manipulate Excel spreadsheets via code, I
think you will have to do it externally via automation. That is, create an
application using an approved tool, such as VB, that uses Excel as a server
app, and manipulates it from within your app. It is very straight-forward,
the main learning exercise is to be aware of which object you are
addressing, and reference properly at all times.

Of course, this still leaves the problem that the user will not be able to
install it, the CD drive will be disabled as well if not even sealed, so you
will need the co-operation of the system administrators for that. They might
say no, they might say yes but insist on testing it themselves, or they
might say yes. As you need their co-operation, you could also ask them about
whether they would install a COM add-in, or a simple Excel add-in. Whatever
way, your first action should be to discuss with them, otherwise you might
just be wasting your time and effort.

 

[Mangesh Yadav]

Hi Patrick,

I tried a small example which launches my excel sheet, somthing like

Private Sub Command1_Click()
Dim xl As Excel.Application
Set xl = New Excel.Application

xl.Workbooks.Open "d:\my_excel.xls"
xl.Visible = True

Set xl = Nothing
Unload Me

End Sub

And I turned security settings to high, disabled the check boxes in the
"trusted sources" tab.

The above code opened the my excel file nicely, and allowed me to work with
all macros.

Why does this happen, I mean although opening excel directly does not allow
the macros, whereas the above code does it finely. I am not a tech guy so
this went above my head.



Mangesh

 

[Mangesh Yadav]

Hi Bob,

Thanks. This is all quite new to me, but yes, I think I have learnt quite a
lot from this post. I will definitely explore all these ideas, and come back
to you. I have tried Patrick's suggestion, and it seems to take me in the
right direction.

Thanks to you once again.

Mangesh

 

[Mangesh Yadav]

Hi DM Unseen,

Thanks for your suggestions. I will have to explore all these options.

Mangesh

 

[NickHK]

Bob,
How would an admin lock the user's security setting at high ?

NickHK

 

[Bob Phillips]

They would install Excel with a high setting, and they can remove the
ability to change it.

 

[NickHK]

Bob,
What I mean is how would they "remove the ability to change it" ?

NickHK

 

[Erasmus Bowen]

We threw away tons of macros when we discovered Pivot tables.
And when we discovered that Pivot tables can re-read from external data, we
threw away even more.
I've hardly touched a macro in years and the only time I do is when they
start going wrong during Excel version upgrades

Erasmus

 

[Tim Williams]

By restricting access to the registry settings?

Tim

 

[Mangesh Yadav]

Hi Erasmus,

Before I discovered macros, I already knew about Pivot Tables. And no, I
cannot replace my macros with pivot tables

But thanks anyway for your response.

Mangesh

 

[Mangesh Yadav]

Hi Tim,

If "restricting access to the registry settings" is done, then will there be
a problem launching and excel application through a VB (as explained in an
earlier post in this thread).

something like what Patrick has shown below:

You could instantaite Excel in VB or C#, then with the Rxcel objetc, open and
manipulate the workbook.
Relatively painless in VB as the Excel object library should be referenced
and thus the VBA that you are used to becomes available.

VB Example
Open Visula Studio / Visual Basic 6.0
start a new starndard exe
set a refenerence to Microsoft Excel 10.0 Object Library
add a button & the following code:

Option Explicit

Private Sub Command1_Click()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xl = New Excel.Application

Set wb = xl.Workbooks.Add

Set ws = wb.ActiveSheet

ws.Range("B2") = "Hello World!"

xl.Visible = True

'clean up & quit -- leave excel visible
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing

End
End Sub

Mangesh

 

[Mangesh Yadav]

Carrying on from here.....

Lets say I have a VB application which launches my excel file. Now my macros
mainly consist of
1. Event modules for each sheet.
2. User forms
3. UDFs
4. Standard modules

Suppose I want to protect my business logic (which resides in the standard
modules), one way is ofcourse to protect the VBA with the password (which I
don't know how safe it is). But is there any way where I can convert these
modules into kind of executables so that the user cannot manipulate the code
in these standard modules. And also allow the code to run on the click of a
button in the worksheet as if I am running a standard macro.

Mangesh

 

[NickHK]

Mangesh,
Even launching Excel from VB, you still have Excel macro code, which is
against policy.
You will need all the code to reside in your VB app, no code associated with
Excel. You will have manipulate the Excel object model from outside (your VB
app) rather than inside (macro code).

Nick