- Microsoft Excel Visual Basic Examples
- Excel Vba Example
- Example Visual Basic Code For Excel Data
- Excel Visual Basic Function Example
- Visual Basic Code Example Excel Spreadsheet
Picosdk-ms-excel-vba-examples is a set of Microsoft Excel Visual Basic for Applications (VBA) examples for PicoScope ® oscilloscope and PicoLog ® data logger products. Examples are provided for 32-bit and 64-bit version of Microsoft Excel and are grouped by driver name.
Examples to Use Excel VBA Code #1 – Paste Code in Module. To write VBA code, the first thing you need to do is go to the Developer tab and click on Visual Basic. You can also press the excel shortcut key “ALT + F11” to open the Visual Basic. As you open the visual basic, you will see a window like the below. Go to Insert and click on Module. This Visual Basic.NET example shows how you can resize your custom form using Advanced Task Panes for Excel 2019 - 2000. To change the form size programmatically, you set the splitter to none. Otherwise, only the user can resize the task pane using the splitter.
Once you start learning VBA one of the coolest things you can do is to write a VBA code to insert new a worksheet in a workbook.
Well, there is already a shortcut key to insert a new worksheet or you can also use the normal option but the benefit of using a VBA code is you can add multiple worksheets with a single click and you can also define that where you want to add it.
For this, you need to use the Sheets.Add method, and in this post, we will be learning how to use it to add one or more worksheets in a workbook.
Sheets.Add Method
- Before: To add a new sheet before a sheet.
- After: To add the new sheet before a sheet.
- Count: Number of sheets to add.
- Type: Type of the sheet you want to add (LINK)
Write a VBA Code to ADD a New Sheet in a Workbook
Open the visual basic editor and follow these steps.
- First, you need to enter Sheets.Add method.
- Then you need to define the place to add the new sheet (Before or After).
- Next thing is to enter the count of worksheets.
- In the end, the type of sheet.
Different Ways to Add New Sheets in a Workbook using a VBA Code
Below you have different ways to add a new sheet in a workbook:
1. Add a Single Sheet
To add a single sheet, you can use the below code, where you didn’t specify any argument.
This code tells Excel to add a sheet in the active workbook, but as you don’t have any argument it will use the default values and add one worksheet(xlWorksheet) before the active sheet.
Here’s one more way to write this, check out the below code.
As you are already in the active workbook you can use the below code as well. It does the same thing.
2. Add Multiple Sheets
To add multiple sheets in one go, you just need to define the COUNT argument with the number of sheets you want to add.
Now the count of the sheets that you have defined is 5, so when you run this code it instantly adds the five new sheets in the workbook.
3. Add a Sheet with a Name
If you want to rename the sheet after adding it, you can use the following code:
In the above code, we have used the name object (LINK) which helps you to specify the name of a sheet.
4. Add a Sheet with a Name from a Cell
You can also take the value to use as the sheet’s name from a cell.
In the above code, cell A1 is used to get the name for the new sheet.
5. Add a Sheet After/Before a Specific Sheet
As these arguments are already there in the Sheets.Add where you can specify the sheet to add a new sheet before or after it.
Now in the above code, you have two lines of code where you have used before and after an argument in the Sheet.Add method. So, when you run this code it adds two sheets one is before and one is after the “mySheet”.
6. Add a New Sheet at Beginning
By using the before argument using you can also add a sheet at the beginning of the sheets that you have in the workbook.
So basically, what we are going to do is we’re going to specify the sheet number instead of the sheet name.
In the above code, you have used the sheet number (1) that tells VBA to add the sheet before the sheet which is on the first position in all the worksheets. In this way, it will always add the new sheet at the beginning.
7. Add a New Sheet at End (After the Last Sheet)
To add a new sheet in the end you need to write the code in a different way. So, for this, you need to know how many sheets there in the workbook are so that you can add a new sheet at the end.
In the above code, Sheet.Count returns the count of the sheets that you have in the workbook, and as you have defined the after argument it adds the new sheet after the last sheet in the workbook.
8. Add Multiple Sheets and use Names from a Range
The following code counts rows from the range A1:A7. After that, it loops to add sheets according to the count from the range and use values from the range name the sheet while adding it.
But with the above code, there could be a chance that the sheet name you want to add already exists or you have a blank cell in the name range.
In that case, you need to write a code that can verify if the sheet with the same name already exists or not and the cell from where you want to take the sheet name is blank or not.
If both conditions are fulfilled only then it should add a new sheet. Let me put it in steps two steps:
First, you need to write an Excel User Defined Function to check if a sheet with the same name already exists or not.
Second, you need to write a code using this function and that code should also check if the name cell is blank or not.
Now in the above code, you have used the VBA IF Statement and in this statement, you have the sheet check function which checks for the sheet name and then you have a condition to check if the name cell has a blank value.
More Tutorials on Worksheets
VB.NET Excel examples
On this page you will find a set of HowTo samples that can help you to get started with add-in development for Excel 2019, 2016, 2013, 2010 and lower:
And here are a few more VB.NET examples for other applications of the Microsoft Office 2019 - 2000 suite:
Excel extensions: COM add-in, RTD server
Microsoft Excel Visual Basic Examples
How to develop a COM add-in for Microsoft Office (Excel, Word and PowerPoint)
This sample shared COM add-in for Excel, Word and PowerPoint shows how you can use Add-in Express for Office and .net to add custom command bars and command bar controls, place new controls onto Office Ribbon tabs, create custom task panes, handle application-level events and more.
Download COM addin
How to customize Microsoft Excel ribbon and toolbar
See how to create custom ribbons and toolbars for Excel and how to integrate with an existing Excel tab.
Download add-in
How to customize Excel main menu, context menus and Backstage view
VB.NET sample shows how to create a custom context menu for Excel 2019, 2016, 2013, 2010, 2007 and lower; how to add your own Backstage view item and how to customize the main menu in Excel 2003 - 2000.
Download sample
How to build your first Excel RTD server in VB.NET
See how to build an RTD server step-by-step in the Developer's Guide. The RTD server project is written in Visual Basic .NET. See also a video showing how to create an RTD server.
Download RTD server
Working with Excel Object model
How to handle the WorkbookBeforeSave event
How to create a custom event when Excel calculation mode changes
This VB.NET Excel addin example provides a solution for missing event that should occur then the user changes the calculation mode.
Download addin
How to find the last used cell (row or column) in Excel
This Visual Basic sample explains some reliable methods for finding the last used cell in an Excel worksheet or a range.
Download Find Last Cell add-in
How to populate Excel workbooks and ranges with arrays
Excel Vba Example
VB.NET code example shows how to use arrays to populate Excel with data; how to fill array with a query or range and insert into a worksheet, and more.
Download Populate with Arrays add-in
How to work with Excel tables & ranges using VB.NET
This sample Excel addin demonstrates how work with tables and ranges: create a new table or a range, insert a column or a row, sort, filter and more.
Download Excel Tables and Ranges add-in
How to work with Excel pivot tables
VB.NET code example shows how to automate pivot tables in Excel: create a PivotTable, add calculated fields, display or hide a field; delete, refresh or clear a pivot table; create a pivot chart and more.
Download Excel PivotTables Add-in
Advanced Excel Task Panes
Example Visual Basic Code For Excel Data
How to show and hide Excel Task Panes programmatically
This Visual Basic .NET Excel sample demonstrates how to set the visibility of an advanced task pane to make it show up from the command bar.
Download VB.NET sample
How to switch between several task panes programmatically
Excel Visual Basic Function Example
This addin shows how you can use several custom task panes in one position and switch between them programmatically.
Download example
Visual Basic Code Example Excel Spreadsheet
How to show an advanced Excel task pane dynamically
The Excel VB.NET add-in shows how to build a context-dependent custom task pane. The plug-in shows a task pane when cell A1 contains any value.
Download plug-in
How to re-size an Excel task pane in VB.NET
This Visual Basic .NET example shows how you can resize your custom form using Advanced Task Panes for Excel 2019 - 2000. To change the form size programmatically, you set the splitter to none. Otherwise, only the user can resize the task pane using the splitter.
Download sample plugin
User-defined functions
How to develop Excel Automation add-in
See how to create Excel Automation add-ins step-by-step in the Developer's Guide. The sample is written in VB.NET.
Download Automation add-in
How to develop XLL add-in
This sample project demonstrates how you can build an XLL addin in Visual Studio. You can find a detail description of this example in the Developer's Guide: How to create an XLL add-in.
Download XLL addin
Note. You can find plenty more HowTo examples on our technical blog. Be sure to check it out, we add new HowTo samples every week.