J
Josh Sale
I have an application that is composed of several add-ins that runs under XL97 - XL2003 because of the diversity of the user base. All of the add-ins are developed, compiled and saved under XL97.
I've found out the hard way that non-XL97 users sometimes get run-time errors because of where the add-ins were compiled. To address this problem, I've got initialization code in my main add-in that checks the XL version and if its not XL97 it dynamically recompiles all of the add-ins including itself. Happily this resolves the run-time errors.
Unfortunately, it causes some screen flashing during the recompilation. Normally this screen flashing is annoying but bearable but when Excel and the application are being served up via Citrix the screen flashing is really gross.
The code that does the recompile looks like this:
Set Book = Workbooks(BookName) ' Find our workbook/xla
With Application.VBE
If Err.Number = 0 And Not Book Is Nothing Then
If LCase(BookName) = LCase(Book.name) Then
On Error GoTo 0
' Make one of our components visible in the VBE
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Show
name = .VBProjects(Book.VBProject.name).name
' Find the Compile toolbar button.
With .CommandBars.FindControl(ID:=578)
If Mid(.caption, 10) = name Then
' Execute the compile if possible.
If .enabled = True Then
.Execute
DoEvents
CompileBook = True
End If
Else
LogIt "Not compiling workbook " & BookName & " but " & .caption
End If
End With
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Window.Close
End If
End If
On Error GoTo 0
End With
The challenge is to get the focus to the correct VBA project without flashing the screen. As you can see, my code uses the .Show method of the CodePane object to accomplish this. I've tried using CodePane.Window.Visible = True but I've found it doesn't reliably set the focus to the desired project and so the wrong project gets compiled. .CodePane.Window.SetFocus seems to work as well as .Show but doesn't improve on the screen flashing.
The calls to the routine that perform the dynamic recompilation are bracketed by code that tries to freeze both the Excel and VBE windows. This code looks like this:
Application.ScreenUpdating = False
hWnd = myMainWhnd ' Get a handle to the application window
If hWnd <> 0 Then SendMessage hWnd, WM_SETREDRAW, -CLng(False), 0& ' Freeze the main Excel window
DoEvents ' Give it a chance to read the message
hWnd = myVBEWhnd ' Get a handle to the VBE window
If hWnd <> 0 Then LockWindowUpdate hWnd ' And lock it too
Without this code, the screen flashing is even worse than I described above.
Other things I've tried include:
- Minimizing the VBE .VBE.MainWindow.WindowState = vbext_ws_Minimize.
- Making the VBE really small application.VBE.MainWindow.Width & .Height = 0
- Looping through the VBE and closing all of its windows.
And as you might correctly guess, since I'm here, none of these things helped.
So I'm either looking for a different way to reliably dynamically recompile my various add-ins that doesn't cause the screen flash or a better way to suppress the screen flashing.
TIA for any suggestions.
josh
I've found out the hard way that non-XL97 users sometimes get run-time errors because of where the add-ins were compiled. To address this problem, I've got initialization code in my main add-in that checks the XL version and if its not XL97 it dynamically recompiles all of the add-ins including itself. Happily this resolves the run-time errors.
Unfortunately, it causes some screen flashing during the recompilation. Normally this screen flashing is annoying but bearable but when Excel and the application are being served up via Citrix the screen flashing is really gross.
The code that does the recompile looks like this:
Set Book = Workbooks(BookName) ' Find our workbook/xla
With Application.VBE
If Err.Number = 0 And Not Book Is Nothing Then
If LCase(BookName) = LCase(Book.name) Then
On Error GoTo 0
' Make one of our components visible in the VBE
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Show
name = .VBProjects(Book.VBProject.name).name
' Find the Compile toolbar button.
With .CommandBars.FindControl(ID:=578)
If Mid(.caption, 10) = name Then
' Execute the compile if possible.
If .enabled = True Then
.Execute
DoEvents
CompileBook = True
End If
Else
LogIt "Not compiling workbook " & BookName & " but " & .caption
End If
End With
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Window.Close
End If
End If
On Error GoTo 0
End With
The challenge is to get the focus to the correct VBA project without flashing the screen. As you can see, my code uses the .Show method of the CodePane object to accomplish this. I've tried using CodePane.Window.Visible = True but I've found it doesn't reliably set the focus to the desired project and so the wrong project gets compiled. .CodePane.Window.SetFocus seems to work as well as .Show but doesn't improve on the screen flashing.
The calls to the routine that perform the dynamic recompilation are bracketed by code that tries to freeze both the Excel and VBE windows. This code looks like this:
Application.ScreenUpdating = False
hWnd = myMainWhnd ' Get a handle to the application window
If hWnd <> 0 Then SendMessage hWnd, WM_SETREDRAW, -CLng(False), 0& ' Freeze the main Excel window
DoEvents ' Give it a chance to read the message
hWnd = myVBEWhnd ' Get a handle to the VBE window
If hWnd <> 0 Then LockWindowUpdate hWnd ' And lock it too
Without this code, the screen flashing is even worse than I described above.
Other things I've tried include:
- Minimizing the VBE .VBE.MainWindow.WindowState = vbext_ws_Minimize.
- Making the VBE really small application.VBE.MainWindow.Width & .Height = 0
- Looping through the VBE and closing all of its windows.
And as you might correctly guess, since I'm here, none of these things helped.
So I'm either looking for a different way to reliably dynamically recompile my various add-ins that doesn't cause the screen flash or a better way to suppress the screen flashing.
TIA for any suggestions.
josh