# Do you know all your VBA code off by heart for interviews?



## poiu (Mar 8, 2014)

Hello,

I work in accounts departments (as an accountant) automating reporting using vba - I do most of this referring to a 20 page "cheat sheet" I've built up over the years and it's very effective and quick.

The thing is that I don't know my VBA off by heart. The main reason for this is that the code isn't very intuitive (unlike PHP for example) and so it would take a big effort to put it all to memory.

 I had an interview recently for finance programming work, but the role was situated in the IT department and the interviewer was an IT manager. He gave me a VBA test and I must have scored pretty much zero, pretty embarrassing.

So do you learn all of VBA off by heart? Or do you have tricks for finding what you need quickly within the VBA window of excel?

Thanks,

Poiu


----------



## ZAX (Mar 8, 2014)

Hi Poiu,
For a start I'm a kid actually and I don't know if this makes any difference..
I actually learned VBA and Excel in about a month, the Visual Basic is easy cuz it's more like normal talk with a few changes, for example you say:
I want the code to copy the range A1 if it has a value of 8:

```
If Range("A1").Value = 8 Then
    Range("A1").Copy
End If
```
The answer to your question is yes, we learned it off by heart, it's pretty easy, here's how I learned it:
I first, read some books to learn the very basics like what's the difference between a macro and a function and how to run a macro...
Then after I understood the basics I downloaded games made using Excel and VBA and learned a lot, it's really helpful, in your case you can browse the forum and see the codes we send.
When I started my father was teaching me and in two weeks, I was the one teaching him!
ZAX


----------



## RoryA (Mar 10, 2014)

I can do a fair amount off the top of my head but I still use the Object Browser as a quick reference.


----------



## RoryA (Mar 10, 2014)

ZAX said:


> I actually learned VBA and Excel in about a month



That's a pretty bold claim - I don't know anyone who would claim to know all of Excel and VBA. I've been doing this a long time and I certainly don't know all there is to know about either one.


----------



## Jon von der Heyden (Mar 10, 2014)

12 years on I'm still learning.  I firmly believe that it takes many months, years even, to become a competent VBA developer, unless you are coming from a development background.

Where people offer VBA development services without suitable experience I note two different kinds of hacks:

1. People from a development background that haven't properly studied the Excel Object Model.  They are often capable programmers, but not Excel developers...
2. People who were pretty darn good as Excel users but never took the time to study programming/programming fundamentals...

The topic is huge - significant time and effort investment is required to learn how to develop _decent _MS Office/Excel solutions...

Do I know it all off by heart?  No!  I frequently visit the object browser, help files (MSDN) and prior projects...


----------



## PaddyD (Mar 10, 2014)

"He gave me a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> test and I *must* have scored pretty much zero…"

Are you sure?  Maybe you scored very well but the IT manager just didn't like you?


----------



## SydneyGeek (Mar 10, 2014)

Like Jon and Rory, I took a long time to learn. It's a language, and languages take time.
Many of the things that I do regularly are in my head; everyone does it differently but I tend to lay out a big piece of VBA by creating comments to describe the sections, then writing the skeleton structures for all the loops, conditions, etc. After that it's filling in the details.

To get syntax for anything that I am unfamiliar with I often use the recorder, then modify the output. For more detail on methods and properties I use the object Browser. And Google ...

Denis


----------



## mole999 (Mar 10, 2014)

poiu said:


> He gave me a VBA test and I must have scored pretty much zero, pretty embarrassing.



write to them and ask for feedback on your strengths and weaknesses from their point of view


----------



## Jon von der Heyden (Mar 10, 2014)

SydneyGeek said:


> ...but I tend to lay out a big piece of VBA by creating comments to describe the sections, then writing the skeleton structures for all the loops, conditions, etc. After that it's filling in the details.



I pretty much do that too.

I will add that I think that the locals window is quite cool for learning properties too.  Having had it open all this while has revealed many properties that I did not know exist...


----------



## Jonmo1 (Mar 10, 2014)

