Share via

ERROR VBA EXCEL FOR MACOS

Anonymous
2025-01-20T11:08:32+00:00

Hi everyone

I am a newbie learning to write VBA code for excel. I write on Windows everything works fine until I transfer the file to MAC and it shows 2 errors

  1. An unexpected error has occurred. AutoRecover has been disabled for this session of Excel
  2. Invalid procedure call or argument.

Can anyone help me? Thanks everyone

Microsoft 365 and Office | Excel | For education | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim G 134K Reputation points MVP Volunteer Moderator
    2025-01-22T17:04:35+00:00

    To troubleshoot we need to see a copy of the problem workbook. Please sanitize it before sharing a copy.

    Why a sample file is important for troubleshooting - - - - - - - and how to do it.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-01-21T02:04:12+00:00

    So how should I fix it now? ...

    Was this answer helpful?

    0 comments
  3. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2025-01-20T12:52:28+00:00

    I would need to see the workbook I think.

    That MacrosExist function seems like a bad idea though. Running code to see if it exists then running it again seems weird.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2025-01-20T11:39:02+00:00

    I also don't know which line is faulty, because the macos system doesn't notify me.

    Below is my VBA code

    Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

    On Error Resume Next ' B? qua l?i đ? tránh s? c? 

Select Case Sh.Name 

    Case "Congno" 

        If MacrosExist("TinhCongno") Then Call TinhCongno 

    Case "Thang" 

        If MacrosExist("Theothang") Then Call Theothang 

    Case "Trangthaihoadon" 

        If MacrosExist("Hoadon") Then Call Hoadon 

    Case "Dashboard" 

        If MacrosExist("Top5kh") Then Call Top5kh 

End Select 

On Error GoTo 0 ' Khôi ph?c x? l? l?i m?c đ?nh

    End Sub

    Function MacrosExist(ByVal MacroName As String) As Boolean 

    Dim Test As Object 

On Error Resume Next 

Set Test = Application.Run(MacroName) 

MacrosExist = (Err.Number = 0) 

On Error GoTo 0

    End Function

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsData As Worksheet

Dim rowNum As Variant

Dim result As Variant

' Xac dinh vung du lieu

On Error Resume Next

Set wsData = ThisWorkbook.Sheets("Data") ' Sheet

On Error GoTo 0

' Khai bao sheet Data

If wsData Is Nothing Then

    Exit Sub

End If

' O J12 phan vung du lieu

If Not Intersect(Target, Me.Range("J12")) Is Nothing Then

    If Application.OperatingSystem Like "\*Mac\*" Then Exit Sub ' Tránh l?i trên Mac

    Application.EnableEvents = False ' Ngan viec lap lai

    If Target.Value <> "" Then

        ' Tim ma hoa don phu hop voi sheet "Data"

        On Error Resume Next

        rowNum = WorksheetFunction.Match(Target.Value, wsData.Range("C2:C100"), 0)

        On Error GoTo 0

        ' Neu co ma hoa don

        If Not IsError(rowNum) Then

            ' Tim ngay o cot E phu hop voi ma hoa don

            result = wsData.Cells(rowNum + 1, 5).Value ' Cot E

            ' Neu có thay

            If IsDate(result) Then

                Me.Range("J13").Value = result

            Else

                Me.Range("J13").ClearContents ' Neu khong tim thay

            End If

        Else

            Me.Range("J13").ClearContents ' Neu khong thay

        End If

    Else

        ' Neu hoa don bi xoa

        Me.Range("J13").ClearContents

    End If

    Application.EnableEvents = True ' Bat lai

End If

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet

Dim j2Value As String, j3Value As String

Dim dataRange As Range

Dim lastRow As Long

Dim i As Long

Dim dataB As Variant, dataC As Variant, dataH As Variant

Dim output() As Variant

Dim updateRequired As Boolean

' Khai sheet "Lichsu"

Set ws = ThisWorkbook.Sheets("Lichsu")

' Kiem tra thay doi cua o J2 va J3

If Not Intersect(Target, ws.Range("J2:J3")) Is Nothing Then

    Application.EnableEvents = False ' Tat sk

    Application.ScreenUpdating = False ' Tat cap nhat man hinh

    ' Set vung J2 va J3

    j2Value = Trim(CStr(ws.Range("J2").Value))

    j3Value = Trim(CStr(ws.Range("J3").Value))

    ' Find last row by checking column B for the last used row

    lastRow = 7 ' Default to 7 if no data

    On Error Resume Next ' Ignore any error if range is empty

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    On Error GoTo 0 ' Reset error handling

    ' Thoat chuoi n?u d? li?u không đ?

    If lastRow < 7 Then GoTo ExitHandler

    ' Load data from columns B, C, and H into arrays for faster processing

    dataB = ws.Range("B7:B" & lastRow).Value

    dataC = ws.Range("C7:C" & lastRow).Value

    dataH = ws.Range("H7:H" & lastRow).Value

    ' Initialize the output array

    ReDim output(1 To UBound(dataB, 1), 1 To 1)

    updateRequired = False

    ' Loop through the data and check conditions

    For i = 1 To UBound(dataB, 1)

        If Not IsEmpty(dataB(i, 1)) And Not IsEmpty(dataC(i, 1)) Then

            If Trim(CStr(dataB(i, 1))) = j2Value And Trim(CStr(dataC(i, 1))) = j3Value Then

                ' Update column H to "Xem" if the value is not already "Xem"

                If dataH(i, 1) <> "Xem" Then

                    output(i, 1) = "Xem"

                    updateRequired = True

                Else

                    output(i, 1) = dataH(i, 1)

                End If

            Else

                ' Clear the value in column H if it's not empty

                If dataH(i, 1) <> "" Then

                    output(i, 1) = ""

                    updateRequired = True

                Else

                    output(i, 1) = dataH(i, 1)

                End If

            End If

        Else

            output(i, 1) = dataH(i, 1)

        End If

    Next i

    ' Write changes to the worksheet if necessary

    If updateRequired Then

        If lastRow >= 7 Then

            ws.Range("H7:H" & lastRow).Value = output

        End If

    End If

    ExitHandler:

        ' Mo lai cai dat

    Application.ScreenUpdating = True

    Application.EnableEvents = True

End If

    End Sub

    Sub TinhCongno()

    Dim lr As Long

Dim ws As Worksheet

Dim rngData As Range

Dim formulaC As String, formulaD As String, formulaE As String, formulaF As String

Dim cell As Range

' Set sheet

Set ws = ThisWorkbook.ActiveSheet

' T?t tính toán t? đ?ng và c?p nh?t màn h?nh

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.EnableEvents = False

' Tim dong cuoi trong cot B

lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

If lr < 7 Then GoTo CleanExit

' Define formulas

formulaC = "=SUMIFS(STno,TKH,B7,Ngay,""<""&$F$2)-SUMIFS(STtra,TKH,B7,Ngay,""<""&$F$2)"

formulaD = "=SUMIFS(STno,TKH,B7,Ngay,"">=""&$F$2,Ngay,""<=""&$F$3)"

formulaE = "=SUMIFS(STtra,TKH,B7,Ngay,"">=""&$F$2,Ngay,""<=""&$F$3)"

formulaF = "=C7+D7-E7"

' Chèn công th?c vào m?ng

On Error Resume Next

ws.Range("C7:C" & lr).Formula = formulaC

ws.Range("D7:D" & lr).Formula = formulaD

ws.Range("E7:E" & lr).Formula = formulaE

ws.Range("F7:F" & lr).Formula = formulaF

On Error GoTo 0

' Ghi k?t qu? (chuy?n công th?c thành giá tr?)

Set rngData = ws.Range("C7:F" & lr)

rngData.Value = rngData.Value

' Xóa các ô có k?t qu? b?ng 0

For Each cell In rngData

    If cell.Value = 0 Then

        cell.ClearContents

    End If

Next cell

    CleanExit:

    ' B?t l?i tính toán và c?p nh?t màn h?nh

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.EnableEvents = True

    End Sub

    Sub Ghicongno()

    ' Turn off unnecessary features to optimize performance

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

On Error GoTo CleanExit ' Ensure that we reset settings even if an error occurs

' Check if the worksheets exist and are properly referenced

Dim wsTrangTinh2 As Worksheet, wsTrangTinh3 As Worksheet, wsTrangTinh1 As Worksheet

On Error Resume Next

Set wsTrangTinh2 = ThisWorkbook.Worksheets("Ghicongno")

Set wsTrangTinh3 = ThisWorkbook.Worksheets("Lichsu")

Set wsTrangTinh1 = ThisWorkbook.Worksheets("Trangthaihoadon")

On Error GoTo 0

If wsTrangTinh2 Is Nothing Or wsTrangTinh3 Is Nothing Or wsTrangTinh1 Is Nothing Then

    MsgBox "ERROR: M?t ho?c nhi?u trang tính không t?n t?i!", vbCritical

    GoTo CleanExit

End If

' Check input data

With wsTrangTinh2

    If .Range("J10") = "" Then ' Date

        MsgBox "ERROR: Ô J10 không đư?c đ? tr?ng!", vbCritical

        .Range("J10").Select

        GoTo CleanExit

    End If

    If .Range("J11") = "" Then ' Invoice Number

        MsgBox "ERROR: Ô J11 không đư?c đ? tr?ng!", vbCritical

        .Range("J11").Select

        GoTo CleanExit

    End If

    If .Range("J12") = "" Then ' Customer Name

        MsgBox "ERROR: Ô J12 không đư?c đ? tr?ng!", vbCritical

        .Range("J12").Select

        GoTo CleanExit

    End If

End With

Dim lr As Long

' Write data to Trang\_tính3

With wsTrangTinh3

    lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    .Range("A" & lr).Value = wsTrangTinh2.Range("J10").Value

    .Range("B" & lr).Value = wsTrangTinh2.Range("J11").Value

    .Range("C" & lr).Value = wsTrangTinh2.Range("J12").Value

    .Range("D" & lr).Value = wsTrangTinh2.Range("J14").Value

    .Range("E" & lr).Value = wsTrangTinh2.Range("J15").Value

    .Range("F" & lr).Value = wsTrangTinh2.Range("J16").Value

    .Range("G" & lr).Value = wsTrangTinh2.Range("J17").Value

End With

' Write to Trangthaihoadon

With wsTrangTinh1

    If wsTrangTinh2.Range("N1").Value = 1 Then

        ' Call the Xoatrang function if N1 equals 1

        On Error Resume Next

        Xoatrang

        If Err.Number <> 0 Then

            MsgBox "ERROR: L?i khi g?i hàm Xoatrang", vbCritical

        End If

        On Error GoTo 0

        GoTo CleanExit ' Already done

    End If

    ' If not already done

    lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    .Range("A" & lr).Value = wsTrangTinh2.Range("J11").Value ' Invoice Number

    .Range("B" & lr).Value = wsTrangTinh2.Range("J12").Value ' Customer Name

    .Range("D" & lr).Value = wsTrangTinh2.Range("J13").Value ' Payment Date

End With

    CleanExit:

    ' Reset the application state and clean up

On Error Resume Next

Xoatrang

On Error GoTo 0

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

    End Sub

    Sub Xoatrang()

    ' Khai báo bi?n đúng ki?u

Dim Ghicongno As Worksheet

Dim wsData As Worksheet

' Ki?m tra n?u sheet "Ghicongno" và "Data" t?n t?i

On Error Resume Next

Set Ghicongno = ThisWorkbook.Sheets("Ghicongno")

Set wsData = ThisWorkbook.Sheets("Data")

On Error GoTo 0

' N?u m?t trong các sheet không t?n t?i, thoát hàm

If Ghicongno Is Nothing Or wsData Is Nothing Then

    MsgBox "M?t ho?c nhi?u trang tính không t?n t?i!", vbCritical

    Exit Sub

End If

' Clear contents in the specified range on Ghicongno

With Ghicongno

    .Range("J10:J17").ClearContents

    .Range("J10").Formula = "=NOW()"  ' Use NOW() function in J10

    .Range("O10").Formula = "=Data!A2" ' Reference cell A2 from Data sheet

End With

    End Sub

    Sub Theothang()

    Dim wsThang As Worksheet

Dim wsLichsu As Worksheet

Dim lrThang As Long, lrLichsu As Long

Dim dataLichsu As Variant

Dim result As Variant

Dim i As Long, j As Long

Dim Thang As Long

Dim idThang As Variant

Dim tongThang(1 To 12) As Double

Dim namCondition As Long

' Set sheet

Set wsThang = ThisWorkbook.Sheets("Thang")

Set wsLichsu = ThisWorkbook.Sheets("Lichsu")

' Set nam

namCondition = wsThang.Range("N3").Value

' Find the last row

lrThang = wsThang.Range("A" & wsThang.Rows.Count).End(xlUp).Row

lrLichsu = wsLichsu.Range("A" & wsLichsu.Rows.Count).End(xlUp).Row

' Exit if data is insufficient

If lrThang < 7 Or lrLichsu < 7 Then Exit Sub

' Load historical data into an array for faster access

dataLichsu = wsLichsu.Range("A7:F" & lrLichsu).Value

' Initialize the result array to store monthly data

ReDim result(1 To lrThang - 6, 1 To 13)

' Loop through each customer (Thang)

For i = 7 To lrThang

    idThang = wsThang.Range("A" & i).Value

    ' Reset monthly totals

    For Thang = 1 To 12

        tongThang(Thang) = 0

    Next Thang

    ' Loop through the historical data to find matching records

    For j = LBound(dataLichsu, 1) To UBound(dataLichsu, 1)

        ' Check if date is valid before using Year and Month

        If IsDate(dataLichsu(j, 1)) And dataLichsu(j, 2) = idThang And Year(dataLichsu(j, 1)) = namCondition Then

            Thang = Month(dataLichsu(j, 1))

            tongThang(Thang) = tongThang(Thang) + dataLichsu(j, 5) - dataLichsu(j, 6)

        End If

    Next j

    ' Store the monthly results and calculate the yearly total

    For Thang = 1 To 12

        If tongThang(Thang) = 0 Then

            result(i - 6, Thang) = "" ' If the total for the month is 0, leave it blank

        Else

            result(i - 6, Thang) = tongThang(Thang)

        End If

    Next Thang

    ' Calculate and store the yearly total

    If Application.Sum(tongThang) = 0 Then

        result(i - 6, 13) = "" ' If the yearly total is 0, leave it blank

    Else

        result(i - 6, 13) = Application.Sum(tongThang)

    End If

Next i

' Write results back to the Thang sheet

wsThang.Range("B7").Resize(lrThang - 6, 13).Value = result

    End Sub

    Sub Hoadon()

    Dim ws As Worksheet

Dim lr As Long

Dim rngData As Range

Dim rngFormula As Range

Dim rngZero As Range

' Set the active sheet

Set ws = ThisWorkbook.ActiveSheet

' Find the last row in column A

lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

If lr < 5 Then Exit Sub

' Turn off unnecessary updates to speed up the code

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

' Define the range for applying the formulas

Set rngFormula = ws.Range("C8:C" & lr)

' Insert formulas into column C

ws.Range("C8:C" & lr).FormulaR1C1 = \_

    "=IF(RC[-2]<>"""", IFERROR(SUMIFS(Lichsu!C5, Lichsu!C2, RC[-2], Lichsu!C3, RC[-1]) - SUMIFS(Lichsu!C6, Lichsu!C2, RC[-2], Lichsu!C3, RC[-1]), 0), """")"

' Update column E based on conditions ("S?p đ?n h?n")

ws.Range("E8:E" & lr).FormulaR1C1 = \_

    "=IF(RC[-1]<>"""", IF(RC[-2]>0, IF(RC[-1]0, ""Chua hoan thanh"", ""Da hoan thanh""))"

' Convert formulas to values in column C (so that formulas are replaced by their results)

ws.Range("C8:C" & lr).Value = ws.Range("C8:C" & lr).Value

' Attempt to remove cells with value 0 in column C

On Error Resume Next

Set rngZero = ws.Range("C8:C" & lr).SpecialCells(xlCellTypeConstants, xlNumbers)

On Error GoTo 0 ' Reset error handling

If Not rngZero Is Nothing Then

    rngZero.Replace What:=0, Replacement:="", LookAt:=xlWhole

End If

' Re-enable screen updates and calculation

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

    End Sub

    Sub Top5kh()

    Dim wsSource As Worksheet, wsTarget As Worksheet

Dim lastRow As Long, dataArray As Variant, i As Long, totalDebt As Double

' Define source and target sheets

Set wsSource = ThisWorkbook.Worksheets("Congno")

Set wsTarget = ThisWorkbook.Worksheets("Data")

' Find last row in column B of the source sheet

lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

If lastRow < 7 Then Exit Sub ' No data available

' Get data from B7:F

dataArray = wsSource.Range("B7:F" & lastRow).Value

' Disable screen updates and calculation to speed up the process

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

' Sort data based on column 5 (current debt)

With wsSource.Sort

    .SortFields.Clear

    .SortFields.Add Key:=wsSource.Range("F7:F" & lastRow), Order:=xlDescending

    .SetRange wsSource.Range("B7:F" & lastRow)

    .Header = xlNo

    .Apply

End With

' Calculate total debt for top 5 customers and write to the target sheet

totalDebt = 0

For i = 1 To WorksheetFunction.Min(5, lastRow - 6)

    If Not IsEmpty(dataArray(i, 1)) And Not IsEmpty(dataArray(i, 5)) Then

        wsTarget.Cells(i, 11).Value = dataArray(i, 1) ' Customer Name

        wsTarget.Cells(i, 12).Value = dataArray(i, 5) ' Current Debt

        totalDebt = totalDebt + dataArray(i, 5)

    End If

Next i

' Write the total debt to cell L6

wsTarget.Cells(6, 12).Value = totalDebt

' Format the result columns

wsTarget.Columns("K:L").AutoFit

' Re-enable screen updates and calculation

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

    End Sub

    Sub MoControlPanel() 

    ' Check if the form is already loaded 

If Not IsLoaded("ControlPanel") Then 

    ' Load and show the form if it's not already loaded 

    ControlPanel.Show vbModeless ' Ensure it's shown in modeless mode 

End If

    End Sub

    ' Function to check if the UserForm is already loaded

    Function IsLoaded(frmName As String) As Boolean 

    Dim frm As Object 

IsLoaded = False 

' Loop through all open UserForms 

On Error Resume Next ' Ignore any errors from the next line 

For Each frm In VBA.UserForms 

    If frm.Name = frmName Then 

        IsLoaded = True 

        On Error GoTo 0 ' Reset error handling 

        Exit Function 

    End If 

Next frm 

On Error GoTo 0 ' Reset error handling

    End Function

    Sub MoQuahan() 

    ' Check if the form is already loaded 

If Not IsLoaded("Quahan") Then 

    ' Load and show the form if it's not already loaded 

    Quahan.Show vbModeless ' S? d?ng vbModeless đ? m? form mà không ch?n 

End If

    End Sub

    ' Function to check if the UserForm is already loaded

    Function IsLoaded(frmName As String) As Boolean 

    Dim frm As Object 

IsLoaded = False 

' Loop through all open UserForms 

On Error Resume Next ' B? qua l?i n?u không có UserForm nào m? 

For Each frm In VBA.UserForms 

    If frm.Name = frmName Then 

        IsLoaded = True 

        On Error GoTo 0 ' Khôi ph?c x? l? l?i 

        Exit Function 

    End If 

Next frm 

On Error GoTo 0 ' Khôi ph?c x? l? l?i

    End Function

    Sub MoTrangthaihd() 

    ' Check if the form is already loaded 

If Not IsLoaded("Trangthaihd") Then 

    ' Load and show the form if it's not already loaded 

    Trangthaihd.Show vbModeless ' M? form ? ch? đ? không ch?n 

End If

    End Sub

    ' Function to check if the UserForm is already loaded

    Function IsLoaded(frmName As String) As Boolean 

    Dim frm As Object 

IsLoaded = False 

' Loop through all open UserForms 

On Error Resume Next ' B? qua l?i n?u không có UserForm nào m? 

For Each frm In VBA.UserForms 

    If frm.Name = frmName Then 

        IsLoaded = True 

        On Error GoTo 0 ' Khôi ph?c x? l? l?i 

        Exit Function 

    End If 

Next frm 

On Error GoTo 0 ' Khôi ph?c x? l? l?i

    End Function

    If needed I can send you my project file

    Was this answer helpful?

    0 comments
  5. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2025-01-20T11:22:57+00:00

    You will need to post the code and, if possible, tell us which lines cause the errors.

    Was this answer helpful?

    0 comments