VBA运行报错‘Compile error: Sub or Function not defined’求助

Hey there, no worries—let's get this sorted out for your homework. The error you're seeing is caused by two main issues in the provided code: a typo and missing Windows API declarations. Here's how to fix it step by step:

1. The Root Cause of the Error #

Your code calls QueryPerformanceFreQuency (note the capital U in "FreQuency")—this is a spelling mistake. The correct Windows API function name is QueryPerformanceFrequency (ends with an E). Additionally, VBA doesn't recognize these low-performance timer functions by default, so we need to declare them first.

2. Full Corrected Code #

Replace your entire existing code with this version—all fixes are included:

' Declare Windows API functions needed for performance timing
#If VBA7 Then
    Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
    Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If

Function FFTRec(N As Integer, theta As Double, ar() As Double, ai() As Double, tmpr() As Double, tmpi() As Double)
    Dim nh As Integer, j As Integer
    Dim xr As Double, xi As Double, wr As Double, wi As Double
    Dim tmp2r(512) As Double, tmp2i(512) As Double
    
    If N > 1 Then
        nh = N / 2
        For j = 0 To nh - 1
            tmpr(j) = ar(j) + ar(nh + j)
            tmpi(j) = ai(j) + ai(nh + j)
            
            xr = ar(j) - ar(nh + j)
            xi = ai(j) - ai(nh + j)
            
            wr = Cos(theta * j)
            wi = Sin(theta * j)
            
            tmp2r(j) = xr * wr - xi * wi
            tmp2i(j) = xi * wr + xr * wi
        Next j
        
        Call FFTRec(nh, 2 * theta, tmpr, tmpi, ar, ai)
        Call FFTRec(nh, 2 * theta, tmp2r, tmp2i, ar, ai)
        
        For j = 0 To nh - 1
            ar(2 * j) = tmpr(j)
            ai(2 * j) = tmpi(j)
            ar(2 * j + 1) = tmp2r(j)
            ai(2 * j + 1) = tmp2i(j)
        Next j
    End If
End Function

Public Sub FFT()
    Dim xr(512) As Double, xi(512) As Double, tmpr(512) As Double, tmpi(512) As Double
    Dim pi As Double, wm As Double, theta As Double
    Dim i As Integer, Tr As Integer, N As Integer
    Dim curStartTime As Currency, curEndTime As Currency, curFreq As Currency
    
    i = 0: N = 512: Tr = 6
    pi = WorksheetFunction.Pi
    theta = 2 * pi / N
    
    For i = 1 To N
        xr(i - 1) = Cells(i + Tr - 1, 2): xi(i - 1) = 0
    Next i
    
    Call QueryPerformanceFrequency(curFreq)
    Call QueryPerformanceCounter(curStartTime)
    Call FFTRec(N, theta, xr, xi, tmpr, tmpi)
    Call QueryPerformanceCounter(curEndTime)
    
    Cells(1, 9) = "Processing Time " & CStr((curEndTime - curStartTime) / curFreq) & " Second"
    Cells(Tr - 1, 9) = "xr(i)_FFT": Cells(Tr - 1, 10) = "xi(i)_FFT": Cells(Tr - 1, 11) = "P_FFT"
    
    For i = 0 To N - 1
        Cells(i + Tr, 9) = xr(i)
        Cells(i + Tr, 10) = xi(i)
        Cells(i + Tr, 11) = Sqr(xr(i) ^ 2 + xi(i) ^ 2)
    Next i
End Sub

3. Key Changes Made #

• Added Windows API declarations at the top: This tells VBA what QueryPerformanceCounter and QueryPerformanceFrequency are, so it can find and execute them.
• Fixed the typo: Changed QueryPerformanceFreQuency to the correct QueryPerformanceFrequency.
• Explicitly declared all variables: Added As Double/As Integer to variables like xr, xi to avoid VBA's default implicit variant type (which can cause unexpected issues).
• Updated WorksheetFunction.pi to WorksheetFunction.Pi (capitalization isn't strictly required in VBA, but it's consistent with best practices).

4. How to Apply the Fix #

1. Open your Excel file and press Alt + F11 to open the VBA Editor.
2. Locate the module where your original code was stored (it's probably named something like Module1).
3. Delete all the old code in the module.
4. Paste the corrected code above into the module.
5. Press F5 to run the FFT subroutine, or go back to Excel and run it from the Developer tab.

That should resolve the compile error and let your FFT code run properly to process your experimental data.

内容的提问来源于stack exchange，提问作者benkyoua