# Off Topic > The Water Cooler >  >  My 1st Anniversary

## Blake 7

Guys - On Saturday 01 Oct it will be a year to the day since I joined the forum!!! 

Wow - how time has flown, and not one of my threads in the bad thread section and not one infraction! yeyyy

In that time i've learnt something about excel and met some great folks!

So on sat i'll open a Black Sheep, none of that Canadian Ally Kat stuff, and say cheers to EF!!!

Do I get something from Roy?

----------


## romperstomper

An infraction?  :Wink:

----------


## zbor

I can give you some.. Anyone else?

----------


## JBeaucaire

A free membership for another year!

----------


## snb

Unfortunately not a one-liner:




```
Please Login or Register  to view this content.
```

----------


## romperstomper

Is it sad if you can read that without running the code?

----------


## snb

Time for holidays.....

----------


## Blake 7

lol - 

I couldn't read it without running it.............will need a couple more years here before I can do that!

No candle?

----------


## romperstomper

> Time for holidays.....



I think you might be right there!  :Smilie:

----------


## Domski

Many happy conflabulations to you!!!





> Is it sad if you can read that without running the code?



I'm so ashamed  :Frown: 





> Time for holidays.....



Mine start at 4:00pm today  :Smilie: 

Dom

----------


## Blake 7

