Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Can't seem to find this anywhere and it should be simple.
I have an array ActiveEmp "John", "Joe", "Bob", etc. I have variables named "UPD_John", "UPD_Joe", "UPD_Bob", etc. in looping through the array, I want to test the variable true/false If "UPD_" & ActiveEmp(i) = True Then "UPD_" & ActiveEmp(i) does equal "UPD_John", etc. but it's not recognized as a variable that I can test for true/false Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Can't seem to find this anywhere and it should be simple.
I have an array ActiveEmp "John", "Joe", "Bob", etc. I have variables named "UPD_John", "UPD_Joe", "UPD_Bob", etc. in looping through the array, I want to test the variable true/false If "UPD_" & ActiveEmp(i) = True Then "UPD_" & ActiveEmp(i) does equal "UPD_John", etc. but it's not recognized as a variable that I can test for true/false Any help would be appreciated. Thanks Wrong approach! Test the array elements to match contents of a string... Const sEmps$ = "UPD_John,UPD_Joe,UPD_Bob" For n = LBound(ActiveEmps) To UBound(ActiveEmps) 'note name change to reflect array contains more than one If InStr(sEmps, "UPD_" & ActiveEmps(n) 0 Then 'do stuff End If Next 'n -OR- ...to use a Boolean... Dim bEmpExists '... bEmpExists = InStr(sEmps, "UPD_" & ActiveEmps(n) 0 If bEmpExists Then... ...but I don't see any point in this type of double testing. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Typos...
Test the array elements to match contents of a string... Const sEmps$ = "UPD_John,UPD_Joe,UPD_Bob" For n = LBound(ActiveEmps) To UBound(ActiveEmps) 'note name change to reflect array contains more than one If InStr(sEmps, "UPD_" & ActiveEmps(n)) 0 Then 'do stuff End If Next 'n -OR- ..to use a Boolean... Dim bEmpExists '... bEmpExists = InStr(sEmps, "UPD_" & ActiveEmps(n)) 0 If bEmpExists Then... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Gary,
Thanks but maybe I didn't explain myself clearly in my original post If I have a variable named "ABCD" Dim ABCD as Boolean ABCD = True What I want to do is take two strings "AB" & "CD" and say If "AB & "CD" = True then.......... How do I tell VBA to look at that "ABCD" as a variable named "ABCD" and not just a string? My apologies if I'm just being dumb. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Gary,
Thanks but maybe I didn't explain myself clearly in my original post If I have a variable named "ABCD" Dim ABCD as Boolean ABCD = True What I want to do is take two strings "AB" & "CD" and say If "AB & "CD" = True then.......... How do I tell VBA to look at that "ABCD" as a variable named "ABCD" and not just a string? You can't! ABCD is the defined variable and so must be used as dimensioned! You can't assemble a string to replace it because that string occupies a different space in memory than that which was reserved when you dimensioned the variable. What I see here is a basic lack of understanding programming basics. Perhaps you'll be better helped if you explain the concept of what you're trying to do so we can example how to code for that! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Gary,
First of all, don't ever say that "You can't" when it comnes to Excel. I learned a long time ago that the proper answer is "I don't know how. Yet" Which is what prompted my initial question. Now what I really take exception to is your statement "What I see here is a basic lack of understanding programming basics". What gives your stuck up pompous ass the right to make any kind of judgement of someone that you don't know? 15 years ago or so I was very active in the Excel Newsgroups both asking questions and contributing. That was back in the Ogilvy, Ken Wright, Pearson, Walkenbach, Jan Karel, Tushar (to name a few) days. Never once did I, nor did I witness any of them or their peers make any attempt to degrade anyone that asked a question or get on their pedestals to profess how great they were (even though they were that great). Talking down to people on the newsgroups or professing your supposed personal prowess in Excel does nothing to help anyone and someone with a thinner skin than mine might be turned off and never come back if you were the first person that he/she encountered on here. Please keep that in mind the next time that you reply to someone. As for my issue, I found another way around it (there's always another way).. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
First of all, don't ever say that "You can't" when it comnes to
Excel. I learned a long time ago that the proper answer is "I don't know how. Yet" Which is what prompted my initial question. If your experience goes back as far as you claim it does.., then you should be well enough familiar with VBA to *know* how to use, and work with, variables and/or string values. "You can't" has to do with the VBA programming language, NOT Excel! As for your assertion "I don't know how. Yet"... True so long as VB[A] remains Excel's macro language, because it doesn't support what you're trying to do (..using strings as refs to dimensioned variables). Now what I really take exception to is your statement "What I see here is a basic lack of understanding programming basics". What gives your stuck up pompous ass the right to make any kind of judgement of someone that you don't know? Sorry if I offended you! Now that I know a little more about you, perhaps I should revise my statement to... "What I see here is that you have forgotten your basic understanding of the VB[A] programming language." ...as it's apparent your skin is not as thick as you profess it to be. Given that my statement was immediately followed with an explanation as to why "You can't!", I fail to see where/how I "degraded" you or "talked down" to you. Note also that I did NOT sling names at you nor make any inference to my own skills/experience with Excel or VB[A]! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
As for my issue, I found another way around it (there's always
another way). Are you going to share this for the benefit of others searching this NG? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
wrote:
If I have a variable named "ABCD" Dim ABCD as Boolean ABCD = True What I want to do is take two strings "AB" & "CD" and say If "AB & "CD" = True then.......... How do I tell VBA to look at that "ABCD" as a variable named "ABCD" and not just a string? I do understand your problem very clearly. It's an interesting question, but I haven't found a solution yet. I thought this code might work, but it produces a run-time error: Sub myTest() Dim UPD_Bob As Boolean UPD_Bob = False ' Evaluate produces a run-time error here! If ((Evaluate("UPD_" & "Bob")) = True) Then MsgBox "Bob has a value of True." Else MsgBox "No, Bob is false!" End If End Sub The Evaluate function only works with formulas, ranges, A1-style referenes, or defined names. For example, Evaluate(Sin(45)) produces 0.850903.... I'm not sure if VBA has a function that takes a string expression, converts it to a local variable name, and extracts that variable's internal value. Please do let us know if you found a solution or another work-around. Robert |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenating strings into a variable
Thanks Robert,
I had tried something very similar to the code that you posted but couldn't get it to work either. I did find an interesting thread on a way to do it he http://windowssecrets.com/forums/sho...el-Office-2003) but I didn't try that yet either. What I did do for my dilemma was negate the need to test for a variable at that particular point in my routine. Basically, I was checking the FileDateTime on a number of different timesheets to see if they had changed and set a flag (variable) so that I could update them later on. The cure was simply to use the FileDateTime as my trigger, run the code to update what I needed, save the new FileDateTime and be done with it. The array I had, steps me through all of the timesheets and I don't need those variables any more. No great revelations here so nothing to share that hasn't already been done thousands of times before. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating two variable fields into a static length text field | Excel Worksheet Functions | |||
Concatenating two text strings, formula is right, cell output is w | Excel Worksheet Functions | |||
How do I start new line when concatenating text strings in excel? | Excel Discussion (Misc queries) | |||
Concatenating 2 Character to form name Variable | Excel Programming | |||
concatenating strings to call a variable | Excel Programming |