Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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 wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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 wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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!!! BEEJAY wrote: 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 wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
"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 "BEEJAY" wrote in message ... | 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 | wrote: | | 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? | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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 "BEEJAY" wrote in message ... | 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 | wrote: | | 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? | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
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 "BEEJAY" wrote in message ... | 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 | "BEEJAY" wrote in message | ... | | 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 | | wrote: | | | | 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? | | | | |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Expected Array
Jim: Thanks. Never thought of that.
"Jim Rech" wrote: 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 "BEEJAY" wrote in message ... | 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 | "BEEJAY" wrote in message | ... | | 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 | | wrote: | | | | 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? | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compile error - expected an array | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
compile error: Expected: = ...Why? | Excel Programming | |||
Compile Error: Expected End Property | Excel Programming | |||
compile error: expected variable or function | Excel Discussion (Misc queries) |