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

Implementing Arrays in Excel

One of the key things in the excel is to compare/find the values in an array....when we specify a range in any function, the excel needs to be intimated that whether we are pointing at a single cell or an array. For example, I am checking that if a single cell is having 0 or not :

If(B1:b10=0,True,False)...Suppose if I have entered the farmulae in C5....it will check if the value in B5 is 0 or not...but if we want this farmulae to check if every cell in the range b1:b10 is 0 or not....we will have to tell excel to use the range as array..for that after putting the farmulae, press ctrl+Shift+Enter rather than the traditional Enter...

Want to know more about Excel. Here are some links given by my frnd OldChippy from Excelforum.com(The Maximum Knowledge Forum)


http://www.ozgrid.com/Excel/arrays.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html
http://www.personal-computer-tutor.c...ayformulas.htm
http://www.excel-vba.com/excel-27-array-formulas.htm

Thanks,
Vikas B