i have a variable that will be the same in every module within a workbook. i have tried playing with Global, Public, Conts, etc. but get compile errors.
essentially this is what i want... i just want to set the dte globally so i dont have to set it in each module.
Code:
Global dte As Date
If Weekday(Now()) = vbSunday Then
dte = Date - 2
Else
dte = Date - 1
End If
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'm using Excel 2000.
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"
View 4 Replies View Relatedproblems with Global Static Variables.
In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.
Here is what i did:
Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer
Private Sub Workbook_Open()
GlobalStartX = 6
GlobalStartY = 21
End Sub
I understand the use of Global variables in the sense that they can be used throughout the project. However, I also understand (I think) that unless these variables (like any) are released from memory or the values changed by some other means they will retain their values when accessed inside a another procedure regardless of where the calling procedure is Public or Private. This seems to be a convenient way to access the values of these variables without having to make ByVal or ByRef declarations in the procedure calling process.
Firstly, is my understanding of this correct? (That the variables and associated values) can be accessed from within a calling procedure without formally passing them in?
Second, even if this is true is it considered to be bad practice because the variables will store the last known value which may or may not be correct if you aren't paying attention to how the variable was last set.
I've been looking at code a lot here and at the end I always see people ending their VBA code by setting the variables to nothing. Why is this done? Don't all the variables in the sub automatically get trashed when the sub ends?
So what would be the difference of me doing:
Code:
Sub emailUser(strSubject As String, strMessage As String)
'Allows you to email the user if an error occurs instead of giving a messagebox
'and stopping everything. This way if something can not be checked out, you know
'what happened and can redo it the next morning (or fix the error if need be)
Dim olApp As Object 'Outlook.Application
Dim Msg As Object 'Outlook.MailItem
[Code] .....
vs
Code:
Sub emailUser(strSubject As String, strMessage As String)
'Allows you to email the user if an error occurs instead of giving a messagebox
'and stopping everything. This way if something can not be checked out, you know
'what happened and can redo it the next morning (or fix the error if need be)
Dim olApp As Object 'Outlook.Application
Dim Msg As Object 'Outlook.MailItem
[Code] ........
I have put together the below macro to take data from an Excel spreadsheet and place in a word documents (using bookmarks.
What should happen is it should open up the correct word template depending on what data is in column E (Servicecode) and fill in the bookmarks with the data from excel.
It all works fine and goes through the loop and saves, inputting the correct data into the bookmarks but after looking at the saved templates and scrolling through using F8 the IF ELSE does not seem to work. It CALLS theBearSSR macro rather than the relevant macro e.g. word in E2 is Cele, the macro should call the CeleSSR macro (which opens the correct template).
I don't think i have declared my DIM Servicecode correctly... I can't seem to figure out how to make the IF ELSE condition look at the Servicecode and then go to the correct sub routine (the difference between them is the template that is opened).
HTML Code:
Sub Celebration()
Dim departuredate As String[code]......
With Wb.strMyBookINT
Set S70Wscopy = Sheets("s70 pivot data") 'set s70 pivot data sheet for kpi 44 s70 pivot data
Set IMFWscopy = Sheets("imf pivot data") 'set imf pivot datasheet for kpi 44 imf pivot data
End With
With Wb.strMyBookEXT
Set IMFEXWscopy = Sheets("imf ex") 'set imf ex sheet for kpi 15 imf ex
End With
correct syntax to set these sheets?
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
I have "set .. with" blocks in some procedure. I want to make it global. How I do it? I have following:
Public cb As CommandBar
Public ctrlClear As CommandBarControl
Public ctrlInit As CommandBarControl
Public ctrlGUI As CommandBarControl
Sub CreateToolbar()
Set cb = Application.CommandBars.Add( Name:="auto", temporary:=True)
With cb
Set ctrlClr = .Controls.Add(Type:=msoControlButton, temporary:=True)
Set ctrlInit = .Controls.Add(Type:=msoControlButton, temporary:=True)
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
End With
End Function
Sub ButStatus()
ctrlClear.Enabled = True
ctrlGUI.Enabled = False
ctrlInit.Enabled = True
End Sub
I get "Object Variable.. not set."
I have a problem with an file that I have exported from another software program (I believe in CSV). The file seems to be in tekst format but I can not change it to date format. I need to know which weekday is which date. So january 1st is a tuesday and so on..
View 4 Replies View RelatedI have a general list from which a chart is created. Column one contains a date series. There are a fixed number of dates in this series (approx 50 or so).
I would like to select a start date and an end date from this list and have the chart display the data from that range.
Typically I would simply create a new list and build the chart from the list, but I'm kind of hoping that I could do it from a single source rather than make mulitple lists.
How do I force the Date and Time format when I import data into an Array that is listed in a listbox?
The desired format is YYYY/MM/DD hh:mm and I do not want to be dependant on the system settings.
I am Using an If statement to check if something has been verified in the last 30 days.
Code:
(TODAY()<=DATE(2011, 9,27)+30,"Yes","No")
Now I would like to create a button that modifies the Date(y,m,d) field to current date. How I could set the y, m, and d to the current values using a button in excel?
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
View 5 Replies View RelatedI've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
I am trying to write a query to pull data using Basis ODBC from our accounting system into Excel. I want to limit the results to a specific range of dates. I can hard code the start and end dates into the Critirea in QueryBuilder and get the results I want, but I would like to enable my users to enter variable Start and End dates in cells in the Workbook so they could select ranges at runtime.
I currently have a StartDate named range in cell C2 and and EndDate named range in cell D2. The data columns would contain fields for "Order Date", "Customer Name", "Order Total" and the like. I want to be able to limit by "Order Date" using the StartDate and EndDate variables.
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
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.
View 7 Replies View RelatedI 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.
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.
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 am trying to match up two data sets that are composed of some not very clean data. Some of my match criteria is going to include text strings.
One data set often has abbreviations for words. Example:
Massachusetts General Hospital
MASSACHUSETTS GENL HOSP
Or St vs Street. Mt vs Mount.
The list of criteria of subs I want to do is quite extensive so it does not make sense to try and do this with embedded replace() and substitution() formulas.
Want I want to is to write some kind of case statement in VB where I can load a list of substituions into a worksheet with the word in the A column, and the sub in the B column.
A b
GENL General
And then the code would find these words and sub them out whenever they are present.
Ideas on how to get started? Maybe there is a function out there already written that I can use? I searched a bit, but I am not familar with all of the resources out there. I don't need someone to write this for me, but just maybe a best practice on how you would, or point me to examples of similar functions.
Is there any way to select things like tools - options across all worksheets in a workbook? I often want to suppress display of zeroes in an entire workbook, but so far the only way I've found to do this is to go to eash worksheet in turn and go thru the procedure on each one.
View 3 Replies View RelatedI 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?
View 4 Replies View Relatedhow to set up a global varable over a workbook? Incognito439
View 5 Replies View RelatedI 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 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?
View 6 Replies View RelatedI have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?
View 2 Replies View Related