Code doesn't work unless delay is added

[wist]

Hi all,

I can't wrap my head around this. I have a userform that will resize frames and listboxes based on the number files in the folder. However the resize doesn't work unless I input either 'DoEvents' or 'Wait' into the code.

The code is in the form and not in a separate module.

Private Sub PeriodBox_Click()Dim Recs As String
Dim CountRecs As MSForms.Label
Dim i As Long


With RecsForm
    .ScrollBars = fmScrollBarsNone
    .Width = 436
    .Height = 120.75
End With


'add recs to listbox if available
If Dir(path & FYBox.Value & "\" & PeriodBox.Value & "\Intercompany Recs * # *") <> "" Then
    FileBox.Clear


    Frame1.Clear
    Frame2.Clear
    Frame3.Clear
    Frame4.Clear


    Recs = Dir(path & FYBox.Value & "\" & PeriodBox.Value & "\")
        Do While Recs <> ""
            If Recs Like "Intercompany Recs* [#] *" Then FileBox.AddItem "IC Rec | " & Right(Recs, 9)
            Recs = Dir
        Loop


'set height based on number of recs
       [COLOR=#ff0000][/COLOR][U][B]If FileBox.ListCount < 9 Then FileBox.Height = 85 Else FileBox.Height = FileBox.ListCount * 10[/B][/U][COLOR=#ff0000][U][B] - this doesn't work if below Wait or DoEvents is added, why?[/B][/U][/COLOR]


        [U][B]Application.Wait (Now + TimeValue("0:00:1"))[/B][/U]


        Frame1.Height = FileBox.Height + 18
        Frame2.Height = FileBox.Height + 18
        Frame3.Height = FileBox.Height + 18
        Frame4.Height = FileBox.Height + 18


        If 150 + FileBox.Height < Application.Height - 20 Then RecsForm.Height = 150 + FileBox.Height Else RecsForm.Height = Application.Height - 20


        If FileBox.ListCount * 20 > Application.Height Then
            With RecsForm
                .ScrollBars = fmScrollBarsVertical
                .ScrollHeight = FileBox.ListCount * 12
                .Width = 446
                .FileBox.SetFocus
                .ScrollTop = 0
            End With
        End If


'rec counter
            Set CountRecs = RecsForm.Controls.Add("Forms.Label.1")
                With CountRecs
                    .Visible = True
                    .Name = "RecsCount"
                    .Caption = "Count: " & FileBox.ListCount
                    .Top = FileBox.Height + 102
                    .Left = 20
                End With
        
        If RecsForm.Height < 120.75 Then RecsForm.Height = 120.75
[U][B]        FileBox.ListIndex = -1 - [COLOR=#ff0000]this line also doesn't work without Wait or DoEvents[/COLOR][/B][/U]
End If


End Sub

Explanation would be appreciated

Thanks

 

[joeu2004]

What do you mean by "does not work"? What exactly happens instead of what you intended?

Can you provide more complete code, including the userform set-up? A turnkey implementation that we can run without depending on external data (e.g. eliminate the dependency on Dir).

Off-hand, I can only guess that some actions are performed in another thread, and it requires some "synchronization" in order to work as intended.

-----

Aside.... Wait Now + #0:0:1# does not reliably wait 1 full second. VBA Now is rounded down to the second. So the actual delay depends on where you are in the current one-second period. The wait could be almost zero time. It is more reliable to write Wait CDbl(Date) + (1 + Timer) / 86400# . Even CDbl(Date) is more reliable than simply Date, avoiding the special way that VBA treats type Date.

Also, I'm surprised that Wait (instead of DoEvents) helps at all. Usually, no other Excel or VBA thread in the same process can run when the current VBA thread is running.

In contrast, DoEvents causes the current VBA thread to sleep until the system process management chooses to run the thread again.

(The kernel Sleep function might have the same effect as DoEvents, but causing the current VBA thread to sleep for at least a specified time, which is your intent by using VBA Wait. But it is not clear to me why that might be true for kernel Sleep, but not for VBA Wait. TBD)

 

[wist]

What do you mean by "does not work"? What exactly happens instead of what you intended?

- first highlighted line Filebox.height will not resize the height unless the delay is instroduced
- second highlighted line filebox.listindex = -1 will not set it to -1, instead it's will be something like 5 or 9 ..not sure why not 0

Can you provide more complete code, including the userform set-up? A turnkey implementation that we can run without depending on external data (e.g. eliminate the dependency on Dir).

Off-hand, I can only guess that some actions are performed in another thread, and it requires some "synchronization" in order to work as intended.

ugh, it's a long one, here's a part that's related to my issue, not sure if you can get it to work.

    Public path, FXpath, MasterPath, MonthEnd, MonthEndDate, CalYear, IntercoFolder, FileBoxItem As String
Public TLcheck As Workbook
Public Sub paths() 'set up basic paths


    IntercoFolder = "C:\"
    FXpath = "C:\ZZ - Exchange rates\"
    MasterPath = "C:\MasterFile.xlsx"
   
    path = IntercoFolder & Country.Value & " interco\"


End Sub

Private Sub UserForm_Initialize()

Call paths 'get basic paths


With RecsForm
    .ScrollBars = fmScrollBarsNone
    .Width = 436
    .Height = 120.75
End With


'add IC Countries
With CreateObject("Scripting.FileSystemObject").GetFolder(IntercoFolder)
    For Each Folder In .Subfolders
        If Folder.Name Like "* interco" Then Country.AddItem Left(Folder.Name, Len(Folder.Name) - 8)
    Next Folder
End With


Country.AddItem "Total TL Check"


FileBox.Clear


End Sub

Private Sub UserForm_Scroll(ByVal ActionX As MSForms.fmScrollAction, ByVal ActionY As MSForms.fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)
'disable scrolling to top when clicking on a frame


    If ActionY = fmScrollActionFocusRequest Then
        ActualDy.Value = 0
    End If


End Sub

Private Sub Country_Click() 'choosing country to populate FY


FYBox.Clear
PeriodBox.Clear
FXBox.Clear


Call paths 'to provide basic paths
    
    If Country.Value <> "Total TL Check" Then
        With CreateObject("Scripting.FileSystemObject").GetFolder(path)
            For Each Folder In .Subfolders
                FYBox.AddItem Folder.Name
            Next Folder
        End With
    End If


End Sub


Private Sub FYBox_Click() 'choosing FY to populate PD and FX
Dim FXfile As String


PeriodBox.Clear
FXBox.Clear


    With CreateObject("Scripting.FileSystemObject").GetFolder(path & FYBox.Value & "\")
        For Each Folder In .Subfolders
            PeriodBox.AddItem Folder.Name             
        Next Folder
    End With


    With CreateObject("Scripting.FileSystemObject").GetFolder(FXpath & FYBox.Value & "\")
        For Each file In .Files
            FXBox.AddItem file.Name
        Next file
    End With


End Sub

After this there's the code I posted initially

Aside.... Wait Now + #0:0:1# does not reliably wait 1 full second. VBA Now is rounded down to the second. So the actual delay depends on where you are in the current one-second period. The wait could be almost zero time. It is more reliable to write Wait CDbl(Date) + (1 + Timer) / 86400# . Even CDbl(Date) is more reliable than simply Date, avoiding the special way that VBA treats type Date.

Works great, thank you but still I'm curios why it needs to be there

Also, I'm surprised that Wait (instead of DoEvents) helps at all. Usually, no other Excel or VBA thread in the same process can run when the current VBA thread is running.

In contrast, DoEvents causes the current VBA thread to sleep until the system process management chooses to run the thread again.

(The kernel Sleep function might have the same effect as DoEvents, but causing the current VBA thread to sleep for at least a specified time, which is your intent by using VBA Wait. But it is not clear to me why that might be true for kernel Sleep, but not for VBA Wait. TBD)

it is very mysterious to me as well :S maybe you will get a better idea from the whole code above, also this is how to uform looks like



Thank you

 

[joeu2004]

I'm afraid I will not be able to help you. Sorry.

I was hoping for a ``turnkey implementation that we can run without depending on external data (e.g. eliminate the dependency on Dir)``. What you provided has external dependencies like MasterFile.xlsx and a folder of files.

Also, I do not see where FileBox is declared. Presumably, it is a class; but I cannot find it with a google search, as I can for InputBox and MsgBox. I wonder if FileBox is part of a third-party add-in.

In any case, what I would need in order to proceed is a self-contained Excel file, stripped down the minimum elements that are sufficient to duplicate the problem. That would require some effort on your part to redesign some elements -- for example, replacing a loop around Dir with some canned data -- and hopefully that would not mask the real problem (need for delays).

But perhaps someone else who recognizes what you are doing can explain the need for the delays without the benefit of a working example.

Good luck! Sorry that I could not be more helpful.

 

[DanteAmor]

Here more information

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doevents-function

 

[wist]

I'm afraid I will not be able to help you. Sorry.

I was hoping for a ``turnkey implementation that we can run without depending on external data (e.g. eliminate the dependency on Dir)``. What you provided has external dependencies like MasterFile.xlsx and a folder of files.

Also, I do not see where FileBox is declared. Presumably, it is a class; but I cannot find it with a google search, as I can for InputBox and MsgBox. I wonder if FileBox is part of a third-party add-in.

In any case, what I would need in order to proceed is a self-contained Excel file, stripped down the minimum elements that are sufficient to duplicate the problem. That would require some effort on your part to redesign some elements -- for example, replacing a loop around Dir with some canned data -- and hopefully that would not mask the real problem (need for delays).

But perhaps someone else who recognizes what you are doing can explain the need for the delays without the benefit of a working example.

Good luck! Sorry that I could not be more helpful.

thanks a lot for your help, the delay is working fine so i might just keep it as I'm struggling with another issue now
https://www.mrexcel.com/forum/excel...-exception-occured-possibly-memory-issue.html