Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel and VBA Environment questions

  Asked By: Lucina    Date: Oct 25    Category: MS Office    Views: 2577
  

Here are some Excel and VBA Environment questions needing some good answers.
(1)..how is that a really intensive graphical application, such as what you
probably have seen
of WallStreet (Excel) graphical chart software. As it updates its main chart,
which is on a
separate sheet( chart sheet ) - NOT a ( data spreadsheet ) with the embedded
chart.

(2)..I have been analyzing the Application.Screenupdating=True, which really
applies to the whole [ Excel ]
application environment. Which would mean other workbooks OPENed in the Excel
environment are effected by this.
I have gone through the WINAPI set of function calls in a 500-600 page book,
which I came across in Borders Bookstore
--WINAPI for VBA&VisualBasic(Published by Sams), with has very little
explainations or examples. And, I was trying to see if
there is a WINAPI function call or a set of them, that would simply update the
current main chart without any "flickering"
of it at all, PLUS also prevent the current displayed Userform overlaying images
of itself onto the current screen.

(3)...I did a simple test, just by doing Application.Screenupdating=True inside
of a command button
selection on a Userform. When this single statement is executed, it causes the
main chart to do a
"flicker" every single time, AND of a horizontal line which scrolls from top to
bottom; thus to do a
refresh of the whole [Excel] environment for updating things in its environment.
If I were to do a Application.Quit, this would cause all OPENed workbooks in the
[Excel] environment to close.
Same is applicable when doing Application.Screenupdating=True, asthis would
affect all OPENed workbooks to update.
Same is applicable when doing Application.Screenupdating=False, as this would
affect all OPENed workbooks to NOT update.

I would like simply to utilize something, whereby it doesn't update the whole
[Excel] environment, but
to simply update the current main chart of its data and to prevent the Userform
of its overlaying images.
The reason for this, is to "OPTIMIZE" the performance of the underlying VBA code
by doing
the Application.Screenupdating=False OR something else and possibly do what is
mentioned in Step#2 above.
Any ideas would greatly be appreciated.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Christie Bradley     Answered On: Oct 25

In addition, the reason why Application.Screenupdating=False helps "optimize"
the performance  of the underlying VBA code,
is because there are no refreshing activities being applied to the screen.
Another statement  in Excel's environment  to help
"optimize" the execution of the underlying code is
Application.Calculation=False, however it is VERY difficult in knowing
when & where to precisely place this statement, because in NOT knowing exactly
when and which specific Excel formulas
in a spreadsheet  ARE being executed at the precise moment within an underlying
VBA subroutine is being executed...

 
Answer #2    Answered By: Wendy Harrison     Answered On: Oct 25

