Slow recalculation of workbook

[vaclavvesely]

Hi, I have problem with my workbook, I solved first issue with user defined functions, but now I have very slow workbook, with small amount of data (comparing other workbooks I made), can somebody help me?

reference to my last thread
https://www.mrexcel.com/forum/excel-questions/1110623-last-used-column-data-ignoring-formulas.html

 

[offthelip]

I haven't looked at your workbook from your last thread but I can see that you are using a UDF which you have forced to be volatile by using application volatile,
this I believe is the UDF:

Function DATUM_HOTOVO(myCell As Range) As Long
Application.Volatile
    With myCell.Parent
        DATUM_HOTOVO = Cells(3, .Cells(myCell.Row, .Columns.Count).End(xlToLeft).Column)
    End With
End Function

I don't know how many places you use this function but the problem with making it volatile is that every copy of the function will be recalculated every time there is any recalculation. This could easily make your workbook very slow.
I would start by taking out the application.volatile statement and see if that solves your problem, if that doens't work I don't know what the layout of your data is or how it changes , but I would look at a different way of triggering the calculation of where the last used column is rather than dong it on every recalculation.

 

[vaclavvesely]

I've tried to delete the "aplication.volatile" but it is still the same.

 

[offthelip]

One thing you could try to see if it is the UDF that is causing the probelm, is change the UDF so that it just returns a constant and does no calculations. This would then prove or otherwise whether the UDF is the probelm.

 

[vaclavvesely]

I've tried it and now it is much faster, so problem is in UDF.

 

[offthelip]

So that means you need to think carefully about how your system is designed. I am not entirely clear what you are trying to do. Your statement at the top of your previos thread:

Hi, I have a spreadsheet where is a table filled with formulas returning "" by default and I need macro which will give me a last used cell/column containing any value ignoring cells containing ""

States what you are trying to do but doesn't state when or why you need to use this information. The problem you have created by using a UDF to calculate is that you are calculating this for many lines all the time. I doubt that you really need to do this.
So there two ways to solve this, one is to only calculate the value when something on the line changes the second is to only calculate the value when you want to use it.
without knowing more about your system I can't help you further. But I suggest you bin the UDF but then incorporate the same code into what ever other macros you have got either when writing or reading and store value in a spare colun at the end of the row.

 

[vaclavvesely]

Description is in the previous post, partially.
I just need to create a table, lets say "project HMG" where in columns header i have date(days) and in rows i have tasks and timeline from-to i have to finish the task. You can see it on the picture i shared. The idea was to have formulas which will give me automatically a dates from-to according to values in the table (timeline) - manually inserted as "x" OR if I put a date from-to into task then i will have automatically marks "x" in corresponding cells (columns). So at the end I can choose the way to create timeline (HMG) of the project. I have tried to get this functionality using only with formulas, but without good result, so I made a UDF which works fine, but its slow.
I hope now its more clear what i need (sorry for my bad english ).

 

[offthelip]

What I suggest is moving the code that you have put in your UDF into the worksheet change event and only calculate the value for the line/lines that have changed. Store this value in a spare column to the right of your worksheet. ( you can hide this coluolmn if you need to) . This means that you will only do the calculatin once on every chnage rather that hundreds of times.
Another alternative is program all the functionality into VBA, which would probably be very fast.(probably the way I would have done it)

 

[offthelip]

I had further thoughts about your problem and I have coded what I think you want using just a single line of a worksheet :
Put a start date in Cell A2 and an End date in Cells B2 . then put this code in the workhseet change event for the workhseet:
The code will automatically put asterisks in cells D2 to BC 2 to coincide with the week numbers that are the start date and end dates.
Also if you add asterisks to cells in the range d2 to BC2 it will update the start and end dates to tie up with them. It all works really fast and you would just need to detect the row it was applicable to

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("D2:BC2")) Is Nothing) Then
 inarr = Range("D2:bc2")
 std = Cells(2, 1)
 etd = Cells(2, 2)
 syr = Year(std)
 started = False
 ended = False
  For i = 1 To 52
   If (inarr(1, i)) <> "" And Not (started) Then
      std = DateAdd("ww", i - 1, DateSerial(syr, 1, 1))
      started = True
   End If
   If (inarr(1, i)) = "" And (started) And Not (ended) Then
      etd = DateAdd("ww", i - 1, DateSerial(syr, 1, 1))
      ended = True
   End If
   Next i
 Application.EnableEvents = False
 
  Cells(2, 1) = std
  Cells(2, 2) = etd
  
 Application.EnableEvents = True


End If


If Not (Intersect(Target, Range("A2:B2")) Is Nothing) Then
 std = Cells(2, 1)
swkno = Application.WorksheetFunction.WeekNum(std)
etd = Cells(2, 2)
ewkno = Application.WorksheetFunction.WeekNum(etd)
dater = Range(Cells(2, 4), Cells(2, 56))


For i = 1 To 52
 If i < swkno Then
  dater(1, i) = ""
 Else
  If ewkno < i Then
   dater(1, i) = ""
  Else
   dater(1, i) = "*"
  End If
 End If
Next i
Application.EnableEvents = False
Range(Cells(2, 4), Cells(2, 56)) = dater
End If
Application.EnableEvents = True


End Sub

Note I haven't taken account of what happens if the start datea nd end date are in different years.