> Unfortunately not a one-liner:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Hi - may I ask what the j serves for in For j and then (Choose(j

Thanks

----------


## snb

You may, but it's 1 day too late: in your first year you are allowed to put this kind of questions, after your first anniversary you can only answer those (it's a mod's switch).

----------


## romperstomper

It's OK - the 1 year is up *tomorrow*.  :Wink:

----------


## Blake 7

ok - its decided that i'm still within the asking q's threshold and the answer is ..... ? pretty please with a sprinkling of 100's and 1000's

----------


## romperstomper

It's a loop variable that increments from 1 to 21 with each iteration of the loop.

----------


## tigeravatar

Blake 7,

Happy forum birthday!  May it be filled with excellence!

... Sorry, couldn't help myself  :Roll Eyes (Sarcastic): 

~tigeravatar

----------


## Andrew-R

Congrats on your 1 year, Blake.

Will you be changing your avatar from Liberator to Scorpio to mark this event?

----------


## Blake 7

No, 

Scorpio's' interior was designed by much less know Roger Cann whereas The liberator's interior was designed by Gok Wan! How to teleport n a k e d

----------


## romperstomper

Scorpio? I preferred Jet.

----------


## Blake 7

scorpio looks like "she" may have been born a "Steve" and Jet - well, i'd jump into her cockpit any day!

----------


## romperstomper

She did used to brighten up Saturday evenings...  :Smilie:

----------


## Andrew-R

> The liberator's interior was designed by Gok Wan!



Wikipedia is calling you a liar (it also spilled your pint and looked at your bird).

----------


## Blake 7

Damn those at wiki and thier Blasphemy.........

----------


## NBVC

I probably won't be online much tomorrow.. so I will bid you happy anniversary now  :Smilie: .  Many more happy postings to you  :Smilie:  

Cheers... have a Caffrey's on me  :Wink:

----------


## Domski

In case you want to relive your first tentative steps:

http://www.excelforum.com/excel-gene...ting-tool.html

Dom

----------


## Blake 7

who are you? micheal aspel..............

it was polite and courteous ! I was scared and intimidated!! I tried my best ! you know, i STILL dont understand what DO did!!

thanks for bringing that to peoples attn though Dom !! nice one, remind me to send you a handgrenade for xmas!!!!  :Smilie:

----------


## Domski

Very few people do  :Wink: 

Dom

----------


## romperstomper

> you know, i STILL dont understand what DO did!!



Nobody ever does (apart from DDL and NBVC)  :Wink:

----------


## Blake 7

> Nobody ever does (apart from DDL and NBVC)



You are being modest - DO has mentioned that he has run things past you before!

----------


## Domski

Yeah, generally what beer to buy  :Wink: 

Dom

----------


## Mordred

Scorpio scares the be-jangles out of me.  She could break me!

----------


## NBVC

> You are being modest...



I would definitely have to agree  :Smilie:

----------


## ChemistB

> In case you want to relive your first tentative steps:



That had me thinking about my first post so I went to Advanced Search> Chemistb >Find threads started by User
Alas it only goes back 5 years and I started in '04.

----------


## Colin Legg

> You are being modest - DO has mentioned that he has run things past you before!



Don't expect an _accurate_ reply to this from RS, Darren. He's far too modest to admit that he's being modest.  :Wink:

----------


## romperstomper

> You are being modest - DO has mentioned that he has run things past you before!



Code, maybe. Any formulas beyond A1+B1 leave me puzzled.  :Smilie:

----------


## Blake 7

Who is Darren? - I dont want my colleagues to discover the fountain of my knowledge!

----------


## Andrew-R

> Any formulas beyond A1+B1 leave me puzzled.



Yes, the Excel work I did for the blood transfusion service went badly wrong after a mix-up over terminology.

Those poor, poor people.

----------


## tigeravatar

@Andrew-R, that made me laugh out loud at work, haha

----------


## Mordred

LoL Andrew

----------


## Bob Phillips

> Hi - may I ask what the j serves for in For j and then (Choose(j
> 
> Thanks



First off you should make sure you never write rubbish code like that.

Any decent coder knows you should ALWAYS declare your variables, and give them meaningful names. Choose was wasteful, a=n array of values and a loop that picks from the array is better. Furthermore, anyone who has any empathy for their user would not inflict that many horrific MsgBox statements on them, they would better give up a nice user form with an aesthetic picture background, with a label that builds up the message in a timed loop, wait for a few seconds with the full message, then fade away independently.

Come on guys, there are standards to preserve.

----------


## Blake 7

I second that Bob, my mouse did get very upset that I hit him so many times in quick succession!

Not to mention my arthritis.

----------


## Whizbang

> First off you should make sure you never write rubbish code like that.
> 
> Any decent coder knows you should ALWAYS declare your variables, and give them meaningful names. Choose was wasteful, a=n array of values and a loop that picks from the array is better. Furthermore, anyone who has any empathy for their user would not inflict that many horrific MsgBox statements on them, they would better give up a nice user form with an aesthetic picture background, with a label that builds up the message in a timed loop, wait for a few seconds with the full message, then fade away independently.
> 
> Come on guys, there are standards to preserve.



I... Uh... Took this challenge as practice and things got out of hand.  For some insane reason I set myself the challenge of not uploading a workbook, instead have all of your requirements in a single sub.  The problem, as I quickly found, was that you said "user form" and "fade away independantly".  As you see from the code below, these were tall orders.  If anyone sees a better way to do this, I would love to see it.




```
Please Login or Register  to view this content.
```



P.S. You need to have the reference "Microsoft Visual Basic for Applications Extensibility X.X" activated in the VBE.

----------


## Blake 7

Hiya - im getting a "user defined type not defined error" on this line

I was so excited!!

lblNewLabel As MSForms.Label

----------


## Whizbang

You need to activate the "Microsoft Visual Basic for Applications Extensibility X.X" reference.

In the VBE, go to "Tools" ->  "References" and check that reference (It is all alphabetical, so you'll need to scroll pretty far down), then click "Ok".

***Edit***
Oops.  And the "Microsof Forms 2.0 Object Library" reference.

----------


## Blake 7

I could find this

Microsoft Visual Basic For Applications Extensibility 5.3.

but not xx

----------


## Whizbang

That is what you want.  The X.X was just because the number may be different depending on your version of Excel.

----------


## Blake 7

Ahhhhhhhhhh doh --- silly me - i'll get this thread in the bad threads thread at this rate. I'll have another crack and get back to you

----------


## Blake 7

WOW Dude - YOU ARE A WHIZZBANG! amazing!!!

----------


## snb

> Come on guys, there are standards to preserve.



Like being polite and congratulate someone in a thread that was started because of a member's 'anniversary'.

----------


## inayat

> You need to activate the "Microsoft Visual Basic for Applications Extensibility X.X" reference.
> 
> In the VBE, go to "Tools" ->  "References" and check that reference (It is all alphabetical, so you'll need to scroll pretty far down), then click "Ok".
> 
> ***Edit***
> Oops.  And the "Microsof Forms 2.0 Object Library" reference.



Whizbang... 

I activated the Applications Extensibility.... but I am still getting the error:
lblNewLabel As MSForms.Label 

I see that I have Microsoft Forms 12.0 object library activated.  But no 2.0... Any help...

----------


## romperstomper

I've never heard of an msforms 12 library - are you sure about that? If you insert a userform, you should get the forms2 library referenced.

----------


## romperstomper

Where's the fading away? All I can see is an unload? Also, Bob didn't actually say you had to _create_ the form in code.  :Wink:

----------


## inayat

Hi RS,

Please see attached.  My VB knowledge is nill so I just tried to follow the instructions.

Cheers-
Inayat

----------


## Blake 7

hi inyat - in the developer tab click macro security > in macro settings make sure that "trust access to the VBA project object model is checked

----------


## romperstomper

That's the Office 12 library, not the Microsoft Forms 2 library, which is the one you need.

----------


## inayat

@Blake... I checked that... Still it is giving the same error

@RS... I can not see the Microsoft Forms 2 Library in Reference... Do i need to look anywhere else... As I said, I have zero knowledge in VBA...

----------


## romperstomper

As I said, insert a userform and the reference will be added for you. (You can then delete the form)

----------


## Blake 7

Here are instructions in the case of missing ms forms 2.0 object library

http://support.microsoft.com/kb/208218

----------


## inayat

Thanks RS... Works perfectly now...

Thanks Blake....

----------


## Bob Phillips

> Where's the fading away? All I can see is an unload? Also, Bob didn't actually say you had to _create_ the form in code.



Why thank you MR rs. You just can't get the staff nowadays!

----------


## romperstomper

Hopefully this is more to your liking, Sir (shamelessly borrowed from VBAX):



```
Please Login or Register  to view this content.
```


No references required but you do need trusted access to the VBProject.

----------


## teylyn

What's the trick for getting this to work, RS?

See error message and trust center settings in attached screenshot.

----------


## romperstomper

The code needs to be in a _normal_ module.

----------


## snb

or 



```
Please Login or Register  to view this content.
```


In macro, sheet or workbookmodule

----------


## teylyn

> The code needs to be in a normal module.



Yup! 

F5 -- Wowwww!!!

----------


## Whizbang

Well, sure... If you use API's...

But seriously, these are great.  What fun!

----------


## davegugg

That's very cool rs, I'm going to definitely incorporate that into future projects at work!

----------


## Whizbang

```
Please Login or Register  to view this content.
```


Seriously!  That's all I needed to get rid of the reference?

Internet, you have failed me!  If not for the fact that you then provided the solution, I would disown you!  Get out!  I don't even want to look at you right now!

...

I didn't mean that, Internet. I got carried away.  I'm sorry.  You know I love you.

----------


## romperstomper

Yep.  :Smilie: 
People seem to get a little carried away with the 'need' for the Extensibility library, I find. It's just early vs. late binding after all.

----------


## ChemistB

RS,
I have the 2.0 Object Library and the 5.3 Extensibility checked but when I run the code (in a normal module), I get the attached error.  Debugging goes to that same line 


```
Please Login or Register  to view this content.
```


 :Confused:

----------


## romperstomper

You need to trust access to the VBProject in your macro security settings.

----------


## ChemistB

I had to search a little bit but I found it.   :Smilie:    Thanks!

----------


## Bob Phillips

All this because I took exception to some code. Must do it more often.

----------


## NBVC

More like, all this because someone decided to announce his 1st anniversary on the forum!  :Wink:

----------


## Blake 7

I must do it more often!

----------


## Whizbang

Do you think the community would be open to having challenges?  Meaning on a regular schedule (say, weekly) a programming challenge would be posted.  People could then post their attempts at a solution.  It would be up to the community to decide if they want to vote on the responses for a "winner" or if we'd just want to do it for the mental challenge and learning opportunity.  Maybe each week the "winner" of the previous week could post a new challenge.  Or there could me categories of winners (e.g. "Adheres to Industry Standards", "Fewest lines of code", "Overall", "Most Ingenious", etc.)

----------


## Blake 7

Great idea whizz - but instead of a programming challenge, how about I post my here an now REAL LIFE excel challenge and people post their ideas! Like Chandoos site! that way, everyones a winner!! yeyyyyy!!!!

----------


## Blake 7

Hey Boys - and Teylyn,,,,, I thought Bob's jibe at snb's sloppy code, snbs subsequent retort, the ensuing battle of the s e x y code which culminated in RS's masterpiece was good enough, but this thread is getting more interesting by the minute, I have just recieved positive feedback (for this thread) from someone (no names mentioned) who hasn't even contributed (to this thread)/ Wowser, can we all now crack on with whizz'z idea of sorting out my dashboard! this weeks programming challenge!!!

----------


## Mordred

Answering OPs questions can be difficult enough at times, never mind challenges from the guru's of this site.  Scary proposition  :Wink: !

----------


## Bob Phillips

A small revision, give it the personal touch




```
Please Login or Register  to view this content.
```

----------


## Blake 7

Hey Bob -This probably says Happy birthday.. but where in your marvellous code is owner!?

72, 97, 112, 112, 121, 32, 66, 105, 114, 116, 104, 100, 97, 121, 32

----------


## Bob Phillips

In




```
Please Login or Register  to view this content.
```


and then




```
Please Login or Register  to view this content.
```

----------


## Blake 7

Thanks mate - now i can send it to my son and change the name - he'd love it.

----------


## romperstomper

The code picks up the Windows username, so why not leave it?

Small tweak to add the name one letter at a time:



```
Please Login or Register  to view this content.
```

----------


## Blake 7

Hey RS - this does not run on my 64 bit system!! please, no more sloppyness!

----------


## romperstomper

That'll teach you to ignore Microsoft recommendations then.  :Wink:

----------


## teylyn

> All this because I took exception to some code. Must do it more often.



Seeing the sparks, heck, yes!!

----------


## Blake 7

Ok Capt Mannering - Don't panic, i've just opened up the beasts little brother a Precision 390 32 bit and it ran like a dream! 

My hero!

----------


## romperstomper

I don't have 64bit here to test, but I think this adjusts correctly:



```
Please Login or Register  to view this content.
```

----------


## Bob Phillips

> Small tweak to add the name one letter at a time:



I deliberately threw up the name all in one, seemed a good touch to me.

----------


## Bob Phillips

> Hey RS - this does not run on my 64 bit system!! please, no more sloppyness!



You have 64-bit Office? Are you a masochist?

----------


## Bob Phillips

> Hey RS - this does not run on my 64 bit system!! please, no more sloppyness!



BTW, can we have less sloppiness when spelling sloppiness!

----------


## teylyn

Bob, that was my line!

----------


## romperstomper

> I deliberately threw up the name all in one, seemed a good touch to me.



Seemed a bit abrupt to me, so now we have options.  :Smilie:

----------


## Blake 7

> BTW, can we have less sloppiness when spelling sloppiness!



oh dear - typical, me and my fudge fingers!

----------


## Blake 7

> You have 64-bit Office? Are you a masochist?



why would you say that Bob, what's wrong with 64 bit office? I have found it much quicker.

to answer your question, i'm probably more of a deviant than a masochist!

----------


## romperstomper

See this article.
Small excerpt:
"The 32-bit version of Office 2010 is the recommended option for most people, because it prevents potential compatibility issues with other 32-bit applications, specifically third-party add-ins that are available only for 32-bit operating systems."

----------


## Bob Phillips

> why would you say that Bob, what's wrong with 64 bit office? I have found it much quicker.



Even MS admit that 64-bit Excel is not the best way to go, yet unless you have a lot of data and using something like PowerPivot, needing to utilise all that memory.





> to answer your question, i'm probably more of a deviant than a masochist!



Then I think you should leave immediately, we don't have no truck with them thar types.

----------

