Subscript Out Of Range Using Public Array Variables
Aug 18, 2006
In my VBA project I've declared several public variables as normal (ahead of
all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it.
What is going on?
I have a form that finds an loads a record in textfields and combo boxes fine. When there are more than 1 record found, the user is presented with the FindAll option. Upon clicking find all, the list box gest populated with the records found. But instead, I am getting Run-Time error:'9' Subscript out of range in the following code block. It is occurring in the block starting with myArray: - MyArray(i, 0) = fndA:
Private Sub cmbFindAll_Click() Dim MyArray(6, 12) Dim FirstAddress As String Dim strFind As String 'what to find Dim rSearch As Range 'range to search Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL As String Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12 As String ' heading s for list Dim i As Integer i = 1 Set rSearch = Sheet1.Range("A6", Range("A65536").End(xlUp)) strFind = Me.txtWorkOrderNo.Value With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select 'load the headings
Public No_of_Sims As Variant Public Sim_No As Variant Public Ground_Up_Agg As Variant Public UseSelfInsuredVehicle As String Public Option_Name(1 To 12) As String
[Code] .....
The code highlighted in BOLD is where I am getting runtime error 9 (Subscript out of range).
In Excel I've declared an array outside of any sub or function.
Dim programList() As String
Now I want to use that array, populating and depopulating as needed by calling these subs from elsewhere:
Sub addProgramToArray(ByVal x As String) ' Adds worksheet names to an array for use with adjusting program lengths If IsArray(programList) Then thisCount = UBound(programList) End If Redim Preserve programList(thisCount + 1) programList(thisCount) = x
End Sub
Sub removeProgramFromArray(ByVal x As String) ' Removes worksheet names from array, for use with adjusting program lengths ' We do this without resorting the array If IsArray(programList) Then For i = 0 To UBound(programList) - 1 If programList(i) = x Then programList(i) = "" End If Next End If
End Sub
But when run it generates an Error 9 Subscript Out of Range error on the addProgram or removeProgram subs. I suspect it might be the ubound on an empty array, or the fact that I'm trying to use an array I declared outside of the subs.
I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
I am trying to use a public variable but it is not holding the value when I call another sub.
Here is basically what I have:
Public AcctName as Long
Sub Statments() There is much more code in here but it is not relevent AcctName = an account name lets say "frank"
If X XX then Call Title End If
End Sub
It then goes to the title sub and executes the code however it is supposed to write out the value held in acctname but it comes up blank instead of as Frank which is the value it held when it left the statments sub. I am new to this public thing and need any help I can get. Just a note: the spelling is correct and the variable does hold the correct value before title sub is called.
I've recently been experiencing some strange phenomena in relation to public variables. It seems that if I run separate routines in quick enough succession (e.g. press a button twice within a couple of seconds), the memory of the public variable "hangs over" into the second routine.
The routines, however, are completely separate events. And I'm not using userforms or anything like that in order to keep VBA running in the background. In which case it seems that Public variables don't always get set to nothing with the end of the final sub in a routine. Has anyone else experienced anything like this? I was under the impression only Static variables could do this.
I'm programming with multiple workbooks. So public variables in one workbook often reference ranges in another. Anyhow, I have found that, when closing a workbook, unless I nullify all public variables that reference this workbook, it closes in excel but remains visible in the VB Editor. When I open the workbook again, another instance of its VB project appears in the VB Editor..and so on and so on, which is obviously pretty annoying..
I've worked out a way of nullifying (setting equal to nothing) all public variables in a workbook, but it's quite arduous and I haven't tried it yet.. basically, it involves running a code which runs through the procedure which sets these public variables (they are all set in a single procedure), then having the code write a new subroutine setting all variables to nothing, then running this subroutine..
Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.
This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends
I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next
ActiveSheet.Cells(2, 3) = Me.MediaP.Value
and it works. But now, i want to call to MediaP in a module. For do that I try to do this:
With Worksheets("Hoja2") .Range("B1").Value = Option.MediaP.Value
but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.
When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?
I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.
I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).
I've got two public dynamic arrays being declared in a module attached to my workbook. I then have a sub that runs off a userform in that workbook which populates and redims the arrays preserving the data. Next I activate a PowerPoint application and insert some tables into which I want to put the data in the arrays but when I refer to the arrays (admittedly in a Sub called from the first Sub) I'm getting a 'subscript out of range error'. I can print out the data before I call the second sub and it's fine. I've worked with public variables before and never had this problem. Do I have to call the public variable differently if I'm curretnly in a different application (i.e. PowerPoint)?
I have a public array in a module in my spreadsheet. The array is an array of a class type (not integers, strings, etc). When I run the macro that uses the array for processing the first time, everything seems to work properly. However, if I run the macro a second time WITHOUT getting out of Excel and coming back in, it doesn't come back with the proper results. It seems like either the array isn't being cleared out properly between the macro runs, OR that something in the macro is overwriting memory. Is there a way to make sure the array is being cleared out between runs of the macro?
i'm trying to develop an array that is populated when a userform is activated. The userform has a command button that when pressed, will cross check the information filled out in the userform with the entries in the array. If there are no matches then the array is ReDim and the new information is added to the array. Once all the entries have been made. The array is then transposed to a sheet titled "Database". My problem is that vba is not allowing me declare a public array.Below is the first part of the code. Which is when the userform is activated.
Private Sub UserForm_Activate() With Sheets("Resources") cbZIP.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value cbBED.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value cbBATH.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value cbSTABRV.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value End With
But I need the array to be public as the array can hold one of 6 different sets of values so I will declare the array to be only used within the module as:
Dim CurOptions(30)
Later I need to fill in the values manually, they will not be copied in, they need to be provided in a list.
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
I am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are: completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls cashsheetname Formatted Sheet cashcurrcolumn A cashfirstrow 2 cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _ Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _ Range((PELCurrColumn & PELlastrow)).Value
I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.
Here is the code from my sub that applies to this error:
Code: Public Sub PutInEngine() Dim InputForm As New FrmInputs InputForm.Show
I have a master spreadsheet in XL2003 (SP3) running Vista. The master sheet, called "DCam IV-11.xls" is a large calculation sheet that is driven by user entered boundary conditions. The boundary conditions are stored in different input files named "******-inp.xls". I use this method to store BCs in small spreadsheet files rather than saving full master spreadsheets in files.
keep getting an error message saying subscript out of range.
What am i missing?
'setting up the variable in the projectManagersWorkbook object Set projectManagersWorkbook.Wkbk = Workbooks(getfileName(projectManagersWorkbook.filePath)) Set projectManagersWorkbook = projectManagersWorkbook.Wkbk.Worksheets("project managers") Set projectManagersWorkbookLookupCell = projectManagersWorkbook.wks.Range("A2") ' searching for the Last Status Column from the last PO spreadsheet vlookupcolumn = 1 Do Until tempValue = "Project Manager" tempValue = projectManagersWorkbookLookupCell.Offset(-1, vlookupcolumn) vlookupcolumn = vlookupcolumn + 1 Loop
What I'm trying to do is change the text color of specific keywords in a spreadsheet. For example, I'd like to highlight the text of 'how to' in the following sentence "I want to know how to change text color", so that it looks like this "I want to know how to change text color".
I came across the following, and it works for only two terms (example, "how to" , "how do I"), but I have a string of about 15 terms I'd like to use, but anything I change over two, I get the subscript out of range error.
what I'm trying to do is automatically format Excel's track changes to look like Microsoft Word's track changes feature i.e. put in a strike-through when people make changes on a shared spreadsheet.
I found the following script from here: [URL] .....
