Compile Error Invalid Redim
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 Us Learn more about Stack Overflow the company Business compile error invalid qualifier Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation invalid qualifier error in vba Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just redim variant vba like you, helping each other. Join them; it only takes a minute: Sign up VBA: Function array, ReDim gives invalid ReDim up vote 4 down vote favorite 2 I am using a Function to make an array. The
Redim Preserve 2d Array Vba
input is also an array. When running it gives me an invalid ReDim compile error. Before this was run in a sub routine and the ReDim worked well but now I changed it in a Function and it gives the invalid ReDim compile error. What am I missing here? Thanks in advance! Amir Public Function bmhussel(filemx As Variant) rijaantal = UBound(filemx, 1) kolomaantal = UBound(filemx, 2) ReDim bmhussel(1 To rijaantal + 1, 1 To kolomaantal vba redim preserve + 1) For i = 1 To rijaantal bmhussel(i, 1) = filemx(i, 1) bmhussel(i, 2) = filemx(i, 3) bmhussel(i, 3) = filemx(i, 5) bmhussel(i, 4) = filemx(i, 28) bmhussel(i, 5) = bucket(filemx(i, 28)) 'buckets maken next i End Function arrays function vba excel-vba subroutine share|improve this question edited Dec 24 '12 at 23:49 Cylian 6,27122446 asked Dec 24 '12 at 23:03 Amir 531311 add a comment| 2 Answers 2 active oldest votes up vote 8 down vote accepted Welkom op Stack overflow. As said you cannot redim the function itself. Therefore use a temporary variable and in the end transfer its content to your function: Public Function bmhussel(filemx As Variant) as Variant Dim rijaantal As Long Dim kolomaantal As Long Dim tmpArray as Variant rijaantal = UBound(filemx, 1) kolomaantal = UBound(filemx, 2) ReDim tmpArray (1 To rijaantal + 1, 1 To kolomaantal + 1) For i = 1 To rijaantal tmpArray(i, 1) = filemx(i, 1) tmpArray(i, 2) = filemx(i, 3) tmpArray(i, 3) = filemx(i, 5) tmpArray(i, 4) = filemx(i, 28) tmpArray(i, 5) = bucket(filemx(i, 28)) 'buckets maken next i bmhussel = tmpArray End Function share|improve this answer answered Dec 24 '12 at 23:15 K_B 3,25011024 add a comment| up vote 4 down vote bmhussel is the name of your function and not the name of a variable. You cannot Redim your function. ReDi
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros [SOLVED] Compile Error: Invalid ReDim To get replies by our experts at redim array vba nominal charges, follow this link to buy points and post your thread
Ubound Vba
in our Commercial Services forum! Here is the FAQ for this forum. + Reply to Thread Results 1 to 7 of 7 Compile Error: Invalid ReDim Thread Tools Show Printable Version Subscribe to this Thread… Rate This Thread Current Rating Excellent Good Average Bad http://stackoverflow.com/questions/14026346/vba-function-array-redim-gives-invalid-redim Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 08-02-2013,11:29 AM #1 welchs101 View Profile View Forum Posts Forum Contributor Join Date 12-01-2007 Location USA-North Carolina MS-Off Ver MS Office 2007 Posts 2,168 Compile Error: Invalid ReDim Hi, I have the following code which causes an error: Please Login or Register to view http://www.excelforum.com/showthread.php?t=944401 this content. However, i will now SWAP the order of the if-statements and the following code passes: Please Login or Register to view this content. can someone explain it to me? I have also submitte my actual file incase some one wanted to see it. Attached Files Compile_redim_error.xlsm (15.9 KB, 4 views) Download Register To Reply 08-02-2013,11:35 AM #2 JieJenn View Profile View Forum Posts Visit Homepage Forum Expert Join Date 12-14-2009 Location San Francisco, CA MS-Off Ver 2010 & 2013 Posts 2,774 Re: Compile Error: Invalid ReDim From your test array fails module, type Dim junk_array() As Variant in the beginning of the module then try again. And, I am not sure if you can assign a value to a range. ' junk_array = ws.Range("A1:B" & last_row_1).Value Last edited by JieJenn; 08-02-2013 at 11:37 AM. To thank someone who has helped you, click on the star icon below their name. I hate reading Guess I am no longer young after turning 30... Register To Reply 08-02-2013,11:36 AM #3 Norie View Profile View Forum Posts For
If 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 register link above to proceed. To start viewing messages, select the forum that you want http://www.vbforums.com/showthread.php?5099-Invalid-ReDim-please-help!!! to visit from the selection below. Results 1 to 4 of 4 Thread: Invalid ReDim - please help!!! Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jan 24th, 2000,04:48 AM #1 Jhd.Honza View Profile View Forum Posts Thread Starter Hyperactive Member Join Date Apr 1999 Location Prague, Czech Republic Posts 350 I have got a big problem now. In declaration section I have: Type Foto Picture() As error in IPicutreDisp 'or something like that Descrip() as String End Type Some rows of a sub are: ReDim Foto.Picture(Val(read)) ReDim Foto.Descrip(Val(read)) where the first line (picture) cause a error 'Invalid ReDim'. Where is the error? I need to leave a picture stored in a dynamic array. ------------------ Thanks, John, 14 years old Reply With Quote Jan 24th, 2000,05:25 AM #2 ChrisJackson View Profile View Forum Posts Lively Member Join Date Oct 1999 Posts 101 Hi John, What is the value of read compile error invalid when you get this error? If you want to keep the info. already stored in your array when you Redim it, you must use Redim Preserve rather than Redim alone. All the best. Chris Reply With Quote Jan 25th, 2000,12:42 AM #3 Jhd.Honza View Profile View Forum Posts Thread Starter Hyperactive Member Join Date Apr 1999 Location Prague, Czech Republic Posts 350 Thank you for your answer. The value of read is usually 1 to 3 or 5, no big numbers. Preserve keyword cause the same error. What should I do now? Reply With Quote Jan 25th, 2000,01:17 AM #4 RogerH View Profile View Forum Posts Visit Homepage Lively Member Join Date Dec 1999 Location Karlsruhe, Germany Posts 122 Hi John, the problem seems to be the type! Try this: Code: Type Foto Picture() As IPicutreDisp 'or something like that Descrip() as String End Type sub test dim testvar as Foto ReDim testvar.Picture(Val(read)) ReDim testvar.Descrip(Val(read)) end sub Roger Reply With Quote Quick Navigation Visual Basic 6 and Earlier Top Site Areas Settings Private Messages Subscriptions Who's Online Search Forums Forums Home Forums Visual Basic Visual Basic .NET VB.net CodeBank Visual Basic 6 and Earlier CodeBank - Visual Basic 6 and earlier Modern Windows Experience (Metro) Office Development Mobile Development ASP, VB Script Database Development Reporting API Games and Graphics Programming Game Demos COM and ActiveX Network Programming Visual Basic FAQs Slow Chat with the Microsoft Visual Basic team .NET ASP.N