Also think of it this way, when doing ( Application.Screenupdating=False ),
it does several things, it turns off everything in [Excel's] environment  to
"optimize"
the performance  execution of the underlying VBA code.
PLUS, it also delays any updates  to the screen; thus the "flickering/repainting"
and of any refreshing activities in [Excel's] environment. The same is true
for (Application.Screenupdating=True), as it does the reverse of the above.

My thinking is, anything with Application.( ? ) with only one level reference to
[Excel's] environment.
Just like ( Application.Quit ), which is one level reference and it affects all
of [Excel's] environment.

Examples of a specific events that does NOT affect the whole Excel environment,
are Worksheets("Sheet1").Activate & Worksheets("Chart1").Activate.

 
Answer #3    Answered By: Noah Evans     Answered On: Oct 25

Shown below are the two WINAPI calls  when the
Application.Screenupdating=True is issued within any VBA application.

 
Answer #4    Answered By: Candace Foster     Answered On: Oct 25

Again, don't know if this ia applicable but have you tried a
DoEvents?

 
Answer #5    Answered By: Jo Fowler     Answered On: Oct 25

Please explain further with a sample, and
what it( DoEvents ) might accomplished.

 
Answer #6    Answered By: Blaze Fischer     Answered On: Oct 25

You seem to be much more accomplished but
I'll give it a go.

DoEvents is a keyword that lets the processing catch up with the code so to
speak.

For example, since we were talking about the VBE... I tried a while back to
build a comprehensive Cross reference for VBE code. I went through all the
code, picked up all the variables/function names/sub names and so on and
then went through the code again to collect references to that stuff. I
collected line  numbers where referenced and defined, module names and
procedure names where referenced, counted the number of times referenced and
so on.

While all this was going on... I've always been of the opinion that you
should give the user something  to see for her money... so I updated labels
in a userform  that reflected what was going on... Looking at this procedure
out of total ... counted so many variables so far this procedure.. this
module..this project etc.

I found that the labels weren't being updated because the code was executing
so fast the label didn't have time  to update  properly... it was just whited
out... Until the end.

I fixed by putting a DoEventsstatement after each of the label updates. It
slowed the process down but you could see what was going on because the
application waited until it's command  stack was empty before continuing.

Hmmmmm... one day I'll go back to that app. I stopped because I found
someone had done something similar... not so comprehensive but it did
produce a cross reference... with *much* less complex code. He'd taken about
2 weeks and I'd taken 5 months over it till then!!! So I thought buggerit.
Ah Well!

But for *intermittent* problems.. I usually look at timing probs first and
spatter my code with DoEvents deleting them judiciously till I get the
culprit. That's worked well when it's worked.. but it's not applicable to
lots of stuff and as I said... maybe not for you.

It's just a statement... It's in the help even... but if you still want an
example I'll send a bit of code from that application.

I just think *full* cross references are soooo valuable. You can see where
things aren't referenced and so on. I went on to start deleting variables
that weren't referenced, sorting Dim statements in procs and all kinda good
stuff... like going through code and deleting stuff commented as debug etc
etc. I got zapped though when I started deleting code from the same module I
was running. Which leads me to suspect that VBA looks down the code evertime
for the next line tag rather than following through.

 
Answer #7    Answered By: Pam Harrison     Answered On: Oct 25

And here is a set  of procedures to place in a module which could be named as (
mEnvironment ).
It also allows you to protect the environment  of your workbook, from accessing
the sensitive data  in it.
By calling the respective procedures in the proper places within your workbook.

Option Explicit
'===============================================================================\
================================
'-Environment Settings EndUser set initially.
'===============================================================================\
================================
Public StdCMDBar As Integer, FrmCMDBar As Integer
Public DspSTABAR As Integer, DspWINTBar As Integer, DspFRMLBar As Integer
'//=====================================================================
'// Run DisableGettingIntoVBE from an Event procedure
'// eg. Workbook_Open or Worksheet_Activate
'// THEN run EnableGettingIntoVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog, View Code - available via RighClick on sheet  Tab
'// Record New Macro..., Design Mode as the User can put the
'// workbook in design mode then select a control & double click to
'// view code, right click top  Document Area etc
'// Also you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.
'// The following Routine Takes care of this.
Const dCustomize As Double = 797
Const dVbEditor As Double = 1695
Const dMacros As Double = 186
Const dRecordNewMacro As Double = 184
Const dViewCode As Double = 1561
Const dDesignMode As Double = 1605
Const dAssignMacro As Double = 859

'===============================================================================\
================================
Sub M_ENVRN(Restore As Integer)
If Restore = 1 Then
'====================================
'Reset previous Environment Settings
'====================================
On Error Resume Next
Application.CommandBars("Standard").Visible = StdCMDBar
Application.CommandBars("Formatting").Visible = FrmCMDBar
Application.DisplayStatusBar = DspSTABAR
Application.DisplayFormulaBar = DspFRMLBar
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True
'======================================
'-Find out what Version MSOffice
'-is installed.
'======================================
If Int(Val(Application.Version)) > 8 Then
'Excel 2000 and the later version
Application.ShowWindowsInTaskbar = DspWINTBar
Else
'Excel 97
'==============================================
'-Not valid for Office97
'Application.ShowWindowsInTaskbar = DspWINTBar
'==============================================
End If
Err.Clear
'==========================================
'-This setting is only when to not show
'-other new workbooks  to be displayed
'-within the application, to not be
'-displayed on the taskbar.
'(Application.ShowWindowsInTaskbar = False)
'(Application.ShowWindowsInTaskbar = True)
'==========================================
Else
On Error Resume Next
'====================================
'Save current  Environment Settings
'====================================
StdCMDBar = Application.CommandBars("Standard").Visible
FrmCMDBar = Application.CommandBars("Formatting").Visible
DspSTABAR = Application.DisplayStatusBar
'===============================================
'-Not valid for Office97
'DspWINTBar = Application.ShowWindowsInTaskbar
'===============================================
DspFRMLBar = Application.DisplayFormulaBar
'======================================
'-Find out what Version MSOffice
'-is installed.
'======================================
If Int(Val(Application.Version)) > 8 Then
'Excel 2000 and the later version
DspWINTBar = Application.ShowWindowsInTaskbar
Else
'Excel 97
'==============================================
'-Not valid for Office97
'DspWINTBar = Application.ShowWindowsInTaskbar
'==============================================
End If
Err.Clear
'==========================================
'-This setting is only when to not show
'-other new workbooks to be displayed
'-within the application, to not be
'-displayed on the taskbar.
'(Application.ShowWindowsInTaskbar = False)
'(Application.ShowWindowsInTaskbar = True)
'==========================================
End If
End Sub

Function M_CmdBarReset(MainSheet As String, Reset As Integer, SheetName As
String)
On Error Resume Next
If SheetName <> MainSheet Then ActiveWorkbook.Sheets(SheetName).Visible =
False
Sheets(SheetName).Activate
If Reset = 0 Then
Application.CommandBars("Chart Menu Bar").Reset
Application.CommandBars("Chart Menu Bar").Visible = True
Application.CommandBars("Chart Menu Bar").Enabled = True
Application.CommandBars("Chart Menu Bar").Controls("Tools").Enabled =
True
Application.CommandBars("Chart").Visible = False
Application.ActiveChart.ProtectSelection = False
Application.ActiveChart.ProtectFormatting = False
Application.ActiveChart.ProtectData = False
Application.ActiveChart.Unprotect
Application.CommandBars.ActiveMenuBar.Reset
Application.CommandBars.ActiveMenuBar.Visible = True
Application.CommandBars.ActiveMenuBar.Enabled = True
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True
Application.CommandBars("Chart Menu Bar").Protection = msoBarNoCustomize
Else
Application.CommandBars("Chart Menu Bar").Reset
Application.CommandBars("Chart Menu Bar").Visible = False
Application.CommandBars("Chart Menu Bar").Enabled = False
Application.CommandBars("Chart Menu Bar").Controls("Tools").Enabled =
False
Application.CommandBars("Chart").Visible = False
Application.ActiveChart.ProtectSelection = True
Application.ActiveChart.ProtectFormatting = True
Application.ActiveChart.ProtectData = True
Application.ActiveChart.Protect
Application.CommandBars.ActiveMenuBar.Reset
Application.CommandBars.ActiveMenuBar.Visible = False
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Visible = False
Application.CommandBars("Chart Menu Bar").Protection = msoBarNoCustomize
Application.CommandBars("Toolbar List").Enabled = False
End If
Err.Clear
End Function

Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
10 'MsgBox "Sorry this command  is NOT available", vbCritical
End Sub

Sub DisableGettingIntoVBE()
Application.VBE.MainWindow.Visible = False '// Close ALL VBE Windows 1st!
CmdControl dCustomize, False '// Customize
CmdControl dVbEditor, False '// &Visual Basic Editor
CmdControl dMacros, False '// Macros...
CmdControl dRecordNewMacro, False '// Record New Macro...
CmdControl dViewCode, False '// View Code
CmdControl dDesignMode, False '// Design Mode
CmdControl dAssignMacro, False '// Assig&n Macro...
Application.OnDoubleClick = "Dummy"
Application.CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
End Sub

Sub EnableGettingIntoVBE()
CmdControl dCustomize, True '// Customize
CmdControl dVbEditor, True '// &Visual Basic Editor
CmdControl dMacros, True '// Macros...
CmdControl dRecordNewMacro, True '// Record New Macro...
CmdControl dViewCode, True '// View Code
CmdControl dDesignMode, True '// Design Mode
CmdControl dAssignMacro, True '// Assig&n Macro...
Application.OnDoubleClick = vbNullString
Application.CommandBars("ToolBar List").Enabled = True
Application.OnKey "%{F11}"
End Sub

'===============================================================================\
=================================
'-The following code is useful if you don't want a user to be able to edit the
'-Excel's main  menu/commandbar by right clicking on it.
'===============================================================================\
=================================
'Purpose : Enables or disables Excel's main menu commandbar
'Inputs : [bEnable] If True enables the Excel's right click
menu, else disables it
'Outputs : N/A
'Notes :
'===============================================================================\
================================================
Function CommandBarRightClick(Optional bEnable As Boolean = False) As Boolean
On Error GoTo ErrFailed
CommandBars("toolbar List").Enabled = bEnable
CommandBarRightClick = True
Exit Function
ErrFailed:
Debug.Print "Error in CommandBarRightClick: " & Err.Description
CommandBarRightClick = False
End Function

 
Didn't find what you were looking for? Find more on Excel and VBA Environment questions Or get search suggestion and latest updates.




Tagged: