Search code examples
vbaexcelcode128

Run time error 424 in excel macros

I am new to excel macros, I am working on creating a macro which needs to format the cells and also to generate the bar code for column A. Created a function Code128() to convert the string in to bar code as given in the blog and it works fine.

enter image description here

I am using them in the macros I am creating like below

 With ActiveSheet.PageSetup
 .PrintTitleRows = "$1:$1"
 .PrintGridlines = True

 .Orientation = xlLandscape
 .PaperSize = xlPaperA4

 .Zoom = False
 .FitToPagesWide = 1
 .FitToPagesTall = False

End With

Columns("A").ColumnWidth = 10

For Each Target In Range("A1", Range("A" & Rows.Count).End(xlUp))
If Target.Value <> vbNullString Then
    Target.Value = PERSONAL.XLSB!Code128(Target.Value)
    Target.Resize(, 12).WrapText = True
    Target.Font = "Code 128"
End If
Next

Next

But when I run the macro on the excel I am getting the run time error like

enter image description here

enter image description here

Solution

  • You should be able to use Application.Run to evaluate a function that exists in another workbook. The format of that is:

    Target.Value = Application.Run("PERSONAL.XLSB!Module2.Code128", Target.Value)

    Or more generally:

    = Application.Run(workbookname$ & "!" & modulename & "." & functionname, args())

    Barring that, you could Add a reference to Personal.xlb in your Book1.