Public Variable Loosing Reference
Mar 14, 2008
I've got the following code, setting up and initializing a Public variable for a sheet, but I've found that it's has no value after the Auto_Open is run,
though it works fine in the procedure Auto_Open calls.
I can get around it by reSetting it in the module I need it in,
but that kind of defeats the purpose of having a Public variable:
code in Module1
Public parSheet As Worksheet
Sub Auto_Open()
Set parSheet = Worksheets("Params")
Call Initialization.BuySellInit
End Sub
View 8 Replies
ADVERTISEMENT
Jul 24, 2013
The spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
View 9 Replies
View Related
Feb 12, 2008
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.
View 2 Replies
View Related
May 26, 2014
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?
View 8 Replies
View Related
Jul 8, 2008
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
View 9 Replies
View Related
Oct 28, 2009
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 Related
Feb 22, 2010
I'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.
View 9 Replies
View Related
Feb 10, 2007
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.
View 4 Replies
View Related
Feb 17, 2007
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.
View 9 Replies
View Related
Aug 20, 2014
I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.
At the end of the private sub for the ok button click I have this:
[Code].....
a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:
[Code] ....
When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.
View 3 Replies
View Related
Mar 6, 2010
How 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'
View 9 Replies
View Related
Feb 10, 2007
I want to define a varible named MonthEnd that I will use in more than one project. In a normal example the variable would look like this:
Dim MonthEnd As String
MonthEnd = Format(Sheet1.Range("C3"), "MMYY")
The problem is that I will be using this more than one time so I figured I could define this a Public constant like
Public Const MontEnd As String = Format(Sheet1.Range("C3"), "MMYY")
View 3 Replies
View Related
Jul 27, 2007
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
View 9 Replies
View Related
Jan 9, 2007
I located combobox on userfom and don't see it in the module.
View 7 Replies
View Related
Jan 29, 2009
I have an Excel 07 spreadsheet containing multiple tabs, modules, and userforms.
In Module1, I have a public variable declared as boolean - will call it X. When X is selected from a combo box in Userform 1, X is set to TRUE. However I've noticed that when the user enters Userform 2, X is somehow set to FALSE.
I can't set it back to TRUE at that point because more often than not, it SHOULD be FALSE, thereby sending the macro down a different path. Any idea how I can retain the "TRUE" value for X. I've tried changing the Public Variables to Global, but am still having the problem.
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Aug 14, 2012
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].....
View 3 Replies
View Related
Jul 27, 2009
how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.
For example, in the following code... I'm looking for an answer to the question posed...
Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '
View 9 Replies
View Related
Nov 30, 2006
trying to get it to put the 2 variables into 2 cells in the worksheet for the moment!
The Forms code is:
Private Sub CmdCancel_Click()
Me.Hide
Unload frmMsgBox
Exit Sub
End Sub
Private Sub CmdOK_Click()
Me.Hide
End Sub
It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.
View 4 Replies
View Related
Jul 8, 2007
I have the mask edit box functioning quite well. However, when the data is transferred from the userform to the worksheet, I cannot get the data to format to the preset format of the cell it is populating. The phone number displays as ########## instead of (###) ###-#### as it is formatted to do. Here is an excerpt of the transfer code I am using:
Private Sub CommandButtonAdd_Click()
Application. ScreenUpdating = False
ActiveWorkbook.Sheets(" Roster").Activate
Range("C1").Select
Selection.End(xlDown).Select
View 7 Replies
View Related
Sep 5, 2007
I want to type some data into a text box on a form, after I press enter I want to do sothing with the data (add it to a list box, no problem) and then go back to the textbox for another entry.
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call processdata
End Sub
Sub processdata()
A = TextBox1.Value
' Do something with A
TextBox1 = ""
TextBox1.SetFocus
End Sub
I can do somthing with the data but it does not jump back to the Textbox i.e. the setfocus does not work.
View 4 Replies
View Related
Jun 13, 2008
I have problems in PASTING my 19 digit number from the source report into excel.
E.g, the original value is 8321515222222123122 but it always transfer to 8321515222222120000
I have tried the simple cell format setting, that is after pasting, I set the cells to text, but it doesn't help and also try the custom fomat "###################", but it is still the same result with the last 4 digit lost.
What I want the format to be is not in scientfic and have to be full display.
View 4 Replies
View Related
Dec 8, 2008
I have a list of employees in a spread sheet. I am trying to add a macro that will add to the bottom of the list. I have managers that are not familiar with excel so I do not want them to just add lines. I have written a macro that will add lines or subtract, but I want the macro to always add at the bottom of the speadsheet.
View 9 Replies
View Related
Mar 3, 2008
Is there any way of referring to a variable name based on the value of another variable? I have the following code: (where the value for "aux" is defined somewhere else)
Dim aux As Integer
Dim number1 As Integer
Dim number2 As Integer
Dim number3 As Integer
If aux = 1 Then number1 = 1
If aux = 2 Then number2 = 1
If aux = 3 Then number3 = 1
The variables "number1", "number2" and "number3" are always filled with the same value ("1"). Is there any way of writing the "If-part" of the code in only one line? I was thinking that maybe there is a way of calling "number1", "number2" or "number3" depending on the variable "aux". For instance writing something similar to: number + aux = 1
View 4 Replies
View Related
Jul 4, 2014
When i save the worksheet cells are loosing links to other cells. what is even more surprising it happens only for 6 cells in 6 sheets.
scheme of links
Basic data
P1
P2
P3
P4
P5
P6
On the sheets P1-P6, each cell A1 has a link to Basic data.
View 2 Replies
View Related
Jan 29, 2013
I'm loosing the validations that I set up such as having a dropdown list and so on when I save and reopen the file. First I thought I didn't save it, but notice it happening consistently.
View 3 Replies
View Related
Apr 11, 2007
I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown.
Do While Not Selection. Offset(i, -6) = ""
ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""ħ""&round(R[i]C[-7],2)"
ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""ħ""&round(R[i]C[-6],2)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
View 9 Replies
View Related
Apr 17, 2007
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?
For example: ...
View 6 Replies
View Related
Sep 1, 2007
Dim vCriteria3 As String
vCriteria3 = ActiveCell.Text
Sheets("CUSTOMER OVR REVIEW").Select
Rows("25:25").Select
Selection.Copy
Rows("26:" & vCriteria3).Select
ActiveSheet.Paste
it hits the
Rows("26:" & vCriteria3).Select
and crasses with a "incorrect type" mesage. am i storing this into memory incorrectly? how do i fix it? the function is intended to use a variable to paste over x amount of rows.
View 5 Replies
View Related
Feb 18, 2008
Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.
Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.
View 4 Replies
View Related
