Search code examples
excelvbaruntime-errorxlsmxlsb

XLSB Personal Macro file Variable Error

Disclaimer: This is not my code. I took a macro from a regular xlsm file and placed it on a xslb file to have it handy for analysis. The macro works correctly in the xlsm, but encounters a variable issue on the xlsb file.

ColumnToFormat identifies a specific column. I verified that the column is present within the worksheet on my Locals window.

enter image description here

Main Sub

ColumnToFormat = Application.WorksheetFunction.Match("OR_TR_OLD_BAL", ActiveSheet.Rows(1), False)

Call FormatAmounts

When I start running FormatAmounts, however, the variable disappears and Excel throws a RunTime Error 1004; Application Defined or Object Defined Error. When testing the xlsm file, this does not happen. The variable gets passed from the main sub to the helper sub. See FormatAmounts sub below.

enter image description here

Sub FormatAmounts()
ActiveSheet.Columns(ColumnToFormat).Select
 Selection.TextToColumns Destination:=Range(ActiveSheet.Columns(ColumnToFormat).Address), DataType:= _
        xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:= _
        False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub
Solution

  • Pass the ColumnToFormat value over to the sub procedure and add error control in the main sub by using Application.Match to a variant and testing the variant for error.

    sub main()
    dim ColumnToFormat  as variant

    ColumnToFormat = Application.Match("OR_TR_OLD_BAL", ActiveSheet.Rows(1), 0)

    if not iserror(ColumnToFormat ) then FormatAmounts clng(ColumnToFormat)
end sub

Sub FormatAmounts(c as long)
    with ActiveSheet.Columns(c)
        .TextToColumns Destination:=.cells(1), DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
                       Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                       FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    end with
End Sub