Quote:
Originally Posted by CopTHIS
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?
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)
Quote:
Originally Posted by CopTHIS
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?
I'm not sure what you mean but alot of people use the reference style:
Code:
sheets("Sheet1").cells(1,1) = "hello"
I prefer the style:
Code:
sheets("Sheet1").range("A1") = "hello"
for obvious reasons, and very rarely need to use the ".cells" extension
Quote:
Originally Posted by SuperRams
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.
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
Quote:
Originally Posted by SuperRams
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
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