0

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

user4912134
  • 1,003
  • 5
  • 18
  • 47
  • I created the function under the personal workbook and it appears as shown in the image – user4912134 Mar 22 '17 at 18:57
  • I don't think you can refer to the Personal workbook in that manner.... The compiler is interpreting `PERSONAL.XLSB` as an `object.method` call and that's incorrect. hold on a second... – David Zemens Mar 22 '17 at 18:58
  • it's not that you can't *use* these functions, you just can't use them *the way you're trying to use them*. As for where they *should* go, they *cannot* go in a .CSV file, and whether you put them in Book1 or Personal.xlsb depends on where you need to use them. If they are local helper functions only, then put them in Book1. If they are general functions you might use on many different files, put them in Personal. – David Zemens Mar 22 '17 at 19:04
  • What could be the solution to this. – user4912134 Mar 22 '17 at 19:08

1 Answers1

1

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.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Now I get the error in the in the Line `Target.Font = "Code 128"`. I need to convert the Font Style of the values in Column A. It again says Object doesn't support this property or method. I have already installed the font Code 128 and see them in the excel font drop down list – user4912134 Mar 22 '17 at 19:19
  • @user4912134 Try `Target.Font.Name = "Code128"`. – David Zemens Mar 22 '17 at 19:34