# VBA testing



## dk (Dec 10, 2004)

Today I interviewed a couple of guys for a contract role maintaining and developing an Excel add-in I've written whilst I spent Christmas getting pissed in the mother country.  My boss asked me to get in someone who "has a very good handle on VBA".  I thought the best way to do this was to chat with them and then ask them to complete a test   

I wrote the test expecting that an intermediate to advanced VBA programmer (i.e. someone who we'd want to do the role) would be able to answer at least 5 questions from this.  One scored 2 and the other 3 out of 8.  The test was carried out *without* a computer i.e. it should all be on paper.

Now, my question.  The test can be found here (it's a Word doc).  What do you reckon?  Is the test a good test of reasonable level of VBA/SQL or is it too hard?  Appreciate any feedback  and let me know your scores if you want.

By the way, I'll post the answers later if anyone needs them.

Dan


----------



## P Sitaram (Dec 10, 2004)

Well, I found it reasonably easy. My score will probably be more than 5.

Do I get the job?


----------



## Zack Barresse (Dec 10, 2004)

Hey Dan,

Nice test.  

Sorry to say, I only got 5.  Had trouble with the Database/SQL, Class Module and Array question.  I just don't work with them enough.  The others were fairly descent (easy ? ) questions.  Couple made me stop and think.  

Okay, time to grade me:

1) Change to Long, not Integer.
2) ---
3) I'd guess c, but don't know.
4) c
5) =R[-4]C-R[-2]C
6) b
7) I'd guess b, but don't know.
8) b

.. so, are the other 5 right?    

Overall .. short, sweet and to the point.  A good test imho.


----------



## adaytay (Dec 10, 2004)

Hi,

