VBA Corner
So you have an Excel sheet with data on your Windows computer, and an app on your Android phone that can import csv files. You want to export your Excel data to a csv file that you can send to the Android device in order to import it in your app.

Read more...Collapse )
 
 
 
 
VBA Corner
18 October 2010 @ 04:06 pm
Apparently, I've never before tried to enter a slash in an Excel sheet until now.
Because the result surprised me - if you type /, it doesn't enter the character in the cell, but it activates the keyboard access to the Excel menu. Which is really annoying if you actually want to enter / in a cell.

Fortunately, this behavior can be turned off and this is how:

Excel 2007
1. From the Office Button menu, press the button Excel Options.
2. Choose Advanced, and then scroll down until you see the Lotus compatibility section.
3. In the field Microsoft Office Excel menu key, replace the slash with a character that you do not use frequently or delete it, and then click OK.

Excel 2003
1. From the Tools menu, select Options
2. Select the tab Transition
3. In the field Microsoft Office Excel menu key, replace the slash with a character that you do not use frequently or delete it, and then click OK.

And to make this a post about VBA, you can switch off this property in VBA as follows:
Application.TransitionMenuKey = ""
Tags: , , ,
 
 
 
VBA Corner
24 August 2009 @ 12:00 pm
Sometimes you just want to read in a complete text file from your PC without muddling through all the records (i.e. text lines) one by one. When you open a text file in binary mode, such things like EOL won't get special attention and you can read in the text as one big chunk.

Code for function that reads in a text fileCollapse )

 
 
 
VBA Corner
20 August 2009 @ 01:22 pm
Long time no post...
And this post has nothing to do with VBA either, but I'd like to point out two incredibly useful Excel tutorials by [info - personal] slowfox

1. Have you ever heard of Array Formulas in Excel? No? Or you did, but never really understood how they work? Then have a look at this tutorial. Although the described tasks would also be solvable with ordinary formulas, they're a great example to understand the concept of array formulas. And as soon as your task is something else than counting or adding up or gets more complex, array formulas are a powerful tool that might save you from coding macros.

2. Working with a program is always quicker and more efficient if you don't have to use keyboard and mouse in parallel. You can hardly work with Excel using only the mouse, but you can try to do everything with your keyboard to get faster. Here's a nice overview of Excel shortcuts that will free you from your mouse maybe not entirely, but a big lot.

 
 
 
VBA Corner
16 February 2008 @ 10:49 am
Ok, Excel probably isn't the first thing that comes to mind when needing to deal with web pages. But sometimes it's necessary to access them from your Excel Application (or Word or any other MS Office Application). And it's easier than you probably think.

Read More...Collapse )

 
 
 
VBA Corner
13 February 2008 @ 07:13 pm
Excel uses collections of objects a lot, and sooner or later you'll be in the need of checking if a certain object exists in a collection. For example, to check if a workbook was already opened, check if it's in the collection of open workbooks (Application.Workbooks), to check if a certain worksheet exists, check if it's in the collection of worksheets (e.g. ActiveWorkbook.Worksheets) and so on...

So how to check this?Collapse )

And this approach works for all collections of objects that Excel uses, not only workbooks and worksheets.

 
 
 
VBA Corner
10 November 2007 @ 06:41 pm
You want individual buttons? Using the formatting options for CommandButtons will get you only so far. You can change the background color, the font, and you can even put a picture on the button, but the button will always be rectangular.

So if you want round buttons (or any other form) on your sheet, use shapes. You can draw them in whichever way you like. You can make them look as complex and colorful as you like by drawing several shapes and grouping them together.

Assign Code to Run When Pressing the Button Collapse )
Create a Tooltip for the ShapeCollapse )
There Can Be Only One... Or Not?Collapse )

 
 
 
VBA Corner
23 October 2007 @ 06:00 pm
Imagine you have a long list in your excel sheet and want to find certain entries. Especially if the list gets bigger, it'd be nice to just tell Excel what you're looking for, and have Excel automatically find it. It's faster and more accurately than searching yourself.

Excel Search - not the ultimate solutionCollapse )

The key word is Conditional Formatting. You can define a conditional format for the list that shows only when the cell value matches the search value. This method has the advantage that you don't have to worry about the original cell formats at all.

Finding exact matchesCollapse )
Looking for parts of stringsCollapse )
Searching with wildcardsCollapse )

Ok, so this post didn't contain VBA code, but only because it was easier to do this without...

 
 
 
VBA Corner
25 September 2007 @ 03:14 pm
To create random numbers in VBA, there's the function Rnd. It returns a random number between 0 and <1.

Randomly select entries from a listCollapse )
Select entries with different probabilitiesCollapse )

 
 
 
VBA Corner
06 September 2007 @ 05:10 pm
Whenever you need to work with very many cells in Excel, check or manipulate cell values, it can considerably speed up your code to use arrays instead of working directly with the cells.

Here're a few examples to show how it worksCollapse )

For detailed info about working with arrays look here.