[SOLVED] What wrong is this macro?

Question

When I click cell B4 to run following macro, it pops up following error.

The object public is unknown format or error on Declare object.

Does anyone have any suggestions on what wrong it is?

Thanks in advance for any suggestions :>

Option Explicit

Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _       (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long      

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("B4")) Is Nothing Then

    Call sndPlaySound32("D:\CT1.wav", 0)

  End If

End Sub

Answer 1

oem7110 wrote:

When I click cell B4 to run following macro, it pops up following error.

The object public is unknown format or error on Declare object.

[....]

Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _

**** (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long****Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("B4")) Is Nothing Then

    Call sndPlaySound32("D:\CT1.wav", 0)

  End If

End Sub

I get the following error:

For me, the fix is:

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _

**** (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Of course, your call assumes that ".wav" file exists in the root directory of device "D:".

That is certainly not the case on my system.  Only you can determine if that is the case on your system.

Answer 2

Just as a point for future reference, if there's a chance your workbook might be used on systems with 64-bit version of Office installed and you want to make sure it works properly on both the 32- and 64-bit Office platforms, then you need to make these kinds of Declare statements like this:

'see http://msdn.microsoft.com/en-us/library/ee691831(v=office.14).aspx

'for more info on VBA7 and WIN64 constant usage.

#If VBA7 Then

  'when 64-bit Office is used this will be compiled

  Private Declare PtrSafe Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _

          (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

#Else

  'when 32-bit Office is used, then this will be compiled

  Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _

          (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

#End If

The use of VBA7 and also Win64 (another internal constant) is covered in this article:

http://msdn.microsoft.com/en-us/library/ee691831(v=office.14).aspx

The # at the start of the #If, #Else and #End If tell the VBA compiler that these are compiler instructions rather than regular If-Then-Else statements.

Answer 3

Thanks, to everyone very much for suggestions :>

Answer 4

I find from other web site, and it requires Private within specific worksheet.

and the problem is solved.

Thank you very much for suggestions :>

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _

    "sndPlaySoundA" (ByVal lpszSoundName As String, _

    ByVal uFlags As Long) As Long

Answer 5

This sounds like a 64-bit vs 32-bit issue. You can find the code to select the correct version of the sndPlaySound32 functionality in this older thread.

Compile error: The code in this project must be updated for use on 64-bit systems...

Give Hans a Helpful if this resolves your issue.