lol agree.

I agree, but i assumed other people would have some comments on it and I/others could learn, that's the point no? There's always going to be someone better at something but maybe they couldn't be bothered starting a thread.

I don't want to get defensive but most of the things I do are not specifically front end which is why I suck at pivot tables. It's probably because i've never done a job that required pivot tables, and find that using vba to manipulate data produces better data sets in my experience.

Yes! I have also learned that most people don't care about a better way to do it and just keep my mouth shut (unless that's why i am there obviously).

It's the same thing in video games, when someone is doing something wrong and you just want to punch them in the face. Unfortunately it's not always a good idea.

EDIT: I also realise that somewhere out there someone could be watching over my shoulder and thinking the same thing (see: pivot tables)

What are the pros/cons of using Excel to store data as opposed to a (relational) database?

I don't use Pivot Tables either. As OP mentioned, VBA provides so much more flexibility I can't imagine a situation I would ever use one.

Obviously I'm a super n00b on Excel but I'm curious how hard it is to write(?) macros. i run some of these daily and can't imagine how much harder my job would be without them.

I have two questions that spring to mind after we just upgraded to Office 07 at work:

I notice that Excel 07 has gone from something like 255 cols and 66k rows so something a zillion times more. However, it seems to me that it you use all the rows then even inserting/deleting a row (so forcing Excel to change lots of references) will crash the PC. Is this just some kind of forward planning by Microsoft in that the sheet range is way too big for anyone to use now?

Re macros and the syntax of Excel ranges (especially if you want variables, eg row number). This officially does my head in and I've never been convinced that it works as it should. Is it just me, or have I missed something?

How would one get into this line of work? I do a fair bit of VBA programming and SQL-based work in my current position.

A tech question -

How would you aggregate an array?

Say my array is 3 columns:

A 1 11
A 1 22
B 1 10
B 1 11

So that my new array is:

A 1 33
B 1 21

http://adminsecret.monster.com/benef...not-a-database

First i would NEVER use excel for a relational database, you would pretty much be using VBA to rewrite SQL. On a flatfile (i.e. a single table with no joins) i would say that any company that is using it could be using a real database with more effect but they don't have the knowledge/money/motivation to do it and you could argue that in most cases it is probably sufficient to the level they need

To do any meaningful reporting from a database you would need to write complex SQL that would then feed into excel to produce pretty graphs, obviously people are much more comfortable with setting up a simple datasheet in excel than trying to set up a database from scratch.

EDIT: or see post above

If you have any programming experience it is REALLY easy, with anything involving only one file you can record it with the macro recorder then go to the vba and take what you need, e.g. whenever i need to do a sort in vba from scratch ill do it in the recorder then take what i need and put it in my code (the recorder creates really bad macros but is useful for specific functions) heres what i mean:

vs
You should almost never be selecting specific cells as it slows processing and can produce random results, also using 'activesheet' can sometimes screw you up

Even that is not the most efficient way to write a sort and you could probably do it in less lines but that was just off the top of my head

This is how I feel but I don't know pivot tables well enough to completely write them off

CopThis,

When you want a variable range find out how many rows and cols you have first then concatenate the variables.

iColumnCount= Range("A1").End(xlToRight).Column
iRowCount = Range("A1").End(xldown).Row

rgMyRange = wsMyWorksheet.Range("A" & iRowCount, "A" & iColumnCount)

Untested code fwiw.

zomg,

"Dim sht1 As Worksheet"

Do you ever use the Option Explicit statement? When I started I learnt it was best practice and all

I've actually never encountered this and i've had very large databases it's probably just a computer specific issue. One thing i find with excel (especially with vba) the workbook can often get corrupted and produce random results that can be completely fixed by copying into a fresh workbook and re saving with no other changes.. I spend a lot of time convincing companies that excel is crap and they should be using other systems (essentially talking myself out of a job but sometimes processes involving numerous large macro enabled workbooks in excel can be frustrating)

I'm not sure what you mean but alot of people use the reference style:
I prefer the style:
for obvious reasons, and very rarely need to use the ".cells" extension

I mentioned this before but the hardest part is getting your foot in the door, even someone with a rudimentary understanding of using VBA could offer value to business', the hard part is convincing them to spend the money

off the top of my head i would do something like
for each element in main array
if element is not in second array
add to second array using worksheetfunction.sumif(array(1st column),current letter, array(3rd column))

im not sure of the exact syntax but at first glance that's probably what i would do

Let me know if you need more help but i'm more a believer of pointing people in the right direction than just flat out giving a solution
I would look into using the "worksheetfunction" command off the top of my head i

Here's one for you:

Do you have any best practices for handling the formatting of dates?

I've spent countless hours trying to convert various cells into a date format that I wanted, but with little luck.

For example, going between Jan 4th, 2010, 1/4/2010, and a more generic 1-2010 / Jan-2010 always results in nightmares for me.

Sometimes it seems to work perfectly, other times Excel refuses to cooperate. I'm aware of the Cell Formatting options (and even tried using the custom formatting for like M/YYYY), but it doesn't always work.

Any tips?

I try to and it is definitely best practice. I always hear that the main benefit is it saves space by specifically declaring everything but i'm not convinced that it makes any significant difference with the processing power cpu's have these days.

OTOH the amount of times i've misspelled a variable and taken 20 minutes to figure out why the result isn't what it should be when an option explicit would have thrown an error straight away is all the convincing i need.

FWIW, i never use toright and down i will always use fromleft and up because if there are blanks in the data xldown will not get all the data

i.e.

in saying that you can also use "lastusedrow" (not sure on exact spelling) but it has issues in that i think it gets set when the workbook is saved so in an unsaved workbook it will miss added rows (can be fixed with a worksheet.save command i think)

Thanks for the replies. I have thought of something that contains blank data in one column causing me to offset the reference I can tweak tomo with the up technique

The array question is in Access so writing it to a temp table (not generally a fan of) then opening a recordset works fine, just wasn't sure if it was the most effective.

How would you get "in" with a company i.e. contract work? I've found working for large organisations it's easy to spot stuff that needs development but I can't think how you would get the freelance side of things.

As someone who has moved his entire operation from Australia (DD/MM/YYYY) to USA i can safely say that excel and vba suck balls with date handling

But in saying that if you are in not moving continents then in the custom formatting you can do:

Jan 4th, 2010,
mmm ddd, yyyy
Note that you cant get the "th/nd" extensions without using external formula i.e. if the date is in cell A1, then if you put this formula in A2 it will format correctly (personally id just lose the extension, who cares)

=TEXT(A1,"mmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","n d","rd","th","st","nd","rd","th","st"})&TEXT(A1 ,", yyyy")

1/4/2010
m/d/yyyy


1-2010 / Jan-2010
m-yyyy / mmm-yyyy

No problem, one of the things i love about excel/vba is there is always a better way to do something so i'm always learning

woops missed your question: I hate temp tables, i have found that they create so many more headaches in the long run. It's hard to say without seeing but have you looked at doing the whole calculation in the original SQL?

In short, I was in the same position as you but i socialised with the owners of the process i thought could be improved then offered to help them out and create solutions. I've designed systems that i could sell for hundreds of thousands for next to nothing while working for a company only to have them call me up years after i have left and ask for changes or more help.

I had a lady call me 3 years after i worked somewhere saying she spoke to such and such who told her that i could probably help her and this has now become my second largest account.

My newest client is a huge movie production studio that my friend was on the marketing team for, he told his boss that i could automate everything they were doing and i've done two contracts with them so far with 3 more on the way in the next couple of months. Note that most of the macros aren't complex at all and rarely take more than a day to complete

It's very much a word of mouth thing and one of the things i struggle with the most is getting new clients. Although thankfully I have enough work at the moment

GETPIVOTDATA(data_field,pivot_table[,field1,item1]...[,fieldN,itemN])


EDIT: pivot tables are great and I highly recommend using them especially when your audience is somewhat competent. they can be dynamically created and destroyed which can drastically cut down on lead time for some of the smaller requests where users just want the ability to summarize/aggregate the data in different ways.

I do the exact same thing for a large company in the US (although we funnel everything into a bunch of SQL Server databases via SSIS and just use Excel/Access for UI. I love the company I work for and don't want to go full-time contract, but picking up some extra dough on the side (especially at $50-90 /hr) doesn't sound bad at all.

Could you elaborate on how you find legit projects on Craigslist for that price range?

I doubt I'm "further down the road" than you, but my plan is to attack some of the project manager type roles at my current company as soon as I absorb a bit more of the business-knowledge (supply-chain) and people management skills. Simply put, I want to be the link between senior management and a team of my clones. Currently (at the company I work for), there are only a handful of these type of people (that I'm aware of) and they are treated like gold.

Thanks, I'll fiddle about with coding when I'm next at work. I just find it fiddly - eg I'm sure that I've been able to use the range command when the variable is the row but not when the variable is the column. I think range("A" & var : "C" & var2) worked but range(var & "1" : var2 & "2") or something similar wouldn't - if you see what I mean.

What do you mean by this? What kind of things do you do?

I can't imagine a whole lot of things where Excel is the right tool for the job for back end work. At the bare minimum I could see writing a simple script to import the Excel data into Access for proper processing/querying/reporting.

Edit: Actually I think its super easy to import Excel data into Access using VB - but it's been a long time since I've done Excel/Access programming.

fwiw I work as an actuary and we use spreadsheets for calcs and modelling all the time. For big jobs involving data we may use Access, and some specific jobs have their own software, but we are not programmers and Excel is so easy to use and ideal for what we do.