Excel Vba Cheat Sheet

-->

Excel Vba Language Reference Pdf
Excel Vba Cheat Sheet 2013
Access Vba Cheat Sheet
Vba Code Writing Cheat Sheet
Excel Vba Reference Sheet With Space In Name

VBScript Regular Expressions Cheat Sheet 14 May 2007 08:44 VBScript. Metacharacters for use with VBScript regular expressions Character Description Marks the next.

A collection of all the sheets in the specified or active workbook.

Remarks

VBA Cheat Sheets Reference this page for lists of all common VBA Commands & Syntax. You will find many basic commands (ex. Insert a sheet) and some advanced syntax (ex. Working with arrays).
These 6 Cheat Sheets are filled with everything you need to know to become an Excel VBA expert. The Cheat Sheets were made with you in mind. Each page of these multi-column Cheat Sheets is packed with information designed to make you a better VBA developer. Get all 6 Cheat Sheets for one low price!
Some Excel Formulas Cheat Sheet is given below. The table contains the name of the function, the meaning of the function, the syntax, and the example. The above-explained cheat sheet of excel formulas examples is implemented in the excel worksheet shown in the following screenshot. Popular Course in this category.

The Sheets collection can contain Chart or Worksheet objects.

The Sheets collection is useful when you want to return sheets of any type. If you need to work with sheets of only one type, see the object topic for that sheet type.

Example

Use the Sheets property of the Workbook object to return the Sheets collection. The following example prints all sheets in the active workbook.

Use the Add method to create a new sheet and add it to the collection. The following example adds two chart sheets to the active workbook, placing them after sheet two in the workbook.

Use Sheets (index), where index is the sheet name or index number, to return a single Chart or Worksheet object. The following example activates the sheet named Sheet1.

Use Sheets (array) to specify more than one sheet. The following example moves the sheets named Sheet4 and Sheet5 to the beginning of the workbook.

Methods

Properties

Data Types

Kind	Literal	Comment
Null	null	Empty value, void 1 * null = null // be careful!
Logical	true / false
Number	0 1 -1 1.5 2.3e-5, 0xff	Whole / decimal number, number in hex
Time	#time(9, 15, 0)	#time( hour, minute, second ) #time(24,0,0) = #time(0,0,0) If hour is 24, then minute and second must be 0 0 ≤ hour ≤ 24, 0 ≤ minute ≤ 59, 0 ≤ second ≤ 59
Date	#date(2013, 2, 26)	#date( year, month, day)
DateTime	#datetime(2013, 2, 26, 9, 15, 0)	#datetime( year, month, day, hour, minute, second )
DateTimeZone	#datetimezone(2013, 2, 26, 9, 15, 0, 9, 0)	#datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes ) 0 ≤ year ≤ 9999, 0 ≤ month ≤ 12, 1 ≤ day ≤ 31 0 ≤ hour ≤ 23, 0 ≤ minute ≤ 59, 0 ≤ second ≤ 59 -14 ≤ offset-hours + offset-minutes / 60 ≤ 14
Duration	#duration( 0, 1, 30, 0)	#duration( days, hours, minutes, seconds )
Text	“hello”	Just text in quotes Special characters =”#(cr,lf)” same as =”#(cr)#(lf)”, string to check =”a#(cr,lf)b” = “a#(tab)b” // a b = “a” & “b””c” // ab”c
Binary	#binary(“AQID”)	If you work with binary – you know
List	{ 1, 2, 3 }, { 1 .. 10 }, {“A”..”Z”, “a”..”z”}	Comma separated values in curly brackets
Record	[ A=1, B=2 ]	Comma separated “Field Name = Value” in square brackets
Table	Simple way: #table( { “X”, “Y” }, { { 1, 2 }, { 3, 4 } } ) Preferable: with specified column types #table( type table [Digit = number, Name = text], { {1,”one”}, {2,”two”}, {3,”three”} } )	result: #table( list of field names, list of lists with values for rows of future table ) #table( { “Field1 Name”, “Field2 Name” }, { { “Field1 Value1”, “Field2 Value1” }, { “Field1 Value2”, “Field2 Value2” }, { “Field1 Value3”, “Field2 Value3” } } ) Empty table: #table( {“A”, “B”}, {} )
Function	(x) => x + 1 in general: (optional Num as nullable number) => Num + 1	( arguments ) => some operations. “nullable” is enough to make argument optional. “optional” is enough to let null be passed to a function.
Type	type{ number } // list type table [A = any, B = text]

