Compile Error: Expected Array

[BEEJAY]

Greetings All
Getting error: Compile Error: Expected array in the following code:

Sub WhoAreYou()
Dim Workbook As Long
Dim Worksheets As Long

' The following WorkBook is NOT Open/Active
If Workbook("C:\Excel
Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJM" Then

Call CC_Message1 'Opening message for required code
End If
If Worksheets("QCNum.xls").Range("C_C_TJM_JFS") = "" Then
Call Notxxx 'Message "not available to you" and exits procedure
End If
End Sub

How to make this work, please?

 

[Chip Pearson]

There are several problems. First, you are declaring variables with
the name 'Workbook' and 'Worksheets'. These are reserved words in
Excel/VBA and using these names are variables is likely causing
problems. Delete the declarations as it seems you are not using them.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[BEEJAY]

Chip:

I removed the dim statements and then get "compile Error: Sub or Function
Not defined.

In the meantime, after further reading I tried to shorten up the code to
following
(but it still gives me the same error, just mentioned)
Also, to confirm, a workbook does NOT have to be Open/Active to be able to
READ it, Correct?

Sub WhoAreYou()
If Workbook("C:\Excel
Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJMk" Then
Call CC_Message1 'Opening message for required code
Else
Call Notxxx ' Message 'not available to you' and exits
procedure
End If
End Sub

 

[Dave Peterson]

In that top "if" statement, it should be workbooks(), not workbook(). And you
don't include the path.

If Workbooks("QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJM" Then

But QCNum.xls has to be open for this to work, too.

One way around it is to find an empty cell and put a formula into that cell that
retrieves the value from the closed workbook.

Then retrieve that value from the cell.

Then clear that cell.

dim myCell as range
dim myVal as variant 'double or string????
with activesheet
set mycell = .cells.specialcells(xlcelltypelastcell).offset(1,1)
end with

'watch your syntax. It's easy to screw up!
mycell.formula = "='C:\Excel Add_Ins\[QCNum.xls]Sheet1'!C_C_TJM_JFS"
myval = mycell.value
mycell.clearcontents

if ucase(myval) = ucase("TJMk") then
.....

=====
Untested, uncompiled. Watch for typos!!!

 

[Jim Rech]

"compile Error: Sub or Function Not defined.

There is no "workbook" function so that's probably causing this error.

I think you're going to have to open the workbook first to do what want.
Then use the Workbooks method to access the open workbook.

--
Jim
| Chip:
|
| I removed the dim statements and then get "compile Error: Sub or Function
| Not defined.
|
| In the meantime, after further reading I tried to shorten up the code to
| following
| (but it still gives me the same error, just mentioned)
| Also, to confirm, a workbook does NOT have to be Open/Active to be able to
| READ it, Correct?
|
| Sub WhoAreYou()
| If Workbook("C:\Excel
| Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJMk"
Then
| Call CC_Message1 'Opening message for required code
| Else
| Call Notxxx ' Message 'not available to you' and exits
| procedure
| End If
| End Sub
|
| "Chip Pearson" wrote:
| >
| > There are several problems. First, you are declaring variables with
| > the name 'Workbook' and 'Worksheets'. These are reserved words in
| > Excel/VBA and using these names are variables is likely causing
| > problems. Delete the declarations as it seems you are not using them.
| >
| > Cordially,
| > Chip Pearson
| > Microsoft MVP
| > Excel Product Group
| > Pearson Software Consulting, LLC
| > www.cpearson.com
| > (email on web site)
| >
| >
| > On Mon, 8 Dec 2008 06:02:01 -0800, BEEJAY
| >
| > >Greetings All
| > >Getting error: Compile Error: Expected array in the following code:
| > >
| > >Sub WhoAreYou()
| > > Dim Workbook As Long
| > > Dim Worksheets As Long
| > >
| > >' The following WorkBook is NOT Open/Active
| > > If Workbook("C:\Excel
| > >Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJM"
Then
| > >
| > > Call CC_Message1 'Opening message for required code
| > > End If
| > > If Worksheets("QCNum.xls").Range("C_C_TJM_JFS") = "" Then
| > > Call Notxxx 'Message "not available to you" and exits
procedure
| > > End If
| > >End Sub
| > >
| > >How to make this work, please?
| >

 

[BEEJAY]

Gentlemen (ALL)

Thanks for all the info. Always amazes me how many ways one can get a
certain thing done.
For now, I went with opening the file in order to get at the range name info.
Then close it asap, so it can't interfere with any other part of the
procedure.
Final (working) code reads as follows:

Sub WhoAreYou()
Workbooks.Open Filename:="C:\EXCEL ADD_INS\QCNum.xls"
If Workbooks("QCNum.xls").Worksheets("Sheet1").Range("C_C_TM_JFS")
= "TJM" Then
Call CC_Message1 'Opening message for required code
Else
Call Notxxx ' Message 'not available to you' and exits
procedure
End If
End Sub

Again, as always, thanks for the prompt input.

 

[Jim Rech]

If there is some risk another user will have it open or will want to open
it, you could set the "readonly" parameter of the Workbooks.Open method to
True. I do that as a matter of course unless I know I'm making a change
that will be saved.

--
Jim
| Gentlemen (ALL)
|
| Thanks for all the info. Always amazes me how many ways one can get a
| certain thing done.
| For now, I went with opening the file in order to get at the range name
info.
| Then close it asap, so it can't interfere with any other part of the
| procedure.
| Final (working) code reads as follows:
|
| Sub WhoAreYou()
| Workbooks.Open Filename:="C:\EXCEL ADD_INS\QCNum.xls"
| If Workbooks("QCNum.xls").Worksheets("Sheet1").Range("C_C_TM_JFS")
| = "TJM" Then
| Call CC_Message1 'Opening message for required code
| Else
| Call Notxxx ' Message 'not available to you' and exits
| procedure
| End If
| End Sub
|
| Again, as always, thanks for the prompt input.
|
|
| "Jim Rech" wrote:
|
| > >>"compile Error: Sub or Function Not defined.
| >
| > There is no "workbook" function so that's probably causing this error.
| >
| > I think you're going to have to open the workbook first to do what want.
| > Then use the Workbooks method to access the open workbook.
| >
| > --
| > Jim
| > | > | Chip:
| > |
| > | I removed the dim statements and then get "compile Error: Sub or
Function
| > | Not defined.
| > |
| > | In the meantime, after further reading I tried to shorten up the code
to
| > | following
| > | (but it still gives me the same error, just mentioned)
| > | Also, to confirm, a workbook does NOT have to be Open/Active to be
able to
| > | READ it, Correct?
| > |
| > | Sub WhoAreYou()
| > | If Workbook("C:\Excel
| > | Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJMk"
| > Then
| > | Call CC_Message1 'Opening message for required code
| > | Else
| > | Call Notxxx ' Message 'not available to you' and exits
| > | procedure
| > | End If
| > | End Sub
| > |
| > | "Chip Pearson" wrote:
| > | >
| > | > There are several problems. First, you are declaring variables with
| > | > the name 'Workbook' and 'Worksheets'. These are reserved words in
| > | > Excel/VBA and using these names are variables is likely causing
| > | > problems. Delete the declarations as it seems you are not using
them.
| > | >
| > | > Cordially,
| > | > Chip Pearson
| > | > Microsoft MVP
| > | > Excel Product Group
| > | > Pearson Software Consulting, LLC
| > | > www.cpearson.com
| > | > (email on web site)
| > | >
| > | >
| > | > On Mon, 8 Dec 2008 06:02:01 -0800, BEEJAY
| > | >
| > | > >Greetings All
| > | > >Getting error: Compile Error: Expected array in the following code:
| > | > >
| > | > >Sub WhoAreYou()
| > | > > Dim Workbook As Long
| > | > > Dim Worksheets As Long
| > | > >
| > | > >' The following WorkBook is NOT Open/Active
| > | > > If Workbook("C:\Excel
| > | > >Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") =
"TJM"
| > Then
| > | > >
| > | > > Call CC_Message1 'Opening message for required code
| > | > > End If
| > | > > If Worksheets("QCNum.xls").Range("C_C_TJM_JFS") = "" Then
| > | > > Call Notxxx 'Message "not available to you" and
exits
| > procedure
| > | > > End If
| > | > >End Sub
| > | > >
| > | > >How to make this work, please?
| > | >
| >
| >

 

[BEEJAY]

Jim: Thanks. Never thought of that.

If there is some risk another user will have it open or will want to open
it, you could set the "readonly" parameter of the Workbooks.Open method to
True. I do that as a matter of course unless I know I'm making a change
that will be saved.

--
Jim
| Gentlemen (ALL)
|
| Thanks for all the info. Always amazes me how many ways one can get a
| certain thing done.
| For now, I went with opening the file in order to get at the range name
info.
| Then close it asap, so it can't interfere with any other part of the
| procedure.
| Final (working) code reads as follows:
|
| Sub WhoAreYou()
| Workbooks.Open Filename:="C:\EXCEL ADD_INS\QCNum.xls"
| If Workbooks("QCNum.xls").Worksheets("Sheet1").Range("C_C_TM_JFS")
| = "TJM" Then
| Call CC_Message1 'Opening message for required code
| Else
| Call Notxxx ' Message 'not available to you' and exits
| procedure
| End If
| End Sub
|
| Again, as always, thanks for the prompt input.
|
|
| "Jim Rech" wrote:
|
| > >>"compile Error: Sub or Function Not defined.
| >
| > There is no "workbook" function so that's probably causing this error.
| >
| > I think you're going to have to open the workbook first to do what want.
| > Then use the Workbooks method to access the open workbook.
| >
| > --
| > Jim
| > | > | Chip:
| > |
| > | I removed the dim statements and then get "compile Error: Sub or
Function
| > | Not defined.
| > |
| > | In the meantime, after further reading I tried to shorten up the code
to
| > | following
| > | (but it still gives me the same error, just mentioned)
| > | Also, to confirm, a workbook does NOT have to be Open/Active to be
able to
| > | READ it, Correct?
| > |
| > | Sub WhoAreYou()
| > | If Workbook("C:\Excel
| > | Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJMk"
| > Then
| > | Call CC_Message1 'Opening message for required code
| > | Else
| > | Call Notxxx ' Message 'not available to you' and exits
| > | procedure
| > | End If
| > | End Sub
| > |
| > | "Chip Pearson" wrote:
| > | >
| > | > There are several problems. First, you are declaring variables with
| > | > the name 'Workbook' and 'Worksheets'. These are reserved words in
| > | > Excel/VBA and using these names are variables is likely causing
| > | > problems. Delete the declarations as it seems you are not using
them.
| > | >
| > | > Cordially,
| > | > Chip Pearson
| > | > Microsoft MVP
| > | > Excel Product Group
| > | > Pearson Software Consulting, LLC
| > | > www.cpearson.com
| > | > (email on web site)
| > | >
| > | >
| > | > On Mon, 8 Dec 2008 06:02:01 -0800, BEEJAY
| > | >
| > | > >Greetings All
| > | > >Getting error: Compile Error: Expected array in the following code:
| > | > >
| > | > >Sub WhoAreYou()
| > | > > Dim Workbook As Long
| > | > > Dim Worksheets As Long
| > | > >
| > | > >' The following WorkBook is NOT Open/Active
| > | > > If Workbook("C:\Excel
| > | > >Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") =
"TJM"
| > Then
| > | > >
| > | > > Call CC_Message1 'Opening message for required code
| > | > > End If
| > | > > If Worksheets("QCNum.xls").Range("C_C_TJM_JFS") = "" Then
| > | > > Call Notxxx 'Message "not available to you" and
exits
| > procedure
| > | > > End If
| > | > >End Sub
| > | > >
| > | > >How to make this work, please?
| > | >
| >
| >