Compile Error Expected Array Range
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
Compile Error Expected Array In Vba
Us Learn more about Stack Overflow the company Business Learn more about hiring excel vba compile error expected array developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the compile error expected = Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up EXCEL VBA Error: “Compile Error:
Compile Error Expected End With
Expected Array” up vote 1 down vote favorite Can anyone help me? I have been getting a compile error (...: "Expected Array") when dealing with arrays in my Excel workbook. Basically, I have one 'mother' array (2D, Variant type) and four 'baby' arrays (1D, Double type). The called subroutine creates the publicly declared arrays which my main macro ends up using for display purposes. Unfortunately, the
Compile Error Expected Identifier
final of the baby arrays craps out (giving the "Compile Error: Expected Array"). Strangely, if I remove this final baby array ('final' - as in the order of declaration/definition) the 2nd to last baby array starts crapping out. Here is my code: Public Mother_Array() as Variant, BabyOne_Array(), BabyTwo_Array(), BabyThree_Array(), BabyFour_Array() as Double 'declare may other variables and arrays, too Sub MainMacro() 'do stuff Call SunRaySubRoutine(x, y) 'do stuff Range("blah") = BabyOne_Array: Range("blahblah") = BabyTwo_Array Range("blahbloh" = BabyThree_Array: Range("blahblue") = BabyFour_Array End Sub Sub SunRaySubRoutine(x,y) n = x * Sheets("ABC").Range("A1").Value + 1 ReDim Mother_Array(18, n) as Variant, BabyOne_Array(n), BabyTwo_Array(n) as Double ReDim BabyThree_Array(n), BabyFour_Array(n) as Double 'do stuff For i = 0 to n BabyOne_Array(i) = Mother_Array(0,i) BabyTwo_Array(i) = Mother_Array(2,i) BabyThree_Array(i) = Mother_Array(4,i) BabyFour_Array(i) = Mother_Array(6,i) Next End Sub I have tried to declare all arrays as the Variant type, but to no avail. I have tried to give BabyFour_Array() a different name, but to no avail. What's really strange is that even if I comment out the part which makes the BabyFour_Array(), the array still has zero values for each element. What's also a bit strange is that the first baby array never craps out
this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the compile error expected identifier or bracketed expression register link above to proceed. To start viewing messages, select the forum
Compile Error Expected End Of Statement
that you want to visit from the selection below. Results 1 to 14 of 14 Thread: [RESOLVED] Expected Array??? compile error expected end of statement macro Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Nov 20th, 2011,11:45 AM #1 PaulTilley View Profile View Forum Posts http://stackoverflow.com/questions/17255458/excel-vba-error-compile-error-expected-array Thread Starter Addicted Member Join Date Sep 2000 Location UK Posts 199 [RESOLVED] Expected Array??? I've just tried to compile my project and all of a sudden some functions that have been working perfectly are causing a compile error: one example: Code: Public Function StripCurrSymbol(StrIn As String) As String If Left(StrIn, 1) = "£" Then StripCurrSymbol = Mid(FormatMoney(StrIn), 2) Else StripCurrSymbol = StrIn End http://www.vbforums.com/showthread.php?665550-RESOLVED-Expected-Array If End Function another example is where im using a 'right'. The error I get is Compile Error: Expected Array and it highlights 'left' any ideas? Reply With Quote Nov 20th, 2011,11:52 AM #2 lone_REBEL View Profile View Forum Posts Hyperactive Member Join Date Jan 2006 Location Pakistan Posts 388 Re: Expected Array??? Its REALLY weird! VB sometimes does absurd things to me, too. Try closing and again opening the project and see if it compiles correctly. If not (as expected), can you post the value of StrIn variable when the error raises? (Taking the mouse cursor on it will display the value) If your problem is solved, then drag down the Thread Tools and mark your thread as Resolved. If I helped you solve your problem, inflate some air into my ego by rating my post and adding a comment too. For notorious issues (elaborate yourself) contact me via PM. I don't answer them in the forums EVER. Reply With Quote Nov 20th, 2011,11:53 AM #3 Spoo View Profile View Forum Posts PowerPoster Join Date Nov 2008 Location Right Coast Posts 2,656 Re: Expected Array??? Paul Two possibil
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build http://www.ozgrid.com/forum/showthread.php?t=165144 Automated Trading Models In Excel | Excel Web Pages | Excel http://www.pcreview.co.uk/threads/compile-error-expected-array.3690399/ Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Compile Error: Expected Array (Need fix) Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ compile error by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 2 compile error expected of 2 Thread: Compile Error: Expected Array (Need fix) Thread Tools Show Printable Version Search Thread Advanced Search May 8th, 2012 #1 ducksonice00 View Profile View Forum Posts I agreed to these rules Join Date 7th May 2012 Posts 3 Compile Error: Expected Array (Need fix) Basically I made a program that received a list of numbers from a .csv file and inputs into a spreadsheet on cells a2:aXXX. Then I am trying to count the numbers, find the mean and standard deviation of them. Here is my code: VB: Dim i, nd As Integer Dim w(1000) As Double Dim m As Variant Dim sd As Variant Dim n As Variant Dim binsize As Double Dim range As Double Dim bincount As Double Open "F:\ME 232A\weights.csv" For Input As #1 nd = -1 Do If EOF(1) Then Exit Do nd = nd + 1 Input #1, w(nd) Loop Close #1 Sheets("sheet1").Select Cells(2, 1).Select For i = 0 To nd ActiveCell.Value = w(i) ActiveCell.Offset(1, 0).Select Next i n = Application.WorksheetFunction.CountIf([COLOR=#daa520]range[/COLOR]("a2:a1000"), ">0") m = Application.WorksheetFunction.Average([COLOR=#daa520]range[/COLOR]("a2:a1000")) sd = Application.WorksheetFunction.StDev([COLOR=#daa520]range[/COLOR]("a2:a1000")) I keep getting Compile Error: Expected Array, where it
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, Dec 8, 2008 #1 Advertisements Chip Pearson Guest 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? Chip Pearson, Dec 8, 2008 #2 Advertisements BEEJAY Guest 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'. Th