I have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA. I am trying to declare a global variable, assign it a value, then use that global variable. Within 'ThisWorkbook' I have the following...
Public myText As String
Private Sub Workbook_Open()
myText = "Hi There"
End Sub
...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox myText
End Sub
Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank. This is all fine, except I want the message box to state "Hi There". What am I doing wrong? Is the variable declared (publically) correctly? Am I assigning the public variable the value correctly?
Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?
I've been asked to produce a spread sheet that will calculate split payments for a list of households built by our company. The bill amount will be given to us by the power company, and the basis for bill separation is base on the billing period, and the date of house settlement.
The codes I've produced are written below, however I couldn't get it to run properly. The software keeps on telling me that my variables aren't declared correctly.
I've attached all my codes below for your reference.
Public Start_Date As Date Start_Date = Worksheets("Sheet_Input").Cells("D", 1).Value Public End_Date As Date End_Date = Worksheets("Sheet_Input").Cells("F", 1).Value Public Total_Date As Integer Total_Date = Worksheets("Sheet_Input").Cells("H", 1).Value Public aAverage As Integer aAverage = Worksheets("Sheet_Input").Cells("B", 3).Value Public bAverage As Integer bAverage = Worksheets("Sheet_Input").Cells("D", 3).Value
There are many fields like Shipping, Shipping-UI, Shipping-BE.
I want the formula to consider all the above when severity_3= Shipping and take the count & not only consider Shipping(Shipping*) .
2.Formula test = "0 - Unclassified" Or "Alpha Testing" Or "Beta Testing" Or "Functional - Interface" Or "Functional - Report" Or "Regression Testing" Or "Requirement Review"
when i use the above formula , it is displaying the message "Run-time error'13':Type mismatch There are spaces in the criteria i have used but i am not sure how to resolve this
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.
The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.
I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.
If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use
Dim intRows As Interger But now how do I make it global.
I have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?
I maked a userform who use global parameters (to let the user decide where write things) and later i need to use this parameter in a module (who is the main program). I try to resolve this problem put this global parameters like global parameters in the module and later in the both sides (in the module and in the useform), but it can't work. How i can resolve that?, i.e., How i can use a global parameter in a userform and the same global parameter in the modulo with the same data?
I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?
I have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"
how to declare&initialize a variable as Global in vba?
I have a variable , listGroup=Array("aaa","bbb","ccc") now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.
I need to declare a global variable in Application level not Module level.
I have declare a variable in Module1 and then Module2 with same name then complile it and get success. That means there has two variable with same name in different Module. I think this was not a proper global variable declaration by which I can allow to declare only one variable in all Module, Class every where. I have used code as below -
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code: Private Sub CommandButton1_Click() If TextBox1.Value = blank Then 'Need name for processing MsgBox ("Name must not be blank.") Exit Sub End If If Len(TextBox1.Value) > 12 Then
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:
I have some code that runs a mouse event and keeps my computer active when I'm away. It works on my 32-bit computer, but doesn't work on my 64-bit. What is the correct syntax to have it work on a 64-bit?
I get the error: "declare stmts must be updated for 64 bit and marked with PtrSafe attribute."
BTW, I already tried changing "user32" to "user64", that didn't work.
Code follows:
Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long) Public Const MOUSEEVENTF_LEFTDOWN = &H2 Public Const MOUSEEVENTF_LEFTUP = &H4 Dim TimerActive As Boolean Sub KeepWindowsActive()
I suspect the array variables declaration in the following example is the problem. The array Function MyRoots() incorrectly returns 0.0 results to cells I11:J13.
cells I11:J13:: array function {=MyRoots(B11:C14, B8, B9)}
Function MyRoots (a, m As Integer, polish As String) ReDim a(m + 1, 2) As Double ReDim roots(m, 2) As Double Dim j As Integer, its As Integer Dim x(2) As Double ReDim ad(m + 1, 2) As Double ......................................my code............ For j = 1 To m + 1 ad(j, 1) = a(j, 1) ad(j, 2) = a(j, 2) Next j ......................................my code............ Call Laguer (ad, j, x, its) ......................................my code............ roots(j, 1) = x(1) roots(j, 2) = x(2) ......................................my code............ MyRoots = roots End Function
I am using the format of using variables for declaring file paths and document names in VBA however I want to make sure I am doing it properly...
Dim Server, VersionName As String Server = Worksheets("Calculation Matrix").Range("CalculationMatrix_Server").Value VersionName = Worksheets("Calculation Matrix").Range("CalculationMatrix_VersionName").Value
ActiveWorkbook.SaveAs Filename:="" & Server & "uploadcomplete" & VersionName & "", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False Should these variables be declared as String?
I am trying to run a VB script that will copy data from 3 worksheets and past it into 3 New worksheets, sort that data into alpha order then delete all rows containing Zeros that may be found in Column B, Then copy that data from the 3 New worksheet back into the original 3 worksheets.
The copying and sorting of the data from one sheet to another is easy. The problem is trying to delete the zeros found in column B once its been copied over into the 3 New worksheets.
The script I am using to delete any Zeros found in Column B is below. If I try to use this script more then once, so that I can delete all the zeros found in column B on all the New worksheets I get this error “Duplicate declaration in current script”
Is there a way for me to refer back to this script instead of me trying to past it more then once within the VB script.
Application. ScreenUpdating = False Application.Calculation = xlCalculationManual Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long Col = ActiveCell.Column StopRow = Cells(Rows.Count, Col).End(xlUp).Row Dim cnt As Long For cnt = StopRow To StartRow Step -1 If Not IsEmpty(Cells(cnt, Col)) Then If IsNumeric(Cells(cnt, Col)) Then If Cells(cnt, Col) = 0 Then Rows(cnt).Delete End If End If Next cnt Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True
Please see attached workbook. You will understand it better once you see the workbook and the VB script in it.
I have the following code which performs the same action on 2 different tabs within the same workbook:
Dim Rng As Range, Dn As Range Set Rng = Range(Range("O1"), Range("O" & Rows.Count).End(xlUp)) Rng.NumberFormat = "@" For Each Dn In Rng If Len(Dn) < 7 Then Dn = String(7 - Len(Dn), "0") & Dn Next Dn
i get the following error:
Compile Error: Duplicate declaration in current scope
I am trying to automate the creation of 3 pivot tables. At first I was having a problem with deselecting all items in the drop-down except one, then I found the code to fix it.
This fixed the problem, but there are still two other pivot tables that need to be created after this first one. I scrolled down through the code and deleted the other code blocks for the selection of the PivotItems and replaced it with the code above, but then when I ran it I got the "Compile Error: Duplicate Declaration in current scope".
I read more through the forum and realize that it's because VBA is dimensioning it twice, and I read that you're supposed to Dimension at the beginning of your program, but how do I implement this into my coding?
I can't dimension literally at the start of the code, there is some formatting that needs to take place first. (Basically I paste in a bunch of data into Sheet1 of my workbook, center it, space it out, then insert a pivot table into the pre-existing Sheet2 based on that data, filtered on the blank entries in Column N. Then I need to create ANOTHER pivot table based on that same data in Sheet1, but filtered on the blank entries in Column O. Then I center the words in the Pivot tables' headers in Sheet2, and finally I need to create one last pivot table on pre-existing Sheet3 based on the data on Sheet1, filtered for 2 specific entry types in Column N.)
I have written a code for a function in VB editor in a sample excel sheet. The UDF is working fine, but I am not able to use it in every other worksheet. I need to give the code to my managers, so even they can use the function conviniently just from the formula bar. I had declared the function as a Public function, so I thought it will be accessible in other excel sheets as well but that doesn't seem to work.
I have a holiday chart which has a summary at the end which counts holidays and sickness etc. It is in a workbook with a worksheet for each month, how can I define a range name that i can use with all worksheets, at the moment I am having to define new range names for all of the sheets and this is taking some time?
When I am in the new worksheet I can click on the range names in the top left but when i select one it takes me to the worksheet where the range is.
