stilldv.blogg.se -

#Excel on my macbook keeps freezing code
#Excel on my macbook keeps freezing free

Newsheet.Cells(x1,x1).Resize(Ubound(output,1), Ubound(output,2)).It is very difficult to see all the headers when we are scrolling down.

'These numbers are not tested, you should test. If you expect to have more data than this, you will need to do some gymnastics to make it work. The issue is that reading/writing arrays to Excel Ranges is still bound by the 2003 size limits (65536 rows. By pulling those interactions out of the loops, we can achieve a serious performance boost. Within your inner loop, VBA is accessing Excel 7 times. Excel and VBA are slow when they interact.

#Excel on my macbook keeps freezing code

Use Variant Arrays: This should speed up your code the most, but also has a caveat.

Set newsheet = Worksheets(CStr(newsheetname)) Set oldsheet = Worksheets(CStr(oldsheetname))

Fully reference you calls to Cells, so that it accesses the right worksheet.ĭim oldsheet as Worksheet, newsheet as Worksheet Along with this, don't use Activate or Select. For repeated access, it should be faster to store that in a variable.

Use Worksheet Variables: You keep accessing your Worksheets by name.

My preferred way of dealing with this is to store the current calculation setting, turn off calculations, and then restore the original setting at the end.Īpplication.Calculation = xlCalculationManual Turn off Calculations: If you have a lot of formulas running, this can slow down what happens when you place a value into the workbook, as it needs to recalculoate.

#Excel on my macbook keeps freezing free

You can free those resources to work on what you need done by adding Application.ScreenUpdating = False to the beginning of your code, and Application.ScreenUpdating = True to the end. Turn off Screen Updating: It is a lot of work for Excel to render the screen. Range(Cells(place, 1), Cells(place, x3 - 1)).Value = copyRangeĬells(place, x3) = Sheets(CStr(oldsheetname)).Cells(1, j)Ĭells(place, x3 + 1) = Sheets(CStr(oldsheetname)).Cells(2, j)Ĭells(place, x3 + 2) = Sheets(CStr(oldsheetname)).Cells(i + 2, j)Īs mentioned in the comments, DoEvents will probably allow the user to interact with Excel while the macro is running, and prevent Excel from becoming unresponsive.Īn alternative approach is to speed up your code, so that it finishes before the user has reason to believe that it has crashed. 'This was the only way to copy multiple cells at once other ways it would just errorĬopyRange = Range(Cells(i + 3 - x1, x1 - 2), Cells(i + 3 - x1, x3 - 1)).Value Range(Cells(x1, x1), Cells(x1 + 1, x3 - 1)).Value = copyRange

'Declaring All of my Variables that are pulled from UserformĬopyRange = Range(Cells(x1, x1), Cells(x1 + 1, x3 - 1)).Value 'This brings up the data for my dropdown menu to pick a sheet to pull data from The Code Private Sub UserForm_Initialize() All size references and sheet names needs to be from a UserForm so I don't know the name of the sheets or size ahead of time this resulted in some weird code at the beginning of my loop.Īlso, if you see anyway to make my code more efficient that would be greatly appreciated! The data is a bunch of numbers that need to be put in one column, and that the 14 (normally 14) columns before it label each number with dates and other data. And it would be best if there was a solution in VBA so people don't have to worry about it and it works perfectly the first time. It would just be better so people that need to use the macro don't freak out when it freezes. Now I was wondering if there was a way to slow it down so that Excel does not go into "Not Responding" mode while it runs. It works great (I think) and does exactly what it needs to do and takes under 1 minute to run, going through ~70k cells and organizing them. So at work I am working on a macro/UserForm in Excel for someone.