VBA Help file

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Is there any way to bring up the VBA help file without first starting
Excel?

I tried a few things last year, don't remember what I did except that
nothing worked.
 
Look for VBAXL10.chm in
C:\Program Files\Microsoft Office\OFFICE11\1033 (2003 example)
Click to expand...

Thanks Ron, but it's not the same file, at least under Office10 (Excel
2002). VBAXL10.chm (Microsoft Excel Visual Basic Reference) doesn't
have the good stuff that's in the Excel VBA Help file (Microsoft
Visual Basic Help), especially the stuff under the heading Visual
Basic Language Reference, which has Constants, Keywords, Functions,
Statements, etc.)

When using that Help file, I see << "C:\Program Files\Microsoft
Office\Office10\1033\msohelp.exe" /w00280d46 >> running in Process
Explorer, but putting that in a desktop shortcut doesn't work. I can
see Msohelp.exe running in Taskmanager processes, but that's all.

I'm about halfway thru John Walkenbach's "Excel 2002 Power Programming
with VBA", and it sure would be handy to have that Excel VBA Help file
instantly accessible with a desktop shortcut.
See also
http://www.rondebruin.nl/id.htm
Click to expand...

More good stuff, thanks!

Andy
 
Is there any way to bring up the VBA help file without first starting
Excel?

I tried a few things last year, don't remember what I did except that
nothing worked.
Click to expand...

Maybe you can use the following VBScript:

'helpfile.vbs

Option Explicit
Dim xlApp, wsh

Set wsh = WScript.CreateObject("WScript.Shell")
Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.WorkBooks.Add
xlApp.Visible = True
xlApp.UserControl = True
WScript.Sleep 100
wsh.SendKeys "%{F11}"
WScript.Sleep 100
wsh.SendKeys "%H"
WScript.Sleep 100
wsh.SendKeys "{F1}"

Set xlApp = Nothing


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Save it in notepad in as (say) vbahelp.vbs on your desktop. If it
doesn't work for you, adjust the sleep lengths. It opens up a copy of
excel while its at it, but that might be good if you are using it as a
learning aid since you can quickly write some test code if need be.

Hope that helps

-John Coleman
 
Basic Language Reference, which has Constants, Keywords, Functions,
What do you miss ?
There is a lot in this chm file
Click to expand...

The things under the Basic Language Reference heading (Constants,
Keywords, etc.) are not in VBAXL10.chm, but they are in the help file
that comes up when you are in the VBE editor. That's what I miss.

The VBSCRIP5.chm file has some good lists, but not as comprehensive nor
any of the Excel specific stuff.

I'll play with the VB script John Coleman posted.

(But the VBE help file has to be somewhere!)

Andy
 
Thanks John, that does work and at least gives me "one-click" access
to the help file.

How about a couple of lines that would minimize the Excel and VBE
windows?

Andy
 
Andy,

You *could* put the following lines just before you set xlApp =
Nothing:

WScript.Sleep 100
xlApp.WindowState = -4140 'xlMinimized
WScript.Sleep 100
xlApp.VBE.MainWindow.WindowState = 1

the only problem is that when the VBE window is minimized so are all
of its child windows. There might be some sort of workaround using
Window's API calls - but I wouldn't know how. Sorry I can't help you
out more there.

-John
 
WScript.Sleep 100
xlApp.WindowState = -4140 'xlMinimized
WScript.Sleep 100
xlApp.VBE.MainWindow.WindowState = 1

the only problem is that when the VBE window is minimized so are all
of its child windows. There might be some sort of workaround using
Window's API calls - but I wouldn't know how. Sorry I can't help you
out more there.
Click to expand...

This is plenty of help John, thanks.

I commented out the last 2 lines, and this works out perfect. I don't
need to see the main Excel window, but having the VBE window open is
actually handy.

I notice that Excel comes up without my personal.xls or any of my
usual addins. As long as Excel will be running anyway, at least
having personal.xls going also might be worthwhile. Is there an easy
way to do that?

Andy
 
Hi Andy
Evidently this is a known problem (http://support.microsoft.com/kb/
213489/en-us). Openning Personal.xls is easy enough - just use the
line
xlApp.WorkBooks.Open("Path/Personal.xls")
where you need to find what the path in your system is (use find
file).
The part about addins seems hard and seems like it can't be done in
the abstract in a satisfactory way. The code

For Each xlAddIn in xlApp.AddIns
If xlAddIn.Installed then
xlApp.WorkBooks.Open(xlAddIn.FullName)
End If
Next

*Almost* works - but fails to properly register functions and run some
AutoOpen routines.
I'll look at it later today.

-John
 
Andy,
On my system, it looks like it is:
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\VBUI6.CHM

Maybe the other Help files here would be useful also.

NickHK
 
If you want to run the Auto_Open macros, take a look at .runautomacros in VBA's
help.

Maybe something like:
dim wkbk as xlapp.workbook
set wkbk = xlapp.workbooks.open("pathto/personal.xls")
wkbk.runautomacros which:=1 'xlAutoOpen is excel's constant for 1
 
For Each xlAddIn in xlApp.AddIns
If xlAddIn.Installed then
xlApp.WorkBooks.Open(xlAddIn.FullName)
End If
Next
Click to expand...

This works fine for add-ins. I don't have AutoOpen routines in
personal.xls, and for this purpose it's not critical that all
functions are available (which as you said, all are not).

Although NickHK found the help file I'm looking for, I'm keeping a
shortcut to this VBS script because it's handy to be able to open the
VBE window and the Help screens all at once if I want to experiment.

The shortcut to Help is faster when that's all I want. Plenty of room
for two shortcuts.
 
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\VBUI6.CHM

That's it!!! Outstanding!
Maybe the other Help files here would be useful also.
Click to expand...

One in particular that is a sort of glossary.


Thanks Nick!


Andy
 
A possibly moot message I tried to post a couple of times yesterday
when Google groups was having server trouble:

Hi Andy,

I'm glad that you can still get some use out of the script. The
problem with the last version is that if you had the AnalysisToolkit
add-in installed, the script would load the add-in but in such a way
that some (all?) of the functionality is lost. For example - no
randbetween() function. The following version of the script (with the
path to personal modified of course) seems to handle this sort of
situation adequately:

'helpfile.vbs

Option Explicit
Dim xlApp, wsh, xlAddIn, wb
Dim personal, name

Set wsh = WScript.CreateObject("WScript.Shell")
Set xlApp = WScript.CreateObject("Excel.Application")
personal = "C:\Documents and Settings\jcoleman\Application Data"
personal = personal & "\Microsoft\Excel\XLSTART\Personal.xls"
xlApp.WorkBooks.Open(personal)
For Each xlAddIn in xlApp.AddIns
If xlAddIn.Installed then
name = xlAddIn.FullName
Set wb = xlApp.WorkBooks.Open(name)
If LCase(Mid(name,Len(name)-2)) = "xll" Then
xlApp.RegisterXLL name
End If
wb.RunAutoMacros 1 'xlAutoOpen
End If
Next
xlApp.WorkBooks.Add
xlApp.Visible = True
xlApp.UserControl = True
WScript.Sleep 100
wsh.SendKeys "%{F11}"
WScript.Sleep 100
wsh.SendKeys "%H"
WScript.Sleep 100
wsh.SendKeys "{F1}"
WScript.Sleep 100
xlApp.WindowState = -4140 'xlMinimized

Set xlApp = Nothing


The script could be modified to loop over all workbooks in the XLSTART
directory and open them - but in my and apparently your case this is
justpersonal.xls.

Hth

-John Coleman
 
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\VBUI6.CHM
Click to expand...

Nick,

Turns out this file doesn't have Excel particulars, just general vba
stuff even though at first blush it looks just like the Excel VBA help
file. Oh well, thanks for trying.

Andy
 
Andy,
Depends what you are after. Otherwise there's all the XLxxx.chm files for
Excel.

NickHK
 
Back
Top