Global Word Substitutions
Dec 18, 2009
I 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.
View 9 Replies
ADVERTISEMENT
May 26, 2006
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
View 2 Replies
View Related
Sep 18, 2013
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 Related
May 27, 2008
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.
View 10 Replies
View Related
Jan 22, 2007
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.
View 9 Replies
View Related
Dec 13, 2008
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.
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
May 13, 2006
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 Related
Oct 20, 2006
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?
View 4 Replies
View Related
Dec 3, 2007
how to set up a global varable over a workbook? Incognito439
View 5 Replies
View Related
Dec 25, 2007
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?
View 3 Replies
View Related
Jan 1, 2008
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 Related
Aug 13, 2009
I 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
Dec 7, 2008
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?
View 4 Replies
View Related
Jul 31, 2009
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 Related
Jan 27, 2010
I have never used or created a Macro before. I have worked out how to record one etc. What I would like to know is, is it possible to design a macro and whenever an excel workbook is opened it will list all the Macro's you have created so that you can just action on any sheet or work book.
View 8 Replies
View Related
Jun 3, 2009
I am a wood logs trader. Wood logs are being sold per cbm (cylindrical volume). Each log has its own number, length and diameter. Once you bought the parcel, the owner of the forest gives you the listing of the logs. Nothing to it, it's just a list of all the logs with their specifications and numbers. We load these logs into 40' containers. Usually, we measure the logs while loading but not anymore, now the logs have already been measured and are being loaded randomly, hence my problem. Let me give you an example, let's say that I bought a parcel of logs of 100 pieces, marked with numbers from 1 to 100 for a total of 100 cbm. Each log has its own length and diameter of course (like 1 - 10m length - 42cm diameter . 2 - 6 - 52 etc...). The first container has been loaded with 5 pieces: number 6, 15, 52, 74, 75.
My question is, if i input the list of all the logs, would it be possible to create the packing list per container just by typing the log number and letting excel get the length and diameter automatically from the global listing? Because, let me tell you, getting through 2000 numbers to find the length and diameter of each of 15 logs loaded in one container, and thus for 50 containers really ain't fun
I've attached an example of packing list for one container to give you an idea (don't pay any attention to the color, it's just different qualities). I didn't attach any global listing though. The only difference is that it is much longer and in numerical order.
View 3 Replies
View Related
Jul 28, 2014
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
View 3 Replies
View Related
Feb 10, 2009
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.
View 9 Replies
View Related
Dec 13, 2006
Im using the following code on a userform to link the spinner and a textbox:
Private Sub Marketing_spn1_Change()
'ensures the text box changes when spinner is changed
Marketing_txt1.Text = Marketing_spn1.Value
End Sub
Private Sub Marketing_txt1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' ensure value typed in is valid
newvalue = Round(Val(Marketing_txt1.Text), 0)
If newvalue < Marketing_spn1.Min Then newvalue = Marketing_spn1.Min
If newvalue > Marketing_spn1.Max Then newvalue = Marketing_spn1.Max
' set the spinner value to keep in step with new value
Marketing_spn1.Value = newvalue
' set the textbox to the appropriate value
Marketing_txt1.Value = newvalue
End Sub
I have many spinners and textboxes with the same name (marketing_txt2, marketing_spn3 etc) on both this userform and on other userforms I shall be doing.
Is there any way of just writing this code once, rather than copying and pasting it for each spinner/textbox?
View 3 Replies
View Related
Jan 20, 2007
problems 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
View 4 Replies
View Related
Apr 27, 2007
I'm working on a project that involves scheduling participants. The good news is that it doesn't have to be complicated at all. Here's some info.:
Each event can either be one person or a group of people. Also, one person could be in more than one group. The event can be either a brass, woodwind, percussion, strings, or vocal. Events are scheduled at 10 minute intervals. Since we don't take time requests, what I need is something that will plug in events (sorted by the type of event) to time slots. (Each event takes place in a different room) All it has to do is avoid scheduling a participant at the same time and there needs to be 20 minutes between when a particpant can perform again. (In other words, if they are scheduled at 8:00AM, they can't be scheduled again until 8:20.)
On the spreadsheet, Column A is the event name, Column B is the Event type (Brass, woodwind, percussion, strings, or vocal) and column C are all of the participants involved. I don't know much about VBA so this may be too much work for this kind of foum but the folks on this board always amaze me.
View 4 Replies
View Related
Mar 7, 2008
How do you get macros to be universally accessible as functions without using the "personal.xls!" prefix? For instance I have the macro testmacro(), and if I do "personal.xls!testmacro(...)" it works fine, but if I just do "testmacro(...)" it gives me a "# NAME?" error. I have PERSONAL.xls (which is in the XLStart folder) as a hidden window. I tried unhiding and re-hiding it
View 5 Replies
View Related
Sep 17, 2009
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 -
View 9 Replies
View Related
Mar 24, 2014
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.
View 2 Replies
View Related
Nov 22, 2006
How could I have a macro which translates formulas on file open since Excel doesn't do it itself for functinos from add-ins like YEARFRAC?
View 9 Replies
View Related
Nov 8, 2006
I have a button on my spreadsheet which activates some code, it has worked perfectly for over a year but now for some reason I cannot get it to work.
When I click on the button now I get the message: Run-time error '1004' Method ' Range' of object '_Global' failed.
the file is far too large to attach here but here is the relevant ...
View 8 Replies
View Related
Apr 23, 2007
Working with named ranges is new to me, and very aggrevating to me too! I am trying to create a Named Range inside a worksheet_change event that is Global. I can create it, but it's always local to the sheet where the change event is happening.
For example:
Names.Add Name:="RandomName", RefersTo:=Target
...creates a local name instead of global one (whereas it would be global if it was created in just a normal subroutine).
Aside from using this trick: Change refersto property to workbook-level
View 8 Replies
View Related
Jun 22, 2008
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.
View 5 Replies
View Related
Jul 6, 2009
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
View 2 Replies
View Related
