OFFSET functions are making my macros extremely slow

Hi all,

In my workbook I have one sheet that contains a form for data input. This data can be saved with a button which then copies all data into one row on another sheet. Item numbers are shown in the form in a dropdown list which can then load that data back onto the form sheet.

This was extemely slow and I found out that this was due to 22 offset functions used in the form sheet. They change images based on values in dropdown lists. This problem was solved by changing the value of cell A1 in a hidden sheet when running the macros so that offset is not used. In the name manager I changed all functions to do nothing if that cell contains FALSE. At the end of the macro that loads the information onto the form, I change it back to TRUE which means that offset function can be used again. That was a huge improvement.

Currently we use many copies of the same workbook to calculate cost for each item (one workbook for one item). Several of these files could be opened for copy pasting into the new file with macros so that they can be saved. The problem is that these older files seem to affect the macros in the new file as well when opened at the same time, and trying to load an item in the new file.

What I used so far to try to speed up the macro that loads the item:

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

I also started all references to cells and ranges with 'ThisWorkbook'. None of this worked.

This didn't work. I did try to delete all the images in the form of the external files (without macros) that use the offset function. That did indeed make it a lot faster. Unfortunately, I cannot use the solution above for all of the files as there are way too many.

If I easily could I would have uploaded a sample file. In this case however, it would take a tremendous amount of work to make one.

In short, is there any way I can prevent OFFSET functions in other opened excel files to affect the speed of the macros in the file I am working in?

I think it is the offset function.

Beginning of macro:

ThisWorkbook.Worksheets("SavedSpecs").Range("A1").Value = False

In the name manager I changed the functions to: =IF(SavedSpecs!$A$1=TRUE, OFFSET FUNCTION, "")

Then it worked well. For that reason I thought it is because of the offset functions. Unfortunately there is no way I can modify other files (copies) that might be open at the same time.

There must be some kind of fix for this?

Thanks for your answer. That also explains why I haven't been able to find a solution for this.

If you have any ideas on how to apply a similar method to other open files (which are copies with other data) that affect the one running the macros, I would happy to hear your suggestion.