PlaySound issue with Windows 7 64 bit

MikeBillsFan

New Member
Joined
Jul 29, 2010
Messages
7
I have a popular fantasy football spreadsheet package which I've sold for years. One of the features (frills) in one of the files is the ability to play "trash talking" sound effects. Someone in your fantasy draft makes a pick you want to laugh at, click the appropriate sound file, and hit play. Not a necessary feature of course but a lot of people like it.

Anyway, I've had a couple customers tell me the file won't open at all - throws an error - on their system with Windows 7 64-bit. The error is coming from the countdown timer and sound module.

Since it isn't opening at all, I believe it is coming from the opening section of the code, as follows:

Code:
Option Explicit
Public RunWhen As Double
Public TimeStart As Double
Public blnFirst  As Boolean
Public Const cRunIntervalSeconds = 1    ' 1 second
Public Const cRunWhat = "The_Sub"
Public bookName As String
Public sTotalTime As Date
Private Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Const SND_MEMORY = &H4
Const SND_PURGE = &H40 'purge the sound
Since this is mostly related to playing the sounds, I've narrowed my search to that. At this point I'm at a loss though. Can someone help me get this running in all versions of Windows?

For those current Win7 64x users, I've taken out this module and now they are fine (but these features don't work). Obviously I'd like a solution that works across all platforms so I don't have to roll out different file versions.

Thanks in advance for any advice!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MikeBillsFan

New Member
Joined
Jul 29, 2010
Messages
7
Thanks Akihito.

I think it is Excel 2007 on Windows 7 64-bit that is the problem, but I'll double check with some of those people if it is in fact Excel 2010.

If I made that change would it be backward compatible for Excel and Windows?
 

MikeBillsFan

New Member
Joined
Jul 29, 2010
Messages
7
I think this is the fix, but I can't confirm for sure since I don't have Excel 2010 / Windows 7 64-bit.

Code:
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function PlaySound Lib "winmm.dll" _
      Alias "PlaySoundA" (ByVal lpszName As String, _
      ByVal hModule As LongPtr, ByVal dwFlags As LongPtr) As LongPtr
#Else
    Private Declare Function PlaySound Lib "winmm.dll" _
      Alias "PlaySoundA" (ByVal lpszName As String, _
      ByVal hModule As Long, ByVal dwFlags As Long) As Long
#End If
HOWEVER, since I am doing this in Excel 2003 or Excel 2007, and VBA 6.5, it still gives me a compile error on the first part of the IF statement where I've added "PtrSafe". I can save it as is, close and open. It still seems to work on those older setups, but rather odd/unnerving about the compile error in the revised code.
 

Akihito Yamashiro

Board Regular
Joined
Jun 2, 2010
Messages
60

ADVERTISEMENT

On my WinXP + Excel2003 , your code does not throw a Compile Error.
Since #VBA7 and #WIN64 are declared only on Excel 2010 ( unless you declare them by yourself),
the first part must be ignored its existence On Excel 2003.
 

MikeBillsFan

New Member
Joined
Jul 29, 2010
Messages
7
I should clarify. In Excel 2003 or 2007, when I edit that first part of the If statement the auto-debugger gives an alert: "Compile error: Expected Sub or Function" (after the Declare statement, highlighting "PtrSafe").

However, if I just save it as is, close and re-open, run the macro it seems to work fine.

I need to get this tested in Excel 2010 64-bit and then I'm going to hope/assume I'm covered through the wide range of versions of Excel I'm trying to support for my customers.
 

MikeBillsFan

New Member
Joined
Jul 29, 2010
Messages
7

ADVERTISEMENT

FYI, I had one customer with Excel 2010 try the file with the new code and he reported back it did work for him, thankfully.
 

Akihito Yamashiro

Board Regular
Joined
Jun 2, 2010
Messages
60
According to the Win32API_PtrSafe.txt ,
PlaySound should be as follows.

Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _ (ByVal lpszName As String, ByVal hModule As LongPtr, _
ByVal dwFlags As Long) As Long

The variable types of dwFlags and the return value
are little bit deiffrent from yours.

You can download Win32API_PtrSafe.txt from
http://www.microsoft.com/downloads/...a5-eef9-4baf-8dbc-63fbd2dd982b&displayLang=en

I wish this may help you.
 

paul_bennett89

New Member
Joined
May 15, 2010
Messages
1
The replies on this thread are for OFFICE 2010, aren't they.

What about EXCEL 2000, EXCEL 2003 or EXCEL 2007 on Windows 7 64-bit?

I use the winmm.dll and VB code to create sounds from EXCEL, I use this function:

Private Declare Function midiOutOpen Lib "winmm.dll" _
(lphMidiOut As Long, _
ByVal uDeviceID As Long, _
ByVal dwCallback As Long, _
ByVal dwInstance As Long, _
ByVal dwFlags As Long) As Long

On Windows XP, using EXCEL 2003 or 2007 it works perfectly. On Windows 7 64-bit, the calls to the winmm.dll are hopelessly slow, I am playing musical notes from EXCEL, so this is a frustrating problem, timing and speed is important.

Why is Windows 7 64-bit unable to make the calls fast enough? I may make 4 or 5 calls to winmm.dll in one second and only one note is
played, the others appear to be skipped, I don't hear them. Back on my XP machine, all notes are played at a blisteringly fast speed, just as I programmed them.

I have been looking for answer for months now, I don't give my spreadsheets to VISTA or WINDOWS 7 users anymore and have to tell them it will not work. Is this a 64-bit issue, a multi-threading issue, an EXCEL issue, a winmm.dll issue?

Any suggestions are greatly welcome,

Regards,

Paul
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
anybody found a fix for this yet? I'm just running in to the same scenario now. Office 2003 32bit on Win7 64bit. I can play chimes.wav, but some wav's cant be played for no apparent reason. I'm not getting any errors either..
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,959
Messages
5,514,401
Members
409,000
Latest member
Will_vanthek

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top