variable not set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raul Sousa - 26 May 2008 12:36 GMT

I have this code, I wrote.
I am not very confortable with VBA, so I have a problem.

When it arrives at the last line it gives an error. Run-time error, 91 :
“variable not set”.

I can’t understand why.

Dim UlinDez, UlinMovOr, UlinCarOr, UlinMovFn, UlinCarFn As String
Dim wbDez As Workbook
Dim wbMov As Workbook
Dim WbCar As Workbook
Dim FicDez, FicMov, FicCar As String
Dim Emp, Mes As String

FicDez = Application.GetOpenFilename
FicMov = Application.GetOpenFilename
FicCar = Application.GetOpenFilename

Mes = Application.InputBox("Indicar o mês")

Workbooks.OpenText Filename:=FicDez _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
Semicolon:=True, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True

wbDez = ActiveWorkbook.Name

Reply to this Message

Jim Cone - 26 May 2008 13:16 GMT

The last line is missing a Set statement, which is required for all objects...
Set wbDez = ActiveWorkbook

Or maybe you meant...
Emp = ActiveWorkbook.Name

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Raul Sousa"
<RaulSousa@discussions.microsoft.com>
wrote in message
I have this code, I wrote.
I am not very confortable with VBA, so I have a problem.

When it arrives at the last line it gives an error. Run-time error, 91 :
“variable not set”.
I can’t understand why.

Dim UlinDez, UlinMovOr, UlinCarOr, UlinMovFn, UlinCarFn As String
Dim wbDez As Workbook
Dim wbMov As Workbook
Dim WbCar As Workbook
Dim FicDez, FicMov, FicCar As String
Dim Emp, Mes As String

FicDez = Application.GetOpenFilename
FicMov = Application.GetOpenFilename
FicCar = Application.GetOpenFilename

Mes = Application.InputBox("Indicar o mês")

Workbooks.OpenText Filename:=FicDez _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
Semicolon:=True, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True

wbDez = ActiveWorkbook.Name

Reply to this Message

Rick Rothstein (MVP - VB) - 26 May 2008 15:53 GMT

To follow up on Jim's response to you, the error is being generated because
you declared the wbDez variable to be of type Workbook, but you are trying
to assign a simple String data type to it (the Name property of the
ActiveWorkbook is a text String, not a workbook object).

I would also like to point out that one of your Dim statements is not doing
what you think it is, and more than likely neither are two of your other
ones (I can't tell for sure because the snippet of code you provided doesn't
make use of the variables declared in them). The one I am sure of is this
one...

Dim FicDez, FicMov, FicCar As String

In the above statement, **only** the variable named FicCar is declared as a
String variable; the other two are getting declared as Variants. Unlike many
other languages, VB requires that all variables be declared individually as
to their type and, if that is not done, they default to being declared as
Variants. So, if you did not want to declare each of those variables in
individual Dim statements (my personal choice on how to do it), you would
need to modify that above statement like this...

Dim FicDez As String, FicMov As String, FicCar As String

The other two Dim statements that probably need to be modified (for the same
reason as discussed above) are these...

Dim UlinDez, UlinMovOr, UlinCarOr, UlinMovFn, UlinCarFn As String
Dim Emp, Mes As String

Rick

>I have this code, I wrote.
> I am not very confortable with VBA, so I have a problem.
[quoted text clipped - 25 lines]
>
> wbDez = ActiveWorkbook.Name

Reply to this Message