Well, I've actually also been doing a few interviews recently as I'm moving to a different part of the country and need to get a replacement in.  I also tested my applicants, but backed the written up with a simple practical assessment (ie - here's a workbook - finish it off!)

Your test is set at a more advanced level than mine.  I wasn't interested in HOW technical they were - more what kind of overall grasp did they have of VBA.  So they were asked to give explanations of some simple (and some advanced) commands, what certain things did etc.  At the end of the day, whoever comes in will be supporting my system so my main concern is that they can understand code - and why it does this and that.

I consider myself to be an intermediate VBA coder (hell, I've written about a dozen systems, some that carry out very complex operations) - but I had problems with some of the questions.  I think some of them were too advanced - but then that's probably because I probably only got one or two of the answers   

But then I dabble and can build systems but I'm not a code expert.

Just my $0.02...

Ad


----------



## Richie(UK) (Dec 10, 2004)

Hi Dan,

Well, it seems a reasonable test.  

Not everybody will have had cause to use Application level events and not everybody will have used Access (I passed on number 2 as I don't use it   ).  That said, scores of 2 or 3 correct out of the 8 questions posed doesn't sound very promising.  

I suppose it depends how far you want to take the testing - you might avoid the results being skewed by having a larger number of questions, but where do you draw the line?  Or you could pose a small problem and ask them to write the code (there and then, not go home and ask for a solution on one of the forums!) to solve it. 

There may also be an expectation gap here.  Remember most Excel users haven't even got a clue that VBA exists, let alone know how to write in it.  Therefore, anybody having a basic understanding may genuinely think that they are appropriate for the role that you are seeking to fill.  Perhaps you could have applicants complete the test *before* you decide whether to invest any time interiewing them.

Good luck.


----------



## litrelord (Dec 10, 2004)

I think I did OK with that. As with everything though it just depends what you've done before. There's probably loads in your add-in which I haven’t looked at before. 

So, do you still get the mark for a question if your answer is "I don't know but I could find out on MrExcel"?

There's nothing wrong with using avilable resources after all.

Nick


----------



## dk (Dec 10, 2004)

Thanks for the replies lads (that was quick!).


To psitaram:
Mate, get yourself to Sydney this weekend and you've got yourself a job 



To firefytr:
Thanks Zack.  5 would have got you the job :wink: 

Answers:


Yes, changing integer to Long is correct.  This stumped both candidates.
One possibility: SELECT * FROM Table1 UNION ALL SELECT * FROM Table2;.
c is correct.  The WithEvents keyword is the vital part.
b, c and d are all valid answers.  a is only relevant for object variables.
Correct  - =R[-4]C-R[-2]C
Yep, b is correct
b it is.  Statement a would fail if you had an array that was anything other than 0 based.  The other are made up.
Again, b is correct.

To adaytay:
Well it sounds like we're trying to do similar things.  The only difference is that this person will be here for just a month, will need to get up to speed very quickly and will be asked to do a lot of work i.e. there won't be much time for a relaxed handover.  The questions were representative of some of the code that exists in the add-in and some of the problems they may face during the add-in's test period.

To Richie(UK):
Some good points Richie me old china.  In this case, the add-in makes quite heavy use of application level events so it is quite relevant.  Also, the add-in relies on an Access database for data e.g. historical ledger data, previous year budget and so on.  That said, if they got the other questions right and not these then that wouldn't have been an immediate 'no'.



			
				Richie(UK) said:
			
		

> Therefore, anybody having a basic understanding may genuinely think that they are appropriate for the role that you are seeking to fill.



This is an excellent point.  I interviewed another guy yesterday who could record and edit macros.  He seemed genuinely surprised when I said that we needed someone who could _write_ code.  He said that he had always been considered a guru.  Re your point about making them do the test before interview - just not possible at short notice given the need to ensure they didn't use help to complete it.

Anway, again, thanks to you all for your useful replies.  I'm meeting another guy on Monday who looks very promising.  I feel comfortable that the test is reasonable so will still use it.

Have a good weekend   

Dan


----------



## Norie (Dec 10, 2004)

Dan

Surely there is more than one answer to the first question?

1. Type the variable as  Long

2. Don't type the variable

3. Type the variable as Variant.

On question 4 could you not use this:

If strName = ""  Then MsgBox "No name entered"

All a matter of taste/preference I suppose.


----------



## litrelord (Dec 10, 2004)

> Surely there is more than one answer to the first question?



True, but any of those will be using more memory than declaring the variable as long.  

Although, as was mentioned on this board and elsewhere before, you’re better of using long instead of integer anyway since if you use integer then VB has to covert the integer to long, then run the code.  

Apparently anyway.

Nick


----------



## Norie (Dec 10, 2004)

> True, but any of those will be using more memory than declaring the variable as long.


I know but memory/performance wasn't mentioned in the question.

If are striving for efficiency obviously you need to take in to consideration such things. 

But if your just writing a one of piece of code then I think it's OK to skirt such issues.


----------



## dk (Dec 10, 2004)

Norie said:
			
		

> Dan
> 
> Surely there is more than one answer to the first question?
> 
> ...



Yes, you're right!  I guess they would have been valid answers.




			
				Norie said:
			
		

> On question 4 could you not use this:
> 
> If strName = ""  Then MsgBox "No name entered"
> 
> All a matter of taste/preference I suppose.


'

You certainly could.  The question was in no way meant to be a complete list.  You could also use:


```
strName = InputBox("Please enter your name")
    On Error Resume Next
    Dim a
    a = 1 / Len(strName)
    If Err.Number <> 0 Then MsgBox "No name entered"
```

or many other variations.

Anyway, goodnight!
Dan


----------



## litrelord (Dec 10, 2004)

Don't get me wrong, I'm not arguing with your logic. There's no doubt you're right.  I just meant that for a test at a job interview it would be a little like turning up in ripped jeans and dirty trainers when the position will be in an office where everyone wears a suit.


----------



## Tom Urtis (Dec 10, 2004)

Hi Dan - -

Your post is coincidental for me.  As you probably know, I own an Excel & VBA development company here in silicon valley.  The CFO of one of my client companies called me recently, really frustrated because he just hired someone who told him during the interview (as most people do) that they "know Excel", and it turned out on this person's first day on the job that they had never entered a formula before or written any VBA code (such as loops or sheet events or anything outside the realm of the macro recorder).

Business moves at lightning speed and these days employers do not have time to train anyone in anything technical, as you point out.  In fact, most employers themselves do not know some of what they need to know.

Anyway, this CFO told me he commiserates frequently with other financial professionals in the SF Bay Area and elsewhere, and they all have the same need for a tool that evaluates a person's Excel skill level.

I am developing a skills test for my client companies in the financial sector to evaluate their applicants, so here are some observations of your test, which is an excellent idea in principle:

(1)
Your test is a bit difficult because it relies on people to know what your company needs them to know, the way they you imply they need to know it.  Example, I never come across that run time error (#6 in question 1) because I always declare rows as Long.  In my opinion, a better way to ask that question is:
"You want to identify the last used row.  Which variable type is best?"
a) Object
b) Double
c) Long
d) String

As Norie and others pointed out, tests work best when their multiple choice possibilities contain only one viable answer. 

I scored 8 out of 8 on your test but I guessed wildly on #2 and got lucky, because I rarely utilize SQL.   People who truly know about Excel's memory issues as you noted earlier, would ask themselves reading Question #1 why looping through 40,000 cells is being asked in the first place, when a filter would be more efficient for "Flag", and then bolding for visible cells.  I tend to cut people a little slack who are not like us (good for them) that do this stuff 14 hours a day.

(2)
Not that it came up here, but different Excel versions determine how people approach some solutions.  The steps to create a Pivot Table are slightly different in 97 than 2003.   Therefore, I find it's important to let people come up with a correct solution, even if it's not the most correct one, especially if the chair is only being filled for one month.

(3)
It might help to structure sets of questions that require beginning, intermediate, and advanced expertise.  The potential pool of job applicants is never static or perfect.   At any given time there may be no available person looking for a job who is beyond the beginner or intermediate level.  At least if you know that about them, and they exhibit a conscientious nature, you can plan your month around their skillset without being surprised on Day 1 about having relied solely on their skillset.

(4)
I totally agree with your testing approach in principle.   Personally, I never listen to people who say they know Excel, or they took this certification test or read that book, or that they are widely recognized as their office's Excel guru.   Maybe I'm too skeptical or too hard, but I am not impressed with most Excel "certification" evaluations or the people who composed them.   There is too much money and risk in business to rely on anything other than what you are sure is a proper evaluation.

The testing you have in mind is a great idea and something I am developing also for a wider audience.   I'll be happy to share ideas with you or anyone about how I am doing it.   I will be marketing my test in 2005, starting with the financial and placement hiring communities here in California.


----------



## just_jon (Dec 10, 2004)

Dan,

I got 6 ( 7 if you consider a slight gaff in the SQL UNION syntax ), so I don't think the test was that extreme a challenge. Hope you get a keeper.

And, Tom -- be interesting to see your test when it's done. There are so many facets to XL/VBA, that I literally learn something new just about every time I browse the threads here.


----------



## Cbrine (Dec 10, 2004)

I got 6 out of 8, but with a few minutes and a copy of Excel, I would have gotten 7.  The R1C1 notation got me, I never use it.  The addin event calling would have slowed me down for a while, since I've never needed it yet.  The other thing to remember is that we don't have every piece of information in our heads.  You should perhaps have something that determines thier searching for answers skills?  Ie.  Using the Online help, web sites, etc...  If I don't know how to solve a problem now, I can most likely find a solution within a couple of hours using my other resources.

HTH
Cal


----------



## NateO (Dec 10, 2004)

Hello Dan,

The test looks good. Re: #1, if someone wanted to solve the integer data type quandary on large numbers by removing the data type declaration all together, I'd be tempted to give them -8 bonus points towards their score (because I can). 

Perhaps tell them that Option Explicit is enforced... Also, I'd be tempted to use an Autofilter (as Tom mentioned)...

Re: question 2, semantics, what about Union (removes duplicates)? E.g.,

_Select * From Table1 Union select * from table2_

I can see Records 1 & 3 would require that _Distinct Row_ not be enforced...

Good luck and happy holidays.


----------



## zilpher (Dec 10, 2004)

I'm with Nate, force these guys to declare correctly and -8 is generous.

I'd be uber picky though and penalize anyone who didn't say:

"Change all instances of intRow to lngRow and redefine lngRow as a Long"

And I'd expect them to pick me up on that for not making it clear in the question.

I'd also dock 50 points for anyone who didn't state they'd comment the hell out of it and keep the ReadMe.txt up to date.

But then I have just been audited.. :D


----------



## zilpher (Dec 10, 2004)

>0¬ Forgot to check it back in to SourceSafe, doh!


----------



## dk (Dec 10, 2004)

NateO said:
			
		

> Hello Dan,
> 
> The test looks good. Re: #1, if someone wanted to solve the integer data type quandary on large numbers by removing the data type declaration all together, I'd be tempted to give them -8 bonus points towards their score (because I can).
> 
> ...



When Norie posted his original alternative answer of ommitting the declaration my initial reply was "OK, I guess that would pass but then I'd punch the interviewee in the head for **** poor programming technique".  However, that isn't really in the spirit of Christmas is it, so I changed it   

