CODEWITHSUNDEEP
excelvbafunctioncollectionsplaysound
Shane
Shane

Reputation: 23

Why is my VBA code giving me a "variable not defined" error?

I made a similar post a few days ago and got some good advice/help, but my code still seems to not be functioning properly. For reference, I am running 64 bit Excel and all of my code is contained within the same module.

I am trying to create a function that will read the value of a cell and play a specific WAV file associated with that value. For example, if the function reads "1", it will play "Amaj.wav", if it plays "2", it will play "Amin.wav", so on and so forth. All of my WAV files are stored in the same directory as my workbook. Here is my current code (taken from the answers on my previous post):

Option Explicit

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

Sub ChordDictionary()
    Set ChordDict = Nothing
    ChordDict.Add "Amaj.wav", "1"
    ChordDict.Add "Amin.wav", "2"
    ChordDict.Add "Aaug.wav", "3"
    ChordDict.Add "Adim.wav", "4"
    ChordDict.Add "A#maj.wav", "5"
    ChordDict.Add "A#min.wav", "6"
    ChordDict.Add "A#aug.wav", "7"
    ChordDict.Add "A#dim.wav", "8"
    ChordDict.Add "Bmaj.wav", "9"

    Sound 3 'you call here the function, for the third collection element
End Sub

Function Sound(Cell As Long)
    Dim WAVFile As String, SoundFile As String
    Const SND_ASYNC = &H1
    Const SND_FILENAME = &H200000

    For i = 1 To ChordDict.Count
        If i = Cell Then
            SoundFile = ChordDict(i)

For some reason, whenever I try to run the "Sound()" function within my Excel workbook, I get a "compile error: variable not defined" message. The line that is highlighted is the "Function Sound(Cell As Long)." Does anyone have any advice for how I can get my code to function as intended? Thanks.

Upvotes: 0

Views: 949

Answers (1)

Storax
Storax

Reputation: 12167

Fixed function 

Function Sound(Cell As Long)
    Dim WAVFile As String, SoundFile As String, i as long
    Const SND_ASYNC = &H1
    Const SND_FILENAME = &H200000

    For i = 1 To ChordDict.count
        If i = Cell Then
            SoundFile = ChordDict(i)
            WAVFile = ThisWorkbook.path & "\" & SoundFile
            Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
            Exit Function
        End If
    Next
End Function

Upvotes: 1

Related Questions