Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - Worksheets does not work

I am trying to write an excel visual basic macro.

My Problem is that this code works:

Dim x As String
x = Worksheets("Abgabe").Cells(20, 3).Value

But this doesn't:

Dim y As Worksheet
y = Worksheets("Abgabe")

Also if I use ActiveWorkbook the code doesn't work.

Dim y As Worksheet
y = ActiveWorkbook.Worksheets("Abgabe")

I'm getting this error:

Object variable or With block variable not set

What could be the problem?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Dim y As Worksheet

y = Worksheets("Abgabe")

Use this (You have to use Set)

Dim y As Worksheet
Set y = Worksheets("Abgabe")

From MSDN (http://msdn.microsoft.com/en-us/library/aa192490.aspx):

Set Keyword: In VBA, the Set keyword is necessary to distinguish between 
assignment of an object and assignment of the default property of the object.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...