Computer Related > Excel Macros Computing Issues
Thread Author: zippy Replies: 9

 Excel Macros - zippy
Just upgraded to Office 365 (installed not web).

Several Excel macros do not work as they used to in Office 2010.

The number crunching seems to work but the output and graphs are corrupted.

The objects and methods seem to be the same so I can't see why they are not working.

Any ideas?

Thanks, Z
 Excel Macros - smokie
Can you easily replicate one of them again from scratch within 365 to see if it's a conversion error or something else?
Last edited by: smokie on Tue 12 Nov 19 at 09:28
 Excel Macros - zippy
For some background.

10k lines of code. Number crunching working. Output partly working, just formatting such as dates and currencies not working.

Using Excel as can't get the budget / IT time for a proper solution.

Some digging in to the code and stepping through on two identical laptops, save for the versions of Excel and O/S.

When running on Excel 2010 the macro copies over the formatting from the origin cell when copying data. Excel 365 does not, hence the different outputs.

I now need to find some time to program the formatting.
 Excel Macros - smokie
Using macros? I wouldn't have thought that too difficult but I've only ever written about 4 of them. Are you just trying to copy/paint a format on a column/row using the equivalent of the brush which you see in the right click menu?
 Excel Macros - zippy
>> Using macros? I wouldn't have thought that too difficult but I've only ever written about
>> 4 of them. Are you just trying to copy/paint a format on a column/row using
>> the equivalent of the brush which you see in the right click menu?
>>

Yes, but it wasn't needed as the formatting was copied over with the selection. It isn't now.

It will take a couple of hours to fix, now I know why it's not working.
 Excel Macros - No FM2R
Dafuq are you doing something that complex or that important in Excel for?

Bite the bullet and properly fix it.
 Excel Macros - zippy
>> Dafuq are you doing something that complex or that important in Excel for?
>>
>> Bite the bullet and properly fix it.
>>

It works and saves a good couple of hours a day per user.

IT have refused. We can't get IT engagement for it - from one of the biggest banks in the UK - it's carp.
Last edited by: zippy on Sat 23 Nov 19 at 20:28
 Excel Macros - zippy
Missed the edit. I could build it in using Visual Studio.

There are issues though, because some of the built in features of Excel saves hours of programming time (graphs, sorting arrays etc.).

Also, importing an application to a company computer has to go through so many loops that it's easier and quicker to write the thing in VBA straight off.
 Excel Macros - Kevin
>IT have refused.

Are you sure that the bank would approve of you using home grown, unsupported and untested code for business use?
 Excel Macros - zippy
>> >IT have refused.
>>
>> Are you sure that the bank would approve of you using home grown, unsupported and
>> untested code for business use?
>>

I understand your point. They don't care, they call it end user computing and don't monitor or support it.

Internal audit have seen it so it is recorded. It is not using any personal info and is GDPR compliant.

The number of these systems and processes used by areas of the bank is frightening. I have worked at 3 banks - they are all the same.

I have even seen another department program their reports - not database reports but word docs based on findings - to be written by a macro changing sentences from a library and just inserting figures. For example: "The borrower owes £1m, repayable over 60 months and their credit score is 100%" and if the macro was run the next day it would read "The loan to the customer is £1,000,000 repayable over 5 years. The customer is fully credit covered" and the staff in the dept. do very little real analysis anymore. The reports are quite complex - and run to 20-30 pages depending on what is required but they are about 90% automatically created by end user macros and VBA.

Latest Forum Posts