As far as interviews go, I don't think you're judged too much by what you know off the top of your head.
The real question an employer wants to know is, do you know "how and where to find the information"?
They usually want you to do things "By the book" anyway, so as long as you know where the book is and how to read it.
If you can rattle off a few tools and resources that you frequently use, then youre good.
Things like  the Help Files, Object Browser, Macro Recorder....um...MrExcel.Com etc...

I'm sure you (like most of us) have at least a few common code bits that you can rattle off the top of your head.
Just have a couple in your head, then you might even mention that you have a cheat sheet (though I wouldn't call it that).
Call it your Personal.xls workbook, that has all of the common and advanced bits of code/formula you frequently use.
This will show you have the initiative and organizational skills most employers want.


----------



## TinaP (Mar 10, 2014)

I have a code library (most kept in personal.xls) that I refer to often.  There are certain modules that I use for almost every project and I couldn't recreate them without pulling up the code.  

I learned how to code in other languages when I was in college (before Excel existed).  I also had to take a 3-credit Lotus 1-2-3 course.  When Y2k came around, I was the only one in my company who knew how to interpret the Lotus macros.  Since we were changing over to Excel then, and our merit adjustment was calculated using a Lotus macro, I was tapped to teach myself VBA and convert all important Lotus macros to VBA.  Needless to say, my coding left a lot to be desired and my Excel wasn't too good, either.  Thanks to MrExcel.com, I've gotten much better with both.  I've also rewritten the merit adjustment macro and it runs so fast I've had to add a "Processing Completed" message box just so the user knows that it ran.

Now I write and understand code fairly well, but often need help with syntax.  And I still don't write the most efficient code.  That's why I hang out here so much.  You probably did much better on the test than you think.


----------



## ZAX (Mar 10, 2014)

RoryA said:


> That's a pretty bold claim - I don't know anyone who would claim to know all of Excel and VBA. I've been doing this a long time and I certainly don't know all there is to know about either one.



I didn't learn it all of course but at least most of it, There are still some things I don't know and things my father didn't understand so he didn't teach me, like financials for example, I learned 0 complicated financial formulas cuz none of me nor dad understood it, it's full of terms lol, And I didn't learn how to program charts and a few more things to go...
Thx for the.... reply I guess?
ZAX


----------



## Jon von der Heyden (Mar 11, 2014)

ZAX said:


> I didn't learn it all of course but at least most of it...
> ZAX


Stick with it and lets chat in a couple of years.  I'm sure by then you'll agree that you didn't know (and probably still won't know) most of it.  You never really know the scope/size of the topic until you know it all...


----------



## Joe4 (Mar 11, 2014)

> I didn't learn it all of course but at least most of it,


Enjoy your teenage years.  Its the last time in your life that you will know everything!
There are actually layers of Excel/VBA that you probably aren't even aware of!

What I know, I know pretty well, but I am aware that there are a lot of layers that I haven't even really scratched the surface on.
All it takes is reading some replies from people like Aladdin, Rick Rothstein, and the late-great Nate Oliver (as well as many other gurus on this board) to remind me how much I do not know.


----------



## ZAX (Mar 11, 2014)

Joe4 said:


> Enjoy your teenage years.  Its the last time in your life that you will know everything!
> There are actually layers of Excel/VBA that you probably aren't even aware of!
> 
> What I know, I know pretty well, but I am aware that there are a lot of layers that I haven't even really scratched the surface on.
> All it takes is reading some replies from people like Aladdin, Rick Rothstein, and the late-great Nate Oliver (as well as many other gurus on this board) to remind me how much I do not know.



Ok, thx for your advice and please, everybody, forget I said anything, I just replied to his question with "yes", the rest is Removed!  
ZAX


----------



## Joe4 (Mar 12, 2014)

In all seriousness, if you are proficient in Excel & VBA, I encourage you to please take a look at the back log of unanswered questions we have out there (under "Zero Reply Posts"), especially those that are more than a day old.  

We would be very grateful if you could help knock some of those off, there are some real challenging ones out there.  Anything you can do to help out there would be of far greater value to the board than anything posted in the Lounge.


----------

