Hello. Not sure if this is possible but I'll give it a try. Let's say I have boolean variables named tab1, tab2, tab3, ... tabn. I know the code below won't work but is it possible to check the status of these boolean variables with a loop such as:
Thanks.
Please Login or Register to view this content.
Use an array:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks for your help. I should have been more specific. My defined boolean variables are actually something like sn_fs where 'sn' is the name of a sheet. So there is an existing boolean variable for each sheet, and that variable is set somewhere in the code. So now I need to construct the boolean variable by combining the sheet name and the literal "_fs", and then check that variable for true/false.
AFAIAA, you cannot do what you want to do in VBA. Perceived wisdom suggest using an array or a Collection.
As a workaround, you could have two arrays. The first would contain a list of sheet names. You could build a text string from your sheet name and match it to the first array. Then, you could update/check the status in the second array.
That's what I thought. Thanks.
Hi,
If you were to declare your variables within a class, you might use CallByName to access the variables by name.
Don
Please remember to mark your thread 'Solved' when appropriate.
@xlnitwit: can you provide a worked example that meets the OP's stated requirement to build a variable name so that he can loop through them and test the values for True or False?
Certainly. Using ThisWorkbook to host the code as an example class
Please Login or Register to view this content.
Thanks Mr. xlnitwit
I tried to test the code but I got an error at this line
Run time error 438Please Login or Register to view this content.
Object doesn't support this property or method
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
Hi,
The code must be in the ThisWorkbook module. Is that where you put it?
The code has to be in the ThisWorkbook Class Module, not a Standard/Normal module.
Thanks a lot for help
Can I put the code in standard module ..
I tried the following but got the same error
Please Login or Register to view this content.
The variables must be declared in a class module. They cannot be in a standard module.Originally Posted by YasserKhalil
Is this because of using CallByName ?!!
I need to know more about this function (CallByName)
Yes it is. CallByName allows one, as the name implies, to call a member, property or method of an Object using the name.
Thanks for reply and for great information
Why the code in post #12 failed ?! I refer to ThisWorkbook Object in CallByName Function ..??
Because the variables were not in the ThisWorkbook module and thus were not members of the object you were using.
Did you mean these variables?
They are declared as public so it is supposed that it can be used at any modulePlease Login or Register to view this content.
They can be used in any module, but they are not members of the ThisWorkbook object unless they are in the ThisWorkbook module. If they are not members of the object, they cannot be accessed using the CallByName function.
Thank you very much for your patience and for great illustration
Regards
You're most welcome.
A simple illustration that may also assist is to use Intellisense. If you were to type ThisWorkbook followed by a full stop, you would see a list of all the things you can access for that object when using CallByName.
Last point .. I have do some search to learn more about CallByName
I have this code
I got error at this linePlease Login or Register to view this content.
How can I get the item using CallByName?Please Login or Register to view this content.
I tried various typos for 1 .. "1" .. (1) >> but all failed
Item is a method of a Collection rather than a property, therefore you must use VbMethod
Please Login or Register to view this content.
Really awesome
I was confused because of the comment line which referred to "Item" as property
Thanks a lot for great help
Best Regards
Did you not write the comments?
No I got the code from this link (VB code)
https://msdn.microsoft.com/en-us/lib...=vs.90%29.aspx
This is the original code
I tweaked it a little to suit VBAPlease Login or Register to view this content.
A Collection in VB is implemented slightly differently to VBA, it seems.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks