|
Ctrl+Z |
Undo |
|
Ctrl+C |
Copy, Paste & Multiple Paste Cut |
|
Ctrl+F, |
Find, |
|
Ctrl+P, Ctrl+F4 Alt+F4 |
Print, |
|
Ctrl+Arrow |
Move to edge of region |
|
Ctrl+* |
Select |
|
Ctrl+A |
Select all cells |
|
Ctrl+Home |
Select A1,
|
|
Ctrl+Shift+End |
Select from active cell to last cell in used range. |
|
Ctrl+Shift+Home |
Select from active cell to A1 |
|
Ctrl+Page
Down |
Move to
the next sheet, |
|
Ctrl+Tab |
Move to next open workbook |
|
Ctrl+N |
Open new workbook |
|
Shift+F11 |
Insert new worksheet |
|
Shift+F3 |
Paste function window |
|
=+FunctionName+Ctrl+A |
Insert new function |
|
Alt+F11 |
Open VBE |
|
Ctrl+Shift+Enter |
Array formula |
|
Ctrl+F3, |
Define name, Paste name |
|
Ctrl+Spacebar
|
Select columns, Select rows |
|
Ctrl+1, |
Format |
|
Ctrl+; |
Current |
Friday, November 9, 2012
Shortcut keys
Convert PowerPoint Presentation PPT to PDF
How to Convert PowerPoint Presentation PPT to PDF using VBA
PDF is always the universal format for sending the files. With lot of versions of MS Office and other Office suites around.
The following snippet converts the Presentation to a PDF and saves in the same folder of the PPT
ActivePresentation.ExportAsFixedFormat ActivePresentation.Path & "\" & ActivePresentation.Name & ".pdf", ppFixedFormatTypePDF, ppFixedFormatIntentPrint
'Get the name of the currently active file. You'll need this when
'toggelinig between two files, and you want to open the old file
'where the data is assembled
Dim OrginialFile
OriginalFile = Application.ActiveWorkbook.Name
'Open new file
Dim MyFile
MyFile = "C:Maria\Myfolder\Myfile.xls"
Workbooks.Open FileName:=MyFile
'Close a file
Dim MyNewFile
MyNewFile = "MyWonderfulFile.xls"
'Unable ScreenUpdating and DisoplayAlerts, so teh user isn't asked if he want tosave the changes
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Windows(MyNewFile).Close
Application.ScreenUpdating = true
Application.DisplayAlerts = true
'Toggle between open files.
Dim AnotherOpenFile
AnotherOpenFile = "MyWounderfullFile.xls"
Windows(AnotherOpenFile ).Activate
'toggelinig between two files, and you want to open the old file
'where the data is assembled
Dim OrginialFile
OriginalFile = Application.ActiveWorkbook.Name
'Open new file
Dim MyFile
MyFile = "C:Maria\Myfolder\Myfile.xls"
Workbooks.Open FileName:=MyFile
'Close a file
Dim MyNewFile
MyNewFile = "MyWonderfulFile.xls"
'Unable ScreenUpdating and DisoplayAlerts, so teh user isn't asked if he want tosave the changes
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Windows(MyNewFile).Close
Application.ScreenUpdating = true
Application.DisplayAlerts = true
'Toggle between open files.
Dim AnotherOpenFile
AnotherOpenFile = "MyWounderfullFile.xls"
Windows(AnotherOpenFile ).Activate
DeletePivotTables
Sub DeletePivotTables()
Dim Pt As PivotTable
Dim Ws As Worksheet
'Loop through worksheets
For Each Ws In ActiveWorkbook.Worksheets
Worksheets(Ws.Name).Select
'Loop through pivot tables
For Each Pt In Ws.PivotTables
'Delete pivot table
Pt.PivotSelect "", xlDataAndLabel, True
Selection.Delete Shift:=xlToLeft
'Exit Sub 'Optional: Get out
Next Pt
Next Ws
End Sub
Dim Pt As PivotTable
Dim Ws As Worksheet
'Loop through worksheets
For Each Ws In ActiveWorkbook.Worksheets
Worksheets(Ws.Name).Select
'Loop through pivot tables
For Each Pt In Ws.PivotTables
'Delete pivot table
Pt.PivotSelect "", xlDataAndLabel, True
Selection.Delete Shift:=xlToLeft
'Exit Sub 'Optional: Get out
Next Pt
Next Ws
End Sub
Check Extension
Sub CheckExtn()
Dim MinExtensionX
Dim Arr() As Variant
Dim lngLoc As Variant
'Retrieve extension of file
MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)
Arr = Array("xls", "xlsx") 'define which extensions you want to allow
On Error Resume Next
lngLoc = Application.WorksheetFunction.Match(MinExtensionX, Arr(), 0)
If Not IsEmpty(lngLoc) Then '
'DO STUFF if it's an xls/xlsx file. Otherwise, chose next file in folder
End If
Monday, October 15, 2012
VBA Controls
This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010, Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.
1 Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.
2 Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.
3 Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.
4 Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.
5 Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.
6 Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.
1 Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.
2 Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.
3 Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.
4 Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.
5 Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.
6 Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.
VBA Programming
This section is for users who want to get the most out of Excel VBA. Excel VBA Programming is not difficult, but you do need to know the keywords used in Excel VBA.
1 Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.
2 String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.
3 Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.
4 If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.
5 Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.
6 Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.
7 Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none? Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.
8 Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet.
In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.
9 Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.
10 Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.
11 Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.
12 Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.
1 Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.
2 String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.
3 Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.
4 If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.
5 Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.
6 Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.
7 Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none? Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.
8 Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet.
In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.
9 Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.
10 Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.
11 Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.
12 Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.
Subscribe to:
Posts (Atom)