CODEWITHSUNDEEP
vbaexcelexcel-2007
learningAsIGo
learningAsIGo

Reputation: 289

Create UDF in VBA Generated Workbook

I'm building an application in VBA to parse a machine generated file. As part of this project I need to convert the machine format into integers since the fixed width columns don't accommodate negative numbers. I used the macro recorder to parse the text file:

Workbooks.OpenText Filename:= _
    *FILE NAME HERE*, Origin:= _
    437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), _
    Array(6, 2), Array(10, 2), Array(14, 2), Array(19, 2), Array(25, 2), Array(27, 2), Array(34 _
    , 2), Array(39, 2), Array(43, 2), Array(44, 2), Array(52, 2)), TrailingMinusNumbers:= _
    True

but this creates a new workbook since it's opening a new file. The problem is I need to run a UDF on the final column that converts the fixed width value to an integer. For example, suppose the final column is 0000000E and I know that E means the last digit is 5 and the number is negative. My UDF converts this accordingly:

Public Function ConvertCodeToInteger(inputString As String) As Long


If inputString <> vbNullString Then
    Select Case Right(inputString, 1)
        Case "E" 'Negative 5
            ConvertCodeToInteger = -1 * (Left(inputString, Len(inputString) - 1) & 5)

        Case Else 'Error Case'
            MsgBox ("An error has occurred.")
            End
    End Select
Else
    ConvertCodeToInteger = vbNullString
End If

End Function

Private Sub TESTConvertCodeToInteger()

Debug.Print ConvertCodeToInteger("000001E")

End Sub

But since the file is open in a new workbook I can't access my UDF. Is there an easy way to use VBA to make the UDF available to the new workbook? Either by directly copying the function code into the new workbook's module using VBA somehow or just accessing the original code? Or is there a way to open the text file in the current workbook instead?

This needs to be portable so using the personal workbook isn't an option.

Upvotes: 0

Views: 100

Answers (1)

Chicago Excel User
Chicago Excel User

Reputation: 263

Record a Macro and import your text file using Data->From Text instead. That will import in your current workbook.

Upvotes: 1

Related Questions