Programming with Charts In Excel

Chart programming is a little difficult in Excel because Excel itself has some procedures to update charts automatically after any event is fired. For example, After deleting a series, the axes values are automatically changed. Like this, there are lots of cases when you need to trace the Excel Behavior in order to write an efficient Chart Code. I have got some tips, which I learnt which doing the Chart Programming in my recent project:

1. If you dont want to plot 0's in your chart, then use NA() for Blank cells. It will remove the 0's in the chart. If you use "" or blank cells, it is considered as 0 in the charts.

2. If you have to assign xRange at run time, you will always have to assign it to a series. Assigning xRange goes like this:

chartobject.seriescollection(1).XValues = Some Range

SeriesCollection(1) refers to the First series of the chart. You need to assign the xaxis range to any of the active series in the chart. By Active series, I mean, that the assigned Series should have atleast a non error value. Incase if series1 doesnt have any value(either numeric/blank) in any of its ranged cells, then VBA will not be able to find where it needs to plot the xrange and will throw an error.

3. If you are assigning the values to a series on RunTime, and the first cell contains Error, VBA throws back an error. So while assigning some values to a chart on RunTime, u have to make sure that the First Element in the series is not any Error Refrences.

Hope, that was helpful!

Thanks,
Vikas

Sort by Colors

In excel, sorting by colors is not a difficult situation. Anyone with a little knowledge of VBA can write this simple code. Just rush to the following link to read more:

http://www.cpearson.com/excel/SortByColor.htm

Thanks,
Vikas

Getting values from Databases quickly.

When you request some values from databases in excel, it probably takes few secs. For example, when you get values from Microsoft query section, it takes lesser time as compared to the procedure when you create a connection and enters the values record by record. Its always a better option to fetch the complete table and insert the whole table in excel. If your database is huge, this technique works really well. See the following code:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DSN=Your Oracle DSN;DBQ=Your database Service;uid=your account;pwd=your password;DriverId=25;FIL=Oracle;MaxBufferSize=2048;PageTimeout=5;" _
       )), Destination:=Range("$A$8")).QueryTable
        .CommandText = qur <-----(write your query like select * from something)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

The above code fetches the data from oracle and enters in the active excel sheet. It will return the table object based on the query you will give in the code, and insert the results in tabular format in no time. If you compare the process with the procedure of connecting and reading the rows one by one through recordset....its going to take 20 times longer than it should usually take!

Thanks,
Vikas

Macro for restricting users to overwrite the value in a sheet

Hi,

Please download the required macro from here.. Here, if someone will overwrite the value in Sheet1, the macro will ask for password(which is set to vikas now).If you dont enter a correct password, you will not be able to change the cells value.

URL for File : Click Here

Thanks,
Vikas