Wait for Calculations to Complete

[DBCox]

Hi everyone,

I have an interesting problem. I thought Excel would not advance to the next line of the VBA code until all calculations were complete (assuming automatic calculations are turned on). However, that doesn't seem to be the case. So, I tried to add a while loop to allow it to run ALL the calculations before proceeding, but it isn't working.

I tried:

    While Application.CalculationState = xlCalculating
    Application.Wait (500)
    Wend

Also, I tried telling the workbook to wait a given amount of time to insure all the calculations completed, but that didn't seem to work either. It was as if it completely stopped everything, including calculations, until the time passed, then picked up where it left off.

How can I make sure all the calculations have been run before preceding to the next step in the code?

Thanks!

David

 

[NdNoviceHlp]

Well that is puzzling. It must have something to do with how you've coded the save part. You can turn off the warning alert or use copyfile or.... maybe post the code. Dave

 

[DBCox]

Here is the code I have currently. There is much more code before it, but I didn't want to bore and/or confuse everyone with it. Save as loop looks at a list of names in the "Save As Info" sheet to determine which external files to open and save as.

Before posting this, I used the "Step Into" function of the VBA editor and ran each step individually. It worked fine then. So, it definitely seems that the macro is not waiting for the calculations to end before moving on.

I have noticed one thing though. I guess it is due to the complexity of the workbook. Often times, I can see the calculation status at the bottom reach 100%, and then start over. I am somewhat certain this is due to calculations in one sheet affecting calculations in another. So, the first sheet calculates to 100%, then the next sheet must calculate. Maybe the macro is seeing that split second of no calculation and moving on?

Here is the current code that works when I run each line manually, but not automatically:

  'Run Calculations
Application.CalculateFull

'Save all files

counter = 2 'initialize variable
Sheets("Save As Info").Select
Range("a2").Select '1st cell with file name
Do Until ActiveCell = "" 'do until condition
    fname1 = Cells(counter, 1) 'selects cell with file name
    'this is set for column A
    filext = Cells(counter, 2) 'define file extension
    fnamesuffix = Cells(counter, 3) 'define file extension
    FileFormatVar = Cells(counter, 4) 'define file extension
    fname = directory & fname1 & fileext 'file location
    newfname = directory & fname1 & fnamesuffix & "." & FileFormatVar 'set the new filename
    Workbooks.Open Filename:=fname 'open the xls file

    'Update Data
    'ActiveWorkbook.RefreshAll
 
 'Run Calculations
   Application.Calculate
    
    
    
    If FileFormatVar = "csv" Then
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlCSV, CreateBackup:=False 'save as new file type
    
        ElseIf FileFormatVar = "txt" Then
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlText, CreateBackup:=False 'save as new file type
    End If
    
    ActiveWorkbook.Close SaveChanges:=False 'close WB

    Windows("UpdateWorkbook - Half 1.xlsm").Activate 'select workbook with file info
    Sheets("Save As Info").Select 'select sheet with file info
    counter = counter + 1
    ActiveCell.Offset(1, 0).Range("a1").Select 'This moves down the column
    
    
Loop

 

[NdNoviceHlp]

Just a couple of shots in the dark. Trial turning off the screen updating (ie Applcation.ScreenUpdating=False).
Trial turning off the document recovery "feature". Maybe the calculationfull isn't done when U tell it to calculate again and again, and again etc. within a loop. Loops are quick, calculations not so much maybe? Beyond that, your code uses selections and activecells which may be troublesome as well as inefficient. I really don't understand the need to have the wb told to calculate after info is added...doesn't it do it by itself? There's a fix here somewhere. Keep trying. Dave

 

[DBCox]

Still no luck. I've tried turning off the screen updating (it was actually off to start with, and I turned it on in hopes of seeing something that might help). I've pulled some of the calculation commands out. I put them there thinking more is better, but that didn't work either.

It is just crazy! Makes me wonder how long this has been happening and I was unaware of it...

 

[NdNoviceHlp]

I'm not entirely sure what your objectives were for your previous code but here's my guess. HTH. Dave

Dim LastRow As Integer, Cnt As Integer
With Sheets("Save As Info")
LastRow = .Range("a" & .Rows.Count).End(xlUp).Row
End With

For Cnt = 2 To LastRow
fname1 = Sheets("Save As Info").Cells(Cnt, 1) 'cell with file name
'this is set for column A
filext = Sheets("Save As Info").Cells(Cnt, 2) 'define file extension
fnamesuffix = Sheets("Save As Info").Cells(Cnt, 3) 'define file extension
FileFormatVar = Sheets("Save As Info").Cells(Cnt, 4) 'define file extension
fname = directory & fname1 & fileext 'file location '???  what about the "/"'s ?????
'??? What is directory ?????????
newfname = directory & fname1 & fnamesuffix & "." & FileFormatVar 'set the new filename
Workbooks.Open Filename:=fname 'open the xls file
If FileFormatVar = "csv" Then
ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlCSV, CreateBackup:=False 'save as new file type
ElseIf FileFormatVar = "txt" Then
ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlText, CreateBackup:=False 'save as new file type
End If
Next Cnt
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False 'close WB

ps. note the ??????????? areas

 

[DBCox]

Sorry for the confusion. That block of code reads a list of file names and associated file types. It opens each file in the list and saves them as their associated file type. The directory is defined earlier in the code. The entire macro is many, many lines long. I can post it if it will help.

 

[NdNoviceHlp]

Just opens files and then saves them as a specified file type... that's really the objective? That's it? Dave
ps. I think copyfile would be much better than SaveAs.

 

[DBCox]

Sorry for taking so long to reply. The reason I am opening files and saving them is to allow for various formats. I am creating multiple files a day, each with a different format, and each with a different number of line items. So, I have created multiple, separate, files that link to the file with the macro. This allows me to quickly, and easily, adjust the final file types. Those files are then uploaded to different servers, depending on their purpose.