Macro - VB script code to extract data from QC (HP ALM)

My understanding:

Note: Suggest to use Head first java book

-        Local variables are variables declared inside the method

-        Static keyword helps to allocate static memory to the variables and methods. What is the use – that way whenever object is created variables and methods in class will be allocated some memory, this happens for each object creation. So for each object creation memory is allocated. Here when you create variable as static, then you cannot have multiple memory allocation. Means address for static variable and method should be constant (cannot change).

              So now how is it useful to have single address?

a.      No duplicates

-        Selenium runs based on JRE, as it is java based application. If one has JRE installed on his machine, he should be able to open Eclipse. But is it necessary to show eclipse where JRE is located, like setting up environment variable to set the path?

-        How about identifying objects in webpage before automating it, like identify all objects in multiple website..that increases your confidence of identifying objects

-         

Start a Macro When Opening an Excel Spreadsheet

3/10/2016


Rename the macro name to 'Workbook_Open()'. Or sometimes Auto_Open().


or do this

Private Sub Workbook_Open()
'Enter your macro here
End Sub

Hervey Ball

- Hervey balls:

Harvey Balls are round ideograms used for visual communication of qualitative information. They are commonly used in comparison tables to indicate the degree to which a particular item meets a particular criterion.

One could create Harvey Balls in Excel, using Conditional Formatting -> Icon Sets->select the circle icon.

Note: Deleted the blog that says: Creating circles that dynamically change based on the % mentioned (like 23% completed, should show up 23% of circle shaded).

Pareto Analysis

SDLC stage: Pareto Analysis could be used during "Test Strategizing, Planning and Defect Management"


The link below will help you understand how to create Pareto chart that help will you find out 20% of reasons that created 80% issues:

http://www.excel-easy.com/examples/pareto-chart.html

 

Scenarios

10/24/2015:

• Compare excel ideas in two excel files, list down the ideas that are not common.

Solution1: You could copy the data of columns into  one sheet and apply Vlookup/Index(),Mattch() in 3rd column, with True/False status.

 - How to loop in excel without VBA or macros?

Source: http://stackoverflow.com/questions/4939537/how-to-loop-in-excel-without-vba-or-macros

• Find min/max values in a column among those matched from another column

http://stackoverflow.com/questions/20904092/excel-find-min-max-values-in-a-column-among-those-matched-from-another-column

for min:

=MIN(IF(($A$1:$A$50=D1),($B$1:$B$50)))

for max:

=MAX(IF(($A$1:$A$50=D1),($B$1:$B$50)))

Date

10/24/2015:

- I have a scenario where I need to convert the date to the first of the month

Date(Month(<cell>,Year<cell>,1) = this will convert the date to first of every month.

 --- Month(serial_no) = gives the month of serial no. in numerics

---- Year and Day does the same

---Date function = produces date with inputs year, month and day i,e  Date(<year>,<month>,<day>)

  What evervalue you put in the year, month and day place, value displayed would be date format and as per the value.



1/22/2016:

- Lets suppose if you need date in a specific format, do this:

= Month(date) & "/" & Day(date) & "/" & Year(date)

if you just put  =Month(date)/Day(date)/Year(date)  -- it will divide the month by day and a result by year. So & is must.


Use DateValue method: Use this method to overcome issues with Date property of the value you enter into the Excel.

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example.

Formula	Description	Result
=DATEVALUE("8/22/2011")	Serial number of a date entered as text.	40777
=DATEVALUE("22-MAY-2011")	Serial number of a date entered as text.	40685
=DATEVALUE("2011/02/23")	Serial number of a date entered as text.	40597
=DATEVALUE("5-JUL")	Serial number of a date entered as text, using the 1900 date system, and assuming the computer's built-in clock returns 2011 as the current year.	39634
=DATEVALUE(A2 & "/" & A3 & "/" & A4)	Serial number of a date created by combining the values in cells A2, A3, and A4.	40850

Evaluate a nested formula one step at a time--looking for shortcut command that does the same?

10/24/2015

Solution1: 

Source: https://support.office.com/en-us/article/Evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7

1. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
   
2. Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.
3. If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula NOTE The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.
4. Continue until each part of the formula has been evaluated
5. To see the evaluation again, click Restart.
6. To end the evaluation, click Close.

NOTES

• Some parts of formulas that use the IF and CHOOSE functions are not evaluated, and #N/A is displayed in the Evaluation box.
• If a reference is blank, a zero value (0) is displayed in the Evaluation box.
• The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula to give results different from what appears in the cell: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, RANDBETWEEN.

Solution 2:

Select cell with formula, go to each section of the formula that means select section 1 in the formula, once selected to to 'Formula' in menu bar and click evaluate. Do this every time, that way it will show you what section in formula is producing.