He has completed his MBA (Finance) from University of Massachusetts, Lowell and had worked for Village Software (www.villagesoft.com) for a year. Village Software is a key developer of spreadsheet applications (the free templates in MS-Office 97 and MS-Office 2000 are from Village Software).

He enjoys programming with Microsoft technologies as they are fairly easy to use and developers can build fairly complex solutions for customers with visually appealing interfaces quickly. He is currently working on a DHTML Interface, which encapsulates the Web Browser control to create a “web” like interface in a Window
emptyness: hi Subhashini...
emptyness: what do you have for us today?!
Subhashini (Moderator): This chat will last for one hour. During this hour, our Expert will respond to as many questions as they can. Please understand that there may be some questions we cannot respond to due to lack of information or because the information is not yet public. We encourage you to submit questions for our Experts. We ask that you stay on topic for the duration of the chat. This helps the Guests and Experts follow the conversation more easily. We invite you to ask off topic questions after this chat is over, but not during.

Please only submit your question once. Submitting your question more than once is against the Chat Code of Conduct
Jacks: hi
Subhashini (Moderator): Hi everybody, the chat will start at 5.00 pm IST
Jacks: what do you have for us today?!
Jacks: ok
Subhashini (Moderator): Today is a discussion to allow developers a
better understanding of the Microsoft Excel Object Model and who wish to build solutions with Excel
VBA or Automate Excel with the focus on:
1. Excel Object Model Top Level Objects - Application, Workbooks, Worksheet, Range, Charts.
2. Create User Defined Functions in VBA code which can be placed on an Excel worksheet.
3. How to use Excel Formulas in VBA code.
4. Q & A on Excel programming, building UI in Excel VBA (User Forms).
Subhashini (Moderator): The first 20-25 mins , Naresh will discuss on the topic
Subhashini (Moderator): And we will follow it with Q&A for the rest of the 35-40 mins
Jacks: hi deept
Subhashini (Moderator): Please welcome Naresh Nichani (displayed as : Naresh[Expert] ) as the expert of this chat.
Subhashini (Moderator): Hope you find this chat informative and productive
Naresh [Expert]: Hi everyone and welcome
Jacks: hi naresh
Jacks: how r u?
Mani: hi Naresh & suba
Subhashini (Moderator): Hi everybody, thanks for joining the chat and taking time off your busy schedule for this.
amish: HI FRIENDS
Jacks: this is jagdish here
amish: hi naresh
Jacks: hi subhashini
Naresh [Expert]: I assume most of you are into Office development of some sort. I will start basic development concepts in Excel -- I welcome questions at any point and we should have time for some Q&A at end as well
Jacks: I would like to know regarding web hosting
Naresh [Expert]: web hosting - not connected with this.
Ashok: welcome Naresh
Naresh [Expert]: To build solutions in Excel we can write VBA code or automate Excel using .Net or Vb6
Naresh [Expert]: You would use VBA code to build a solution in Excel if the solution is fully in Excel - for example a Tax Planning spreadsheet
Naresh [Expert]: You could automate Excel with say Vb6 or VB.Net if your program needs to open an Excel file to get data or send a report to Excel
Naresh [Expert]: However for all these solutions we need to understand the Excel Object Model
Naresh [Expert]: The top level object in Excel is called Application
Naresh [Expert]: When you work with VBA code you need not refer to the Application Object specifically - however if you automate Excel from .Net or VB6 you need to create an instance of the Excel Application Object and then refer to it.
Naresh [Expert]: If you were running Vb6 you could refer to Excel Application like this
Naresh [Expert]:
Dim objExcel As Excel.Application
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
End If
Naresh [Expert]: The GetObject looks for a running instance of Excel - if it is not running the CreateObject create a new instance of Excel
Naresh [Expert]: If you are doing this in VBA this is not needed
Naresh [Expert]: In VBA Excel is open already and you refer to it with the keyword "Application"
Naresh [Expert]: Does this make sense?
Naresh [Expert]: OK I guess it makes sense. Now let’s get to some methods of the Application Object
Naresh [Expert]: One key property is Application.ScreeenUpdating = False
Naresh [Expert]: With this line we can turn of screen updates. Image you are writing a long report from a database to Excel - if you do this it will work real quick
Naresh [Expert]: When you are done do this - Application.ScreenUpdating = True
Naresh [Expert]: The Application Object also lets you open an existing workbook or create a new workbook
Naresh [Expert]:
Dim wb as Excel.Workbook
Naresh [Expert]:
Dim wb as Excel.Workbook
Set wb = Application.WorkBooks.Open(strworkbookName)
Naresh [Expert]: This code opens a workbook with path strWorkBookName
Naresh [Expert]: The wb variable holds a reference to the Workbook object
Naresh [Expert]: To create a new workbook we would do it like this
Naresh [Expert]:
Set wb = Application.Workbooks.Add
Naresh [Expert]: This create a new workbook with blank worksheets
Naresh [Expert]: Now the next object we need to know about is Worksheet and Range
Naresh [Expert]: To set a reference to a specific worksheet in your workbook you can write code like this
Naresh [Expert]:
Dim wks As Excel.Worksheet
Set wks = wb.Worksheets(1)
Naresh [Expert]: This create a reference to the first worksheet in the wb workbook
Naresh [Expert]: Within the worksheet object we have a key Range Object
Naresh [Expert]:
Dim wks As Excel.Worksheet
Dim rg As Excel.Range
Set wks = wb.Worksheets(1)
Set rg = wks.Range("A1")
Naresh [Expert]: Here the variable rg refers to cell "A1" of the worksheet wks
Naresh [Expert]: Once you have a range you can read it contents, set it contents, set a formula and use the power of Excel
Naresh [Expert]: TO put some text into a range do it like this
Naresh [Expert]:
rg.Value = "Hello"
Naresh [Expert]: We can also put a formula into a range like this
Naresh [Expert]:
rg.Formula = "=Sum(B1:B10)"
Naresh [Expert]: This puts the Sum formula for range B1:B10 into the rg cell
Naresh [Expert]: Any questions on this?
Naresh [Expert]: OK Lets go on for now. The next thing I will convert is User Defined Functions in Excel
Naresh [Expert]: Excel has a number of built in questions like Sum, Average, Max, Min etc
Naresh [Expert]: We can also create our own functions using VBA code
Naresh [Expert]: For example you want to calculate the Tax payable and this depends on Salary
Naresh [Expert]: If Salary is less than 100,000 tax is 10%, if it is less than 150,000 it 10% for the first 100,000 and then 15% on the balance, it is more than 150,000 then is 10% for the 100,000, 15% on 50,000 and 25% for anything over that
Naresh [Expert]: Create a Module in VBA by clicking Insert | Module
Naresh [Expert]: Here type this code
Naresh [Expert]:
Public Function CalcTax(dbl1 As Double) As Double
If dbl1 < 100000 Then
CalcTax = dbl1 * 0.1
ElseIf dbl1 < 150000 Then
CalcTax = (100000 * 0.1) + ((dbl1 - 100000) * 0.15)
Else CalcTax = (100000 * 0.1) + (50000 * 0.15) + ((dbl1 - 150000) * 0.25) End If End Function
Naresh [Expert]: This is simple Vb code which takes a parameter dbl1 of type Double - this is the base salary and returns a Double number
Naresh [Expert]: In any Excel cell A1 type salary of 160000, in A2 type Formula =CalcTax(A1) and you will get a answer
Naresh [Expert]: A key factors is controlling recalculation in your Excel formula
Naresh [Expert]: Normally your Excel formula will recalc if any of the dependent cells change - in our case if A1 changes it will recalculate
Naresh [Expert]: If you want your Formula to recalc if "ANY" cell changes type this line in the VBA function first line
Naresh [Expert]: Application.Volatile
Naresh [Expert]: However this can slow down your formula - but it will always recalc if anything on spreadsheet changes
Naresh [Expert]: OK so much for Excel User Defined function - basically you can create custom functions in VBA and then insert them into Excel cells to work with other Excel formula etc.
Naresh [Expert]: Another nice feature of Excel I link is that I can use an Excel function like say a VOOKUP inside my VBA code
Naresh [Expert]: Sorry VLOOKUP
Naresh [Expert]: VLOOKUP looks up a value in a vertical list and returns an Offset column. So say I had Name and Age - I could look for a Name and gets the Age easily with VLOOKUP
Naresh [Expert]: This can be done on an Excel Sheet and in VBA code as well
Naresh [Expert]: To do this on Excel Sheet you would have a formula like this =VLOOKUP("Abhi",B12:C15,2). This looks for Abhi in rage "B12 to C15" and returns the second column value for Abhi
Naresh [Expert]: In VBA the same thing can be done like this
Naresh [Expert]:
Application.VLookup("Abhi", Range("B12:C15"), 2)
Naresh [Expert]: So instead of "B12:B15" we use the Range Object and say Range("B12:B15")
Naresh [Expert]: Sorry Range("B12:B15")
Naresh [Expert]: That should be a closing bracket at end
Naresh [Expert]: An Excel app you develop may need some custom forms and menus as well (UI)
Naresh [Expert]: To create forms in VBA we have something called UserForms
Naresh [Expert]: To create a User Form start the VBE (Visual Basic Editor) (Tools | macros | Visual Basic Editor)
Naresh [Expert]: Here click Insert | UserForm
Naresh [Expert]: A Userform is a form designer where you can place controls, set props etc just like other designers
Naresh [Expert]: How do we invoke a UserForm in Excel assuming we have one setup?
Naresh [Expert]: To do this you need to write some code in a Module like this
Naresh [Expert]:
Dim us As New UserForm1
us.Show vbModal
Unload us
Set us = Nothing
Naresh [Expert]: Here we have a UserForm called UserForm1
Naresh [Expert]: I instantiate a new instance of this Userform and show it Modally - once it is done Unload it and set it to Nothing to clean up memory
Naresh [Expert]: In a userform you could accept User Input and paste that input into a range and then do some calcs as needed
Naresh [Expert]: How do we invoke this code in the Module which invokes the UserForm?
Naresh [Expert]: Create a button in Excel - to do this add the "Forms" CommandBar - right click on any CommandBar and click Forms.
Naresh [Expert]: Here click the Button control and add a Button to your worksheet
Naresh [Expert]: It will prompt you for which Macro to run - select the procedure above you just made
Naresh [Expert]: OK I will now open up to Q & A - Excel is huge and I can go on forever
r: Dear Naresh, if you could tell us something about PIVOT tables
Sai Ashok: Thank you Naresh. Nice explanation about Excel and Excel object
Sai Ashok: Hi, Ashok from cognizant technology solutions. Is there anyway to import data in specific cells from one sheet to another using VB?
Naresh [Expert]: Yes there is
Naresh [Expert]: You could do like this
Venkat: Thank you naresh. Can you give me details about xll files.
Naresh [Expert]:
Set rgCopyFrom = wksCopyFrom.Range("A1:B100")
rgCopyFrom.Copy
Set rgCopyTo = wksCopyTo.Range("A1")
rgCopyTo.Paste
Naresh [Expert]: wksCopyFrom refers Worksheet to copy from
Naresh [Expert]: Just use a copy and paste - you could do it cell by cell like this as well
Naresh [Expert]: For Each cl in wksCopyFrom.Range("A1:B100")
Naresh [Expert]: Here cl is a Range object which refers to a single cell in the range A1:B100 and will loop through all cells there
Naresh [Expert]: Not very sure about xll - I think they are for Excel4 macros?
Naresh [Expert]: Excel4 uses Macro sheets and not VBA
Naresh [Expert]: So you have a RegisterXLL method to register them I think. This is like old Lotus123 macros
Naresh [Expert]: VBA is far superior as far as I can tell
Naresh [Expert]: PIvot Tables - they are really good. You can basically cross tabulate data. Imagine you have a large data in SQL Server or Access
Venkat: Actually I am using an Excel add-in but I came to know that the performance will be increased if we use xll files in which we can use c++ to code.
Naresh [Expert]: Click Data | Pivot Table and Pivot Chart on menu and select your data source (it can be Excel or a database)
Naresh [Expert]: Here you can have one or more columns as a Row and one or more as Columns and a numeric field in the middle which is Summed or Averages
Naresh [Expert]: So you could see the Total Sales by State by Employee for example
Naresh [Expert]: From a database
Naresh [Expert]: As data changes this will refresh with click of button
Naresh [Expert]: You can chart it, filter it etc with all of Excel analytical power
r: There is an option called save with data. What does that mean?
Naresh [Expert]: It will cache the data
Naresh [Expert]: So you can work with it offline
M_Rajesh: Q: How different has VBA changed now in Visual Studio Tools for Office. What are the benefits we have in VSTO over VBA.
Naresh [Expert]: Pivot tables are part of Excel object model and you can automate it as well
r: Doesn't it get refreshed when data in original source changes
Naresh [Expert]: VSTO uses managed code and has all of .Net runtime with it - so it is kind of .Net power + Excel power
Naresh [Expert]: Next version of VSTO with Whidbey has a lot of great enhancements
Naresh [Expert]: You can write to the Task Panes on the right and build your forms there - much better than User Forms
Said Ashok: how can I give password (password protect the excel file) for the newly created excel file using vb 6
Naresh [Expert]: In the workbook object "SAveAS" method there is an option to set the password
Sai Ashok: ok fine
Naresh [Expert]: In Vb6 you need to create a reference to the Excel Object Model first like "Microsoft Excel Object Library 11.0" for Excel 2003
M_Rajesh: Q: How can applications in VBA be ported to VSTO?
Naresh [Expert]: There is no direct method - UserForms need to be converted to Winforms
Naresh [Expert]: The Application Object needs to reference the Excel Application Object
Naresh [Expert]: Other VBA code will translate almost correctly in VB.Net with new minor changes as long as it is Excel related and does not use other COM objects
Naresh [Expert]: But no automated way as far as I know
Naresh [Expert]: You can build solutions in Excel with .Net without VSTO as well by referring to the Excel InterOp assembly as well
jigs[dotnetjini]: Excel InterOp assembly is always leaving its footprint behind.
jigs[dotnetjini]: I mean the object does not get released
Naresh [Expert]: Did you set to Nothing and do a Quit on the App object?
Naresh [Expert]: The Excel Application should be Quit and then set to Nothing
jigs[dotnetjini]: All stunts applied , but no effect.
Naresh [Expert]: Otherwise it may still run hidden
Subhashini (Moderator): We have five more mins left to conclude this chat.
Naresh [Expert]: I have seen this earlier - there are some links on this. Let me check and get back to you. Can you email me at nareshnichani@hotmail.com and I will dig up some stuff for you
Subhashini (Moderator): For any additional questions could be answered by Naresh
Subhashini (Moderator): Please mail him at nareshnichani@hotmail.com
Venkat: Is there any way to know that whether a workbook has been closed or not in .net application. Because we have only workbook before close event and no event like workbook closed?
Subhashini (Moderator): We can take one last question
jigs[dotnetjini]: Sure,.. I have observed that when application Quit after few minutes footprints goes..
Naresh [Expert]: You are right no Workbook Closed
Naresh [Expert]: There may be a WindowClose event and you can check WIndow Type?
Naresh [Expert]: No no WindowClosed either
Naresh [Expert]: One note - do not automate Excel from a Web App
Naresh [Expert]: Instead use XML to generate Excel files and then send to user (assuming they have Office 2003)
jigs[dotnetjini]: Can you brief on security (rights) required to use interop with excel.
Naresh [Expert]: Not sure on that.
jigs[dotnetjini]: Currently I have given Everyone full control. explicitly
Naresh [Expert]: Does not work huh?
Subhashini (Moderator): Guess we have timed out . And you can reach Naresh for any queries at nareshnichani@hotmail.com
Subhashini (Moderator): I thank you all for attending this chat and hope it was informative
Naresh [Expert]: Thanks everyone for your patience and please email me if any questions. Allow a day or two for replies normally
Subhashini (Moderator): Thank you Naresh for sharing the information with us.
M_Rajesh: Thanks Naresh, it was a very informative session. Thanks for taking your time to share your knowledge with us today.
Sai Ashok: Thank u Naresh
Subhashini (Moderator): The next Webchat is scheduled on Nov 24'th .
Subhashini (Moderator): November 24'Th Webchat by Naveen K S (MVP) -
Performance & Stress Testing using WAS & ACT tools

Testing considered a different ballgame altogether by most developers and designers, integrated
inevitably with software development life cycle; carries more importance when we talk about
Performance & Stress testing. This is also one of the requirements to design and build enterprise &
mission critical applications.
Join this discussion on Performance & Stress testing as we talk about:
--Performance modeling and objectives
--Stress testing & its objectives
--WAS & Act tools and its significance
Subhashini (Moderator): Thank you for your time Naresh
Naresh [Expert]: Thanks All Bye
jigs[dotnetjini]: OK. Bye EveryOne.
Mani: Bye EveryOne
Sai Ashok: ok. see on Nov 24th.
Mani: HAVE A NICE DAY
Subhashini (Moderator): Have a nice evening !