Excel And VBA Tips

Sub Time_Now() Dim s As String s = Now() Cells(1, 1) = s End Sub

For ... Next   Use  For ... Next  loop if the number of loops is already defined and known.  A  For ... Next  loop uses a counter variable that increases or decreases in value during each iteration of the loop.  This loop structure is being used the most for our examples on this site.   Here is an example of the  For ... Next  loop:       For  i = 1 to 10         Cells(i, 1) = i      Next  i      In this example, i is the counter variable from 1 to 10.  The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column. Note that the counter variable, by default, increases by an increment of 1 For ... Next Loop With Step You can use the  Step  Keyword to sepcify a different increment for the counter variable. For example:      For  i = 1 to 10  Step  2         Cells(i, 1) = i      Next  i This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one.      You ca

Select Case  statement is an alternative to the ElseIf statement.  This method is more efficient and readable in coding the the  If ... Then ... ElseIf  statment.  Example:      Select Case  Grade          Case Is  >= 90             LetterGrade = "A"          Case Is  >= 80             LetterGrade = "B"          Case Is  >= 70             LetterGrade = "C"          Case Is  >= 60             LetterGrade = "D"          Case Else             LetterGrade = "Sorry"       End Select

The  If ... Then ... Else  statement is used to define two blocks of conditions - true and false. Example:             If  Age >=22  Then         Drink = "Yes"      Else         Drink = "No"      End If Note that End If statement is needed in this case as well since there is more than one block of statements   The  IF ... Then ... ElseIf  is used to test additional conditions without using new If ... Then statements. For Example:      If  Age >= 18 and Age < 22  Then         Msgbox "You can vote"      ElseIf  Age >=22 and Age < 62  Then         Msgbox "You can drink and vote"      ElseIf  Age >=62  Then         Msgbox "You are eligible to apply for Social Security Benefit"      Else         Msgbox "You cannot drink or vote"      End If Note that the last condition under Else is, implicitly, Age < 18

Decision Structures - IF and Select Case IF ... Then Statement The  IF ... Then  is a single condition and run a single statement or a block of statement. Example, the following statement set variable Status to "Adult" if the statement is true:          If Age >= 18 Then Status = "Adult" You can also use multiple-line block in the If statement as followed:            If  Ago >= 18  Then                Status = "Adult"               Vote = "Yes"          End If

Press To Do This Press To Do This Alt+F11 Open the VBA editor F1 Get help on any selected item Alt+F8 Display a list of macros F2 Display the Object Browser window Ctrl+Break Perform an emergency stop of the program F4 Display the Properties window Ctrl+G Display the Immediate window F5 Start the program Ctrl+I List the quick information for the selected element F7 Display the Code window after selecting a form or control Ctrl+J List the properties and methods for an object F9 Add a breakpoint Ctrl+R Display Project Explorer Shift+F7 Display the form that corresponds to the active Code window Ctrl+Shift+I List the parameter information for the selected element Shift+F9 Add a quick watch for the highlighted text Ctrl+Shift+J List the constants associated with an enumeration Ctrl+Tab Move to the next Code or UserForm window

Function What It Does Abs Returns a number's absolute value Array Returns a variant containing an array Asc Converts the first character of a string to its ASCII value Atn Returns the arctangent of a number Choose Returns a value from a list of items Chr Converts an ANSI value to a string Cos Returns a number's cosine CurDir Returns the current path Date Returns the current system date DateAdd Returns a date to which a specified time interval has been added — for example, one month from a particular date DateDiff Returns an integer showing the number of specified time intervals between two dates, for example the number of months between now and your birthday DatePart Returns an integer containing the specified part of a given date — for example, a date's day of the year DateSerial Converts a date to a serial number DateValue Converts a string to a date Day Returns the day of the month

{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($F$1:F2,$A$2:$A$8),0),1)}

In Excel, the  ADDRESS  function returns a text representation of a cell address. The syntax for the  ADDRESS  function is: ADDRESS( row, column, [ref_type], [ref_style], [sheet_name] ) row is the row number to use in the cell address. column is the column number to use in the cell address. ref_type is optional. It is the  type of reference  to use. It can be any of the following values: Value Explanation 1 Absolute referencing. For example: $A$1 2 Absolute row; relative column. For example: $A1 3 Relative row; absolute column. For example: A$1 4 Relative referencing. For example: A1 If this parameter is omitted, the ADDRESS function assumes that the ref_type is set to 1. ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values: Value Explanation TRUE A1 style referencing FALSE R1C1 style referencing If this parameter is omitted, the ADDRESS function assumes that the ref_style is set to TRUE.

Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time. Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is unchanged for the purpose of formulas and calculations. How to enter a custom cell format Select the cell/s you want to format then open the Format Cells window. The quick way just  press CTRL+1 Or the way most people do it is to  right click and select ‘Format Cells’ . On the Number Tab select Custom from the Category list. Note: It’s handy to have the text you want to format in the cell before you press CTRL+1 because Excel will give you a sample view of what the text is going to look like in the Format Cells window, so you can see before pressing OK, if it’s what you want. How to make your cell formats l ook the way you want Custom Cell Formats Text Before Formatting Custom For