I am trying to declare a public (or global) array and it's values so I don't have to keep dimensioning it for each function I create.
I was trying things such as:
Public whatever(10) as string
whatever(0) = "something"
whatever(1) = "somethin else"
....
And tried creating an initialize macro in "ThisWorkbook".
I understand how to create an array within a routine:
CurOptions = Array("M1", "M3", "M3 DSR", "L1", "L2", "CSA")
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.
It should be just one line.
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)?
View 2 Replies View RelatedI 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?
View 9 Replies View RelatedHow can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.
Public MyVar()
Sub test1()
Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub
Sub test2()
For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'
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
[code]....
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?
how is the best method to link up forms to "talk" to my modules? (aka set variables inside my module script)
the way i understand it with my C++ background, it seems like separate forms and modules act much like private declarations do inside class calls.
I know that in C++ i can declare a friend class to grant access to communication between classes... and i'm wondering if such a procedure is possible in VBA as well...
or to break it down for those not familiar with C++, quite simply, is there a method of code i can implement to have different user forms and modules to talk to eachother? as it seems that any variable declaration is all inclusive to that one object.
The only way i can think of passing variables is by having my code create a worksheet, paste variables one by one into cells, bounce to the destination module, and import those declarations one by one through the cell contents, and remove the sheet after use...
I know i can do it that way, but i really dont want to as that is pretty darn sloppy, and a mickey mouse way to tie my program together.
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
[Code].....
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies View RelatedQuestion contained within code:
Code:
Dim MyArray(6, 1) As String
Dim i As Integer
'' Array List 1
[Code]....
'If lbxLI = any list index from Array List 1 then use the value stored in Array List 2 to give Listbox 2 its rowsource.
'For example; ListIndex "0" would produce a row source "_0" for listbox 2. Else if ListIndex "1" is selected, produce row source "_1" for listbox 2.
' I would like to achieve this from one "If" statement. I'm guessing it must be possible using a loop, I just can't think how despite a lot of messing about with code.
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO()
Dim TNs As Long
Dim i As Long
Dim TempArray() As String
Dim TRCO As String
Dim CD03 As String
Dim ASOC As Range
TRCO = "TRCO"
CD03 = "CD034DF1"
'Filter TN's
Columns("B:B").EntireColumn.Insert
Set tempRange = Range("A6", Range("A65000").End(xlUp))
With tempRange
. AdvancedFilter _...................
Setting the Public Statement does not work. These are my codes:
Option Explicit
Public Ankis_makro As Boolean
Sub Makro2_Ankis_veckor()
On Error Resume Next
Application. ScreenUpdating = False
Sheets("Listan").Activate
Selection. AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Range(Range("J2"), Range("I65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = "=TEXT(RC[-9],""ลลลล"")" ...................
What ever I do I can't get the Ankis_makro set to True.
I have declared one variable outside of Sub. And in a Sub I gave it a value. Then I run a macro, which should take that value and print it to the cell. However, it is printing a blank cell, not even "0" (I run a macro and anything in that cell is removed).
[Code].....
and then my macro:
[Code]......
How to make so macro see my variable declared outside of this macro?
I am using a set of arrays in a module. The arryas are set as public as the module that loads the data is called many times from diffrent other modules that then use the data.
How so I reset the array at the start of the module so each level is "Empty"
so new data can be loaded with no data left over from the last time the array was used.
I'm using a public declaration for a worksheet but I seem to have to initialize it in each routine. Is there a way to set it and leave it? So in a module I have:
View 4 Replies View RelatedI have a combo box which is on a worksheet and it's always active/visible. I need to pass on the selection data to another project. I thought I had successfully converted it to a public variable but it doesn't work.
I've indicated that the Tr variable is public and it does populate with the results of the case select but to no avail.
What do I have to do?
Public Tr As String
Public Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As String
Select Case ComboBox1.Value
Case "NYF PT"
[Code] .........
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 have a number of userforms that all reference the same worksheet. I wanted to create a public variable for that worksheet so I don't have to keep referencing it in each userform/commandbuttons etc. So I inserted a module and placed this declaration.
Option Explicit
Public MySh As Worksheet
Set MySh = ActiveWorkbook.Sheets("sheetName")
And I referenced it in a UserForm commandbutton
Private Sub CommandButton2_Click()
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i5") = Me.TextBox1
End Sub
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.
My code is below.
Option Explicit 'requires variable declaration
'declare global connection
Public cn As ADODB.Connection
Sub OpenDBConnection()
'open db connection
'this happens on workbook open
If cn Is Nothing Then
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
End If
End Sub
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.
explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms?
View 9 Replies View RelatedI've got several proccedures all inside a single module where I declare 7 variables as Public before the start of the first proccedure.
My structure is something like this:
Public v1 as Variant
Public v2 as Variant
.
.
.
Public v7 as Variant
Sub Proc1
Call Proc2 (defines v2 as name of a file opened by a user; inside Proc1)
Call Proc3 (inside Proc1)
Call Proc4 (inside Proc3)
Call Proc5 (inside Proc5)
End Sub
Sub Proc6
Here I use v2
call Proc7 (inside Proc6)
End Sub
Variable v2 is uniquely named and is only used to hold a file's name so that I can call upon that file in various procedures. However, after Proc1 ends, Proc 1 retains the variable names of some of my Public variables, but not all, including v2, which I need!
I've tried going through this all step-by-step, no joy/nothing apparently obvious. I've also turned on Tools-Options-General-Notify Before State Loss but this isn't generating anything either.
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..
I have a few lines of code as follows:
Private Sub Workbook_Open()
fileName = ThisWorkbook. Name
fileLocation = ThisWorkbook.Path
Dim strFound As Boolean, pos As Long
If Not fileName = "" Then
strFound = False
pos = 1
Do While strFound = False
stringFound = Mid(fileName, pos, 1)
If stringFound = "-" Then
productName = Mid(fileName, 1, pos - 1)
MsgBox "Name of Product Is: " & productName
strFound = True
Else
pos = pos + 1
End If
Loop
End If
End Sub
It would return "Test" if the file's name is "Test-Part1.xls". I then declare the productName variable as Public (Public productName As String) in one of the modules. But the productName only holds the value for a certain amount of time. After a while, it's empty.
I read the Excel VBA Variables Scope and Lifetime info, but I still don't understand b/c of the statement "It's value is retained unless the Workbook closes or the End Statement is used." Which "End Statement"?
I want to fill an array in a form module, then later use those values in a different form module. If I declare the public variable in a standard module, I know any of the modules can access it, but will it retain its value after form1 is no longer running?
If not, do I need to involve a class somehow? I know nothing about classes except that their variables retain their values as long as any of the members is running.
how do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.
Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer
Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text
I have Three questions about debugging VBA code of an excel add-in.
-Breakpoints set in certain Public Subroutines are NOT hit. I have an application that writes data to the add-in. You cannot debug the app + the add-in. The add does things(e.g. writes data), then calls a main public macro in the add-in, then the addin takes over. If I comment out the main macro, the parent app halts, then I try to set breaks in the add-in.
After resumeing, some breaks are never hit. I know this because if I set a stack trace (output ing the func name to a temporary worksheet), I see these functions being hit. All the modules are public.
Usually in C, breakpoints are not hit because that code is NOT part of the address space of the program.
2. Very naive question (I am a C,C++ programmer)...some tiimes when I hit F5, the whole app runs, other times a list of macros comes up (just as if I had gone to the menu and went toolsmacros. Why is this?????.....placement of the cursor?????
3. If in the app I have 5 code modules, with two public subroutines each.
HOW COME, IF I GO TOOLSMACROSRUN (same as what I get sometimes when I go F5), I ONLY GET A PARTIAL LISTING OF THE SUBROUTINES, ie NOT ALL 10?
This formula's array
=IF(COUNTIF($A$2:$B$12,$A$2:$B$12)=1,COLUMN($A$2:$B$12)-COLUMN($A$1)+1)
returns
{FALSE,FALSE;FALSE,2;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;1,2;FALSE,FALSE;1,2;1,2;1,FALSE;1,FALSE}
This is the column number for each TRUE value
What I want to know is when I drag the formula down how can I obtain the values from left to right? ie:
2
1
2
1
2
1
2
1
1
I am trying to store values into a dynamic array. The size of the array will vary each time, so I need the range to be dynamic. Most importantly, I need all values to be retained in the array. Currently, a value will be stored, but once the next round of the for loop is initiated, the array changes to "<subscript out of range>" and stays that way until the it is replaced by the next value. So, there isn't an accumualtion in the array--it goes one value, to out of range, to one value, etc.
View 8 Replies View Related