Author Archive


While Preparing an Excel Dashboard one needs to handle a lot of hiding and unhiding of Sheets for various users. For Devloper all sheets should be unhidden and for user some of them should visible. We need different views of one workbook for different people developer, user, admin, etc.
We can handle this with using a Custom View property of the workbook with VBA

ActiveWorkbook.CustomViews.Add Viewname, PrintSettings(value True/False), RowColumnSettings(value True/False)

(more…)

Advertisements

Adding Metadata to a Worksheet:CustomProperties

Posted: July 29, 2013 by kulshresthazone in Excel, VBA
Tags:

Want to keep some information a bit out of reach of the user or you want to keep some information for your code support, away from general user.

One way of storing information is using constants in your code. Now, suppose you have multiple sheets and you want to associate the constant’s values with specific sheets. Then, in place of creating a mapping of sorts or creating multiple constants for each sheet, you can make use of adding metadata to a worksheet.
To Add:

Worksheets(WorksheetName).CustomProperties.Add Name:=PropertyName, Value:= SomeValue

To Read:

Worksheets(WorksheetName).CustomProperties.Item(PropertyIndex).Value

Happy Coding…  :)

Text Comparison: Option Compare Text

Posted: July 12, 2013 by kulshresthazone in Excel, Outlook, Powerpoint, VBA
Tags: ,

If we compare text in VBA Code then “B” is not equal to “b” since there ASCII codes for both these characters are different. However we can force Excel code to compare text on the basis of  Text not on their Binary values.
Make use of  Option Compare Text on the very First line of the Code module. It will force VBA to compare Text in Text mode not in Binary mode. By Default Text comparison is done in Binary mode.

If we don’t use Option Compare Text then
“B”<>”b”
If we make use of Option Compare Text then
“B”=”b”

Happy Coding…


Converting a Range to Recordset is a very painfull process. Currently the process which we follow is establishing a connection and then firing a Query to get the values in to a Recordset.
But now we can make a new and better approach using the Range.Value() property.
This is a better way to move data from one workbook to another without making use of the Clipboard.


Sub Test()

        Dim xlXML             As Object
        Dim adoRecordset      As Object
        Dim rng               As Range

        Set rng = ActiveSheet.Range("A1:D20")
        Set adoRecordset = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument") 
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        adoRecordset.Open xlXML

End Sub

This adorecordset will hold the range in the form of recordset and one make use of this in the way it is required.
Happy Coding….   😉