Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 161
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenating two variable fields into a static length text field Young Programmer Lad Excel Worksheet Functions 2 April 17th 09 05:39 PM
Concatenating two text strings, formula is right, cell output is w Bojamin Excel Worksheet Functions 3 March 3rd 09 09:08 PM
How do I start new line when concatenating text strings in excel? Max Excel Discussion (Misc queries) 2 January 26th 06 06:48 PM
Concatenating 2 Character to form name Variable mozart[_5_] Excel Programming 2 August 26th 05 04:39 AM
concatenating strings to call a variable Frank Brown[_2_] Excel Programming 2 April 13th 04 03:29 PM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"