Problom With VBA code throw an “user defined type not defined” error

Question

I cannot figure out why the following code is throwing a compile error with the message "user defined type not defined". It is highlighting Set fso = FileSystemObject

Sub S()     
Dim fso As FileSystemObject
    Dim ts As TextStream
    Dim i As Integer
    Dim myCell As Range

    Set fso = FileSystemObject

    For i = 0 To TotalColumnNumber
       ' last argument, True, says to create the text file if it doesnt exist, which is
       ' good for us in this case
       Set ts = fso.OpenTextFile("column_" & i, ForWriting, True)

       ' set mycell to the first cell in the ith column
       Set myCell = SheetName.Cells(1, i)

       ' continue looping down the column until you reach a blank cell
       ' writing each cell value as you go
       Do Until myCell.Value = ""
           ts.writeline myCell.Value
           Set myCell = myCell.Offset(1, 0)
       Loop

       ts.Close
    Next

    Set ts = Nothing
    Set fso = Nothing


    End Sub

thanks

Move the line Sub S() to the top of your code.

Gary's Student
– Gary's Student

2019-02-16 14:35:17 +00:00
Commented Feb 16, 2019 at 14:35 — Gary's Student
– Gary's Student, Commented Feb 16, 2019 at 14:35
@Gary'sStudent now Sub S() is highlighting :(

shahab47
– shahab47

2019-02-16 14:40:46 +00:00
Commented Feb 16, 2019 at 14:40 — shahab47
– shahab47, Commented Feb 16, 2019 at 14:40
Try Set fso = New FileSystemObject

S Meaden
– S Meaden

2019-02-16 14:52:26 +00:00
Commented Feb 16, 2019 at 14:52 — S Meaden
– S Meaden, Commented Feb 16, 2019 at 14:52
trumpexcel.com/vba-filesystemobject

patel
– patel

2019-02-16 14:53:50 +00:00
Commented Feb 16, 2019 at 14:53 — patel
– patel, Commented Feb 16, 2019 at 14:53

Gary's Student · Accepted Answer · 2019-02-16 17:02:09Z

2

The code has ~~many~~ several problems:

You need to set a Reference

use: Set fso = New FileSystemObject
establish values for your variables before you use them; this includes TotalColumnNumber, SheetName, etc.

edited Feb 16, 2019 at 17:02

answered Feb 16, 2019 at 15:06

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iSpain17 · Accepted Answer · 2019-02-16 14:53:37Z

0

Are you referencing the correct namespaces in Tools/References?

answered Feb 16, 2019 at 14:53

iSpain17

3,1403 gold badges22 silver badges34 bronze badges

Collectives™ on Stack Overflow

Problom With VBA code throw an “user defined type not defined” error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related