Carly Bond

Microsoft Excel

VB.Net – Connection to Microsoft Excel Issue

Over the last week or so, I have been learning VB.Net. Now I am very new to this, and although I have got 10 years of VBA for Applications behind me, any new language is difficult to pick up. For me, anyway!

The job I needed to do, is work through a list of files & folders and amend a small amount of VBA code from one thing to another.  Given a starting point from Mike Hudson, I started to look into how it all worked & fitted together.  My first stumbling block was being able to connect to Excel.  I kept getting the following message:

HRESULT = 0x800A17B4 (-2146822220) “Programmatic Access to Visual Basic Project is not trusted”

I searched on the Internet to find that there was a setting in Excel that I needed to select before I could continue.  One which you can solve by following these easy instructions:

Office 2003 & Office XP

  1. Open the Office 2003 or Office XP application in question.
  2. Go to the Tools menu.
  3. Click Macro.
  4. Click Security to open the Macro Security dialog box.
  5. Go to the Trusted Sources tab.
  6. Click to select the Trust access to Visual Basic Project check box to turn on access.
  7. Click OK to apply the setting.
  8. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

Office 2007

  1. Open the 2007 Microsoft Office system application in question.
  2. Click the Microsoft Office button.
  3. Click Application Options.
  4. Click the Trust Center tab
  5. Click Trust Center Settings.
  6. Click the Macro Settings tab.
  7. Click to select the Trust access to the VBA project object model check box.
  8. Click OK.
  9. Click OK.

Microsoft Excel – Showing hidden data in charts

Excel

Sometimes when you are producing reports in a chart form you don’t want to show the raw data behind it, however, if you hide your data from the end user, the chart information seems to disappear.

There is, however, an easy way to get around this…

1) Create your chart in the usual way but when you Select Data Source, Click the Hidden and Empty Cells button in the bottom left of the pop up box.

SelectDataSource

2) Select Show data in hidden rows and columns

3) Click OK

Microsoft Excel – Stop a ‘Find & Replace’

Excel

I am always working with large sets of data and occasionally I need to ‘Find & Replace’ on the whole data set, but it can take, what feels like FOREVER! The cell reference whirring away in the top left hand corner of the grid.

Sometimes, I realise that I didn’t really want to ‘Find & Replace’ what I have & want to cancel the process, however, Excel likes to tell you it’s ‘(Not Responding’) and you can either be sat there for ages, or end task when you don’t really want to.

To get round this problem, all you need to do Click & Hold the Esc Key until Excel starts to respond again.

Simples 🙂

Microsoft Excel – Forward Slash Issue

Excel

I’ve been using Microsoft Excel for about 15 years but yesterday I came across something I have never seen before.  I went to type the forward slash straight into a cell and it behaves just like the Alt key showing all the short key references on the ribbon.  I thought I was going a little mental & couldn’t believe that I’ve never come across this before.

But I found out that you could switch it off very easily and thought I would share:

  1. Click the Office Logo and go to Excel Options
  2. In the options window, click Advanced
  3. Scroll all the way down to the bottom to the Lotus compatibility section
  4. Remove the / from the Microsoft Office Excel menu key

Conditional Formatting – Show Items Only

Excel

In Excel 2007 there is an option to use icons in conditional formatting…For example, Up, Down & No Change arrows, which are fed by a number, 0, 1 or 2 in the same cell.  I needed to show these arrows on scorecard I was designing but there was an issue with the size of my column & where the arrows were sitting:

CF

I wanted the arrows to appear in the middle of the cell & the numbers not to be there.  My initial thought was to just change the font colour of the cell, but this still showed the same information (although hidden) & the arrows stayed where they were.  Then I found a setting which means you can just ‘Show Icons Only’…

(more…)

Index & Match with an Array

Excel

I spend most of my day at work returning values from one worksheet to another or one list to another, for example, matching a customer name to a customer number.   This can be done by using a combination of the INDEX & MATCH functions (see example below)

Index&Match1

(more…)