Re the SQL syntax; the question asks for "ALL records from both tables" and you can see duplicate rows in the sample output.  Therefore the UNION ALL is necessary.  Maybe I should add to the question something like: "do not remove duplicates" to remove any ambiguity.

Anyway, thanks for taking a look Nate, and Happy Christmas to you too   

Dan


----------



## dk (Dec 11, 2004)

Tom Urtis said:
			
		

> Hi Dan - -
> 
> Your post is coincidental for me.  As you probably know, I own an Excel & VBA development company here in silicon valley.  The CFO of one of my client companies called me recently, really frustrated because he just hired someone who told him during the interview (as most people do) that they "know Excel", and it turned out on this person's first day on the job that they had never entered a formula before or written any VBA code (such as loops or sheet events or anything outside the realm of the macro recorder).



Thanks for that Tom.  To reply to each of your points in turn:

1.  When I wrote the first question I had it tagged as "Easy".  However, on thinking about it, and hearing the comments here, I guess it should receive a harder grading.  Anyway, these questions were all supposed to be based on real world problems.  Although I always use Longs, and never Integers, the same cannot be said of others.  I recently had to fix a simple macro that had been working for years, but that had fallen over exactly because of this problem i.e. the report it was formatting had grown and grown and eventually reached 33000 odd lines, at which point it failed.  The organisation I work in has a strong reliance on macros (some that have been in place since 94/95).  This is good, in that it keeps me in a job   , but bad because there is all sorts of dodgy code floating around which may or may not be working as intended.  I'm often only made aware of the code when it goes wrong, as in this case.  Therefore I think to ask this sort of question is valid.  However, I am going to try and re-word is so that the questionee is pointed to the fact that there is a problem with the declaration.

3.  For more general testing, this would be a nice "luxury".  However, this position has come about because my bosses boss decided at the last minute that they wanted someone to cover the test period of the add-in (rather than me fix any problems on my return to work).  I deemed that there would be no point in hiring someone unless they would actually understand the code, be able to fix it, and enhance it.  Therefore, I tried to set a level of questions that would indicate an intermediate to advanced level of VBA expertise.  Interviewing is proven to be a poor management technique so I chose this method to allow a more objective conclusion to be reached.  If we were hiring for a permanent replacement for me then I would consider the VBA skills less important, as long as they had the desire/ability to learn.

4.  This is something that I've also been thinking about.  I know that a lot of recruitment agencies have software that can test a candidate's level of Excel/Word/Access knowledge but I've never seen one purely for VBA.  Here in Australia (and I'm sure in the US even more) "advanced Excel, macros, VBA" are becoming a more and more common phrase within job adverts, especially for accountants.  I'm sure there is a market for this kind of testing.  I'd be interested to hear how your ideas develop and will happily share my thoughts with you.

All the best,

Dan


----------



## dk (Dec 11, 2004)

Cbrine said:
			
		

> ...The other thing to remember is that we don't have every piece of information in our heads.  You should perhaps have something that determines thier searching for answers skills?  Ie.  Using the Online help, web sites, etc...  If I don't know how to solve a problem now, I can most likely find a solution within a couple of hours using my other resources.
> 
> HTH
> Cal



Very true.  I use the help files, Google Groups, the MSDN, books _constantly_ and I agree that the ability to get an answer, even when one does not know it off the top of their head, is an indispensable skill.  However, at the same time I needed to test the individual's own knowledge.  I should mention that I set no time limit for this test.  If I had said to the candidate "here's a laptop, here's an internet connection" then given enough time they could have answered all the questions, and I'm not sure if that would have given me the measure I wanted.

Regards,
Dan


----------



## Legacy 5624 (Dec 11, 2004)

Exactly.
It's all about knowing how to find a solution.
If everyone were competent in this respect, there would be a greatly reduced need for this board.
Unfortunately, everyone isn't.


----------



## Norie (Dec 12, 2004)

> OK, I guess that would pass but then I'd punch the interviewee in the head for **** poor programming technique


 

Which Excel/VBA sort of condones. 

I learned how to program using Turbo Pascal, C, Prolog, Java and machine code (Z80 on a Sinclair Spectrum and later 8080 on a PC).

All of which are very unkind if you don't do things correctly. 

I always use Option Explicit and try to name variables/objects using the proper conventions.

But sometimes if you just want a piece of code that is only going to be used once then it is easier (sloppier) to ignore the conventions.

I know it's probably the wrong way to do things but when somebody asks you for something "immediately" what choice do you have.

I once worked in the insurance section of the finance department for a large utility company and their IT department told them that it would take 6 months for them to create the database they required.

I was able to produce what they wanted in 2 weeks - including Help files and documentation.

It wasn't perfect but it did the job, and then I was asked by other sections of the department to create databases for them.

BTW I've found that the Help files for Access/Excel VBA can be quite helpful - I just think that you need to know what you are looking for.


----------



## Greg Truby (Dec 13, 2004)

Hi dk,

I thought the content of the test quite appropriate given the circumstances you describe.  I'd score myself as getting 7.5 out of 8.  A couple of thoughts...

Q#1. I would call this "easy".  If the candidate has done very much VBA Excel programming, I would expect them to have had an integer/row variable blow up in their face at some point.  Furthermore, I would hope that the candidate would call me to task for looping instead of using Autofilter & SpecialCells.

Q#2. I don't use SQL frequently enough to be rock-solid sure of myself.  The answer I jotted down was on the money, except that I didn't have the ALL keyword.  But I knew it would be a UNION query and I know exactly which chapter in my Ken Getz book that I'd look in for help.  So I have myself 1/2 point on that one.

Q#3. At first I thought it a bit tough to be included.  But on thinking more about it, even if the candidate had not created an application class module, I would hope that he had at least used a class module to handle groups of controls on a user form, where he would have learned about the WITHEVENTS being required and could guess that this would be the case for your example.  [I might even have included a fourth option using the Public keyword without WithEvents to make it harder to simply guess based on option "c" being the only one that declared it as Public.]

Q#4. I guessed on LenB().  I don't know how many people ever use the --B() versions of Len() or Char().  It's one of those functions that I know are out there because I've read about them; but never had need to use them.

Q#5, #6 & #7 - All were "easy" questions in my book.  I'd probably scratch off any candidate that couldn't answer these. 

Q#8 - I think it's a fair topic.  However, this is not my strong suit so I made an educated guess about what you were expecting to hear.  But the question left me wondering if late binding is better or whether one would be better off trying early binding, adding the reference from the GUID and setting the Major argument to the lowest possible version.  My _guess_ is that error trapping and handling might be easier if I could early bind.  Am I wrong about this?  [Seriously -- I'm hoping for enlightenment here. As I said, this is *not* my forté and I'm always eager to learn more about the advantages/disadvantages of early vs. late binding in various situations.]

Like the rest of the gang -- hope you find/found someone to fill your shoes while you're on holiday.


----------



## XL-Dennis (Dec 14, 2004)

Hi Dan 

Well, You already got some good points so I will not add more to the general impression except that I find Tom's points to be very good and interesting to which I agree with.

Several questions are not complete in that they explicit gives the whole picture so to speak.

A typical one is the #7
In general there is no guarantee (as we all know) that code will actual work 

Using LBound is a lazy (and poor) coding when not consider the actual circumstances which means that 0 to UBound(....) can work very well too. 

For #8 there exist an additional alternative if You have all the versions involved available on the developing computer(s):

c) Use early binding and set a reference to the earliest version the solution should be working with.

#5 is not a good question due to the lack of problem-solving.


----------



## dk (Dec 14, 2004)

XL-Dennis said:
			
		

> Hi Dan
> 
> Several questions are not complete in that they explicit gives the whole picture so to speak.
> 
> ...



I disagree that the question is incomplete.  Of the 4 answers, only 2 are viable (answer a and b).  Answer b will ensure that every element is looped through whereas a would not if the first index was anything other than zero.  To state that "LBound is a lazy (and poor)" is a bold statement.  How about if you wanted a function which simply accepted an array and performed an operation on it, and returned the processed array?  If you wrote this function to be robust then you would HAVE to use LBound and UBound, unless you make the assumption that the array being passed is always zero based.  If you pass an array of values from a range then it will be 1 based.



			
				XL-Dennis said:
			
		

> For #8 there exist an additional alternative if You have all the versions involved available on the developing computer(s):
> 
> c) Use early binding and set a reference to the earliest version the solution should be working with.



The scenario I was working with assumes that the workbook in question will be shared amonst other people using different version and each user will need to save the workbook.  Try this simple test.  Put this code into a module and set a reference to the earliest version of Outlook available (call this machine A).


```
Sub TestOfEarlyBinding()
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem

Set olApp = New Outlook.Application
Set olMsg = olApp.CreateItem(olMailItem)
olMsg.Subject = "Test"
olMsg.Display
End Sub
```

