Excel/VBA
As nice as Excel 2007 is, and much of Office 2007 in general, Microsoft made some annoying decisions with developer access in VBA.
Charts are now Excel objects and their data are based on worksheets. This is pretty nice overall, but Microsoft does not allow access to the sheet data when pasted into Word or PowerPoint. So
Also, changes in the chart are not picked up with the macro reader. So developers can’t use the reader to help figure out the object model and thus write code.
The following routine is one alternative to the macro reader. Select a chart and then run the module. It will stop into break mode and then you can set up watches (right click on them to do so) on each of the three objects. You can select objects (keep the focus on the chart, though) and see the values change.
Public Sub ExploreChartElements()
‘select a chart and then run this, it will break and select various chart elements.. use watches…
Dim oChart As Excel.Chart
Dim oChartGroup As Excel.ChartGroup
Dim oSeries As Excel.seriesSet oChart = ActiveChart
Set oChartGroup = oChart.ChartGroups(1)
Set oSeries = oChart.SeriesCollection(1)Stop
End Sub
Here’s a common dilemma: Managers create “rogue” reports that give them metrics they need, and aren’t easily available by the corporate reporting system. They are usually Excel reports that start small, but, over time, grow so big that they’re challenging to maintain. If managers reach out to the IT department for help, they rarely find a warm response.
FSI found just this situation when it was asked to help some managers in two divisions of JP Morgan Chase. They created several detailed reports in Excel that were updated on a weekly or monthly basis. The reports had several data sources, including corporate data (Oracle) and several external data exports as text files.
Staff had to update these reports with a dozen metrics down to the bank branch level. IT had already conducted a formal analysis and said it would be much too expensive to duplicate the reports in the enterprise reporting system.
FSI kept the reports as is, which were quite complex and elegant, and used VBA to automate the update process. The managers had pull down menus or buttons to easily update or manipulate the data.
The time for each update was reduced from a full FTE day to fifteen minutes.
Sometimes you want to list the column letters. You could just use the CHAR function, but when you get past Z and move on to AB and beyond, that system breaks down.
Here is a function to use to return the column letter(s) to place in a module. In Excel 2007 it will automatically pop up with the default functions.
Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then‘ 1st character: Subtract 1 to map the characters to 0-25,
‘ but you don’t have to remap back to 1-26
‘ after the ‘Int’ operation since columns
‘ 1-26 have no prefix letter‘ 2nd character: Subtract 1 to map the characters to 0-25,
‘ but then must remap back to 1-26 after
‘ the ‘Mod’ operation by adding 1 back in
‘ (included in the ’65′)ColumnLetter = Chr(Int((ColumnNumber – 1) / 26) + 64) & _
Chr(((ColumnNumber – 1) Mod 26) + 65)
Else
‘ Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function
If there is a * in cells and you want to replace them, you need to use ~* to tell Excel it isn’t a wildcard.
Summary
Although Microsoft does not recommend it, you can install and use more than one version of Access on a single computer. This article discusses how to install and use Access 97 and Access 2000 on the same computer. This article also discusses some of the problems you may experience when you do this.
NOTE: For information about running Access 97 and earlier versions on the same computer, please see the following article in the Microsoft Knowledge Base:
Q159333 ACC: Running Current and Earlier Access Versions Simultaneously
More Information
How you install Access 97 and Access 2000 on the same computer depends on whether one of the versions is already installed or whether neither is installed. The following installation instructions cover all scenarios:
Neither Access 97 nor Access 2000 Is Installed
Access 97 Is Already Installed
Access 2000 Is Already Installed
If you have already installed Access 97 and Access 2000 on the same computer and are having problems, see the Problems After Installing Access 97 and Access 2000 on the Same Computer section of this article.