Operator	Result	x = y	Equal
x > y	Greater than	x<>y	Not equal
x >= y	Greater than or equal	x or y	Conditional logical OR
x < y	Less than	x and y	Conditional logical AND
x <= y	Less than or equal	not x	Logical NOT

PowerQuery code shortcuts

IF / THEN / ELSE

Result = if T>0 then A else B // low case if / then / else, M is case sensitive

TRY / CATCH – error handling

Result = try A/B otherwise 0 // low case “try [some action] otherwise [some action/object]”

Excel cell value (Named Range consisting of one cell)

Result = Excel.CurrentWorkbook(){[Name=”CELLNAME”]}[Content]{0}[Column1]

Rename Columns according to “Renaming Table”

Renamed_Columns = Table.RenameColumns(TARGET, Table.ToColumns(Table.Transpose(RENAMING_TABLE)), MissingField.Ignore),

where RENAMING_TABLE looks like (headers do not matter)

Create a table from thin air

For example, when response is null but you want to keep structure of your PowerPivot table

= #table( {“A”, “B”}, {} ) – empty table, simple approach

Or with defined column types

= #table( type table [A = text, B = number], {} ) – empty table

= #table( type table [A = text, B = number], { {“one”, 1}, {“two”, 1} } )

ISNUMBER() analog

= Value.Is(Value.FromText( VALUE ), type number)

Or:

= “sample” is number // false, = 123 is number // true

Excel Vba Language Reference Pdf

ISTEXT() analog

= Value.Is(Value.FromText( VALUE ), type text)

Or:

= “sample” is text // true, = 123 is text // false

Expressions

“Hello World” // a text value

123 // a number

1 + 2 // sum of two numbers

{1, 2, 3} // a list of three numbers

[ x = 1, y = 2 + 3 ] // a record containing two fields: x and y

(x, y) => x + y // a function that computes a sum

if 2 > 1 then 2 else 1 // a conditional expression

let x = 1 + 1 in x * 2 // a let expression

error “A” // error with message “A”

Recursion

[ Factorial = (n) =>

if n <= 1 then

else

n * @Factorial(n – 1),

x = Factorial(5)

]

// @ is scoping operator

Operations with date and time in Power Query

Time

#time( hour, minute, second )

Operator	Left Operand	Right Operand	Meaning
x + y	time	duration	Date offset by duration
x + y	duration	time	Date offset by duration
x – y	time	duration	Date offset by negated duration
x – y	time	time	Duration between dates
x & y	date	time	Merged datetime

Date

#date( year, month, day)

Operator	Left Operand	Right Operand	Meaning
x + y	date	duration	Date offset by duration
x + y	duration	date	Date offset by duration
x – y	date	duration	Date offset by negated duration
x – y	date	date	Duration between dates
x & y	date	time	Merged datetime

DateTime

#datetime( year, month, day, hour, minute, second )

Operator	Left Operand	Right Operand	Meaning
x + y	datetime	duration	Datetime offset by duration
x + y	duration	datetime	Datetime offset by duration
x – y	datetime	duration	Datetime offset by negated duration
x – y	datetime	datetime	Duration between datetimes

Duration

#duration( days, hours, minutes, seconds )

#duration(0, 0, 0, 5.5) // 5.5 seconds

#duration(0, 0, 0, -5.5) // -5.5 seconds

#duration(0, 0, 5, 30) // 5.5 minutes

#duration(0, 0, 5, -30) // 4.5 minutes

#duration(0, 24, 0, 0) // 1 day

#duration(1, 0, 0, 0) // 1 day

Operator	Left Operand	Right Operand	Meaning
x + y	datetime	duration	Datetime offset by duration
x + y	duration	datetime	Datetime offset by duration
x + y	duration	duration	Sum of durations
x – y	datetime	duration	Datetime offset by negated duration
x – y	datetime	datetime	Duration between datetimes
x – y	duration	duration	Difference of durations
x * y	duration	number	N times a duration
x * y	number	duration	N times a duration
x / y	duration	number	Fraction of a duration

Main source of info is M language specification: https://msdn.microsoft.com/en-us/library/mt807488.aspx

Recommended blogs

http://datachant.com/ – Gil Raviv (@gilra)

https://blog.crossjoin.co.uk/ – Chris Webb (@Technitrain)

http://exceleratorbi.com.au/ – Matt Allington (@ExceleratorBI)