Referencing Procedure in Personal.xls

  • Thread starter Thread starter John Pierce
  • Start date Start date
J

John Pierce

I use sound files in VBA using a line like:
PlayWav ("C:\Sounds\bounce.way")
which requires access to a function and macro which follow:
---------------------------------------------------------------
Public Declare Function sndPlaySoundA Lib "winmm.dll" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
---------------------------------------------------------------
Public Sub PlayWav(WavFile As String)
'
sndPlaySoundA WavFile, 1
'
End Sub
----------------------------------------------------------------
Up to now I have put these in each file using wave files but I
should be able to have the function and macro in Personal.xls but
that isn't working for me because apparently the other workbooks
are not referencing Personal.xls. I thought that if Personal.xls
was open it was accessible to any other open workbook. What gives?
 
John,

It is not correct that code in personal.xls is automatically available to
all open workbooks. Personal.xls is nothing special, so to call code in it
from another workbook you must reference personal.xls from the second
workbook. As an alternative, you can use Application.Run to execute the
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip, thanks for the help. I figured out how to make a reference to my
Personal.xls. In case anyone else is struggling with this, it may be
necessary to change the Project Name of Personal.xls in its
Project Properties from 'VBA Project' to something else (I happen to
have chosen 'MacroBook') because there may be more than one 'VBA
Project'
showing up in the Available References list, possibly including any
open workbooks and Add-ins.
Speaking of Add-ins, I have a couple that load every time Excel
starts up but it takes Excel ages to start because it runs a virus
scan
on these Add-ins every time. I have Security level within Excel set to
low (not recommended, I know) and I have 'Trust all installed add-ins
and templates' checked so I don't see why the virus scan has to run
every time.
Any ideas?
 
Do you have a third party virus scanner like Norton. I believe that is a
setting of the virus scanning software, not Excel.
 
Back
Top