It should run without problem.  Now open the same workbook on a machine with a later version of Outlook (call this machine B).  The reference should be updated to the later version of the Outlook object library.  Save the workbook.  So far so good.  Now open the workbook on a machine with an earlier version of Outlook than that of machine B (which does not have multiple versions installed).  What happens to the reference?  It goes AWOL!  This is the behaviour I've witnessed whilst distributing Access databases and Excel workbooks within our organisation and the reason I would opt for late binding.  However, if the interviewee was to provide an argument such as yours then they would still get the job   



			
				XL-Dennis said:
			
		

> #5 is not a good question due to the lack of problem-solving.



I take it you didn't get this one right  :wink:   A completely valid question in my opinion related to the job at hand.  To avoid hard coding addresses I often use code such as this:-


```
For lngCol = consts.COLUMN_HISTORY_FIRST + 1 To consts.COLUMN_HISTORY_LAST + 1 Step 2
                shtBudget.Cells(lngRow, lngCol).FormulaR1C1 = "=IF(R" & lngPerUnitRow & _
                        "C[-1]=0,0,RC[-1]/R" & lngPerUnitRow & "C[-1])"
            Next lngCol
```

The ability to understand R1C1 formulas and to convert between A1 and R1C1 is therefore very relevant to this position.

Thanks for your feedback Dennis.  And I'm glad to report that I did find someone to fill the position.

Dan


----------



## XL-Dennis (Dec 14, 2004)

Dan,

I still disagree and the mainexplanation is propably due to the different approaches we take when it come to work with different  task. 

I nowadays split the code and the data from each other which means that saving is only related to the data involved and never to the code. 

So You scenario will never happend in my case 

As for #5 I believed i learned that when version 2.0c of Excel was shipped in 1987 or was it 2.0 d   

LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not


----------



## NateO (Dec 14, 2004)

Hello,



			
				XL-Dennis said:
			
		

> LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not
> {snip}


I thought we already determined that Option Base is a very risky approach to iterating through arrays:

http://www.mrexcel.com/board2/viewtopic.php?t=78319&start=11

You're safer [and more dynamic, as the question put it] using LBound().


----------



## dk (Dec 14, 2004)

Dennis my man,

OK, let's agree to disagree on the other points.  We obviously have different approaches to tasks and many problems have many possible solutions.




			
				XL-Dennis said:
			
		

> LBound is still no good coding practice as this can be controled by the use of Option Base as well related to if we work with arrays that read data from ranges or not



On this one, I do disagree.  Option Base has no effect when creating an array from a range:


```
Option Base 0

Sub Test()
Dim vArray()
vArray = Range("A1:A10").Value
MsgBox LBound(vArray)
End Sub
```

All the best,
Dan


----------



## Greg Truby (Dec 14, 2004)

Dan (or anyone else that can answer),

Thank you for discussing the issue of binding and your example on Outlook.  My interest in the question is more than academic.  I've written an add-in that is used by a few dozen people in my company.  I set references using early binding.  Here's a snippet of the code from a function that is called by the Open event handler to make sure that everything is in order:

<font face=Courier New>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>

    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rngRefs.Rows.Count
        strName = rngRefs(i, 2)
        strDesc = rngRefs(i, 3)
        strGUID = rngRefs(i, 4)
        intMajor = rngRefs(i, 5)
        intMinor = rngRefs(i, 6)
        <SPAN style="color:#00007F">Set</SPAN> objRef = DataScraperWorkbook.VBProject.References
        objRef.AddFromGuid strGUID, intMajor, intMinor
        <SPAN style="color:#00007F">If</SPAN> Err.Number <> c_lngErrAlreadyRefd And Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrorHandler
    <SPAN style="color:#00007F">Next</SPAN> i
    
    CreateReferences = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

I thought that by using the GUID and controlling the major version and using error checking that I would be able to exit more gracefully should the needed references not work out.  Do I have an Achilles heel here that I'm not aware of?  Couldn't you do this with the Outlook example you cited?

{Edit}And before any of you wiseguys point it out, _i_ *is* an integer, but Heaven help me if I ever have to set over 32,000 references!{EndEdit}


----------



## macleanb (Dec 15, 2004)

I would definately say that < 4 on this test is a bad sign for your replacement - and would expect a competant person to be in the 6/7/8 range


----------

