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

Try putting this code at the beginning of your recompile code:

Application.ScreenUpdating = False

and at the end of it

Application.ScreenUpdating = True

"Josh Sale" wrote:

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

Thanks Rich. However as I showed in the code included in my posting, I'm
already doing that.

josh




"Rich J" wrote in message
...
Try putting this code at the beginning of your recompile code:

Application.ScreenUpdating = False

and at the end of it

Application.ScreenUpdating = True

"Josh Sale" wrote:

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 didn't look thru the whole code you posted. I had that problem when the
application.screenupdating = False was in one of my subroutines. If the
subroutine is being called many times during execution then depending where
.... = True is you might be causing the flashing. I moved the line to the
main calling routine and the screen does one flash and the changes are there.

"Josh Sale" wrote:

Thanks Rich. However as I showed in the code included in my posting, I'm
already doing that.

josh




"Rich J" wrote in message
...
Try putting this code at the beginning of your recompile code:

Application.ScreenUpdating = False

and at the end of it

Application.ScreenUpdating = True

"Josh Sale" wrote:

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

Right, as I tried to say in my original post, all of the recompile logic is
contained within code designed to eliminate the screen flashing ...
including the manipulation of Application.ScreenUpdating.



"Rich J" wrote in message
...
I didn't look thru the whole code you posted. I had that problem when the
application.screenupdating = False was in one of my subroutines. If the
subroutine is being called many times during execution then depending
where
... = True is you might be causing the flashing. I moved the line to the
main calling routine and the screen does one flash and the changes are
there.

"Josh Sale" wrote: