VBA-Excel: Storing multiple data In the Windows Clipboard

In earlier sessions  you saw how to put text in the windows clipboard (Putting Text In The Windows Clipboard) with the help of PutInClipboard() and Data Object and how to get the text from the Clipboard using GetFromClipboard() and Data Object.

In this session you will learn how to store multiple data’s in Clipboard and retrieve them. For storing multiple data’s, you need to store values as pair, Keys and Values or you can say format identifier and while retrieving the data from the Clipboard you will use these format identifiers to get the data. This data can be String, Long, Integer.

For storing multiple data in Clipboard, Follow the below steps.

Read moreVBA-Excel: Storing multiple data In the Windows Clipboard

VBA-Excel: Array Functions – Split()

Description:

 Split() Function returns one dimensional array containing the substrings, after splitting the main string based upon the delimiter provided.

Format:

Split(strMainString [, delimiter[,intlimit[, vbCompare]]])

Arguments:

VBA-Excel: Arrays – Multi Dimensional Array

Multi Dimensional array has more than 2 dimensions.

For creating multi dimensional array, Follow the below status:

  • Declare the multi dimensional Array
  • Store values in array
  • Retrieve values from array

 

Declare the multi dimensional Array

Dim arrMatrix(1 To 9, 1 To 3, 1 To 3)

Store values in array

Read moreVBA-Excel: Arrays – Multi Dimensional Array

VBA-Excel: Arrays – One Dimension, Dynamic Array

Dynamic array means when size of the array is not fixed at the start of the program,  the size changes dynamically.

Use ReDim, For Resizing the size of the array.

If you just you ReDim to changing the size of an array, the already stored values in array will be deleted, if you want to keep the old values of an array while resizing the array, use Presereve keyword along with ReDim

ReDim Preserve arrArrayName(newSize)

Example: Store all the available values in column A, into an Array.

For creating one dimensional static array, follow the below steps

  • Declare an Array
  • Resize the array
  • Store values in array
  • Retrieve values from array.

Read moreVBA-Excel: Arrays – One Dimension, Dynamic Array

VBA-Excel: Reference Libraries in Excel WorkBook.

There are set of built in libraries available in excel, few of them are default to your code and rest are optional to be added. These libraries contain various built in methods, objects and properties for these methods and objects. These libraries act same as namespaces in .Net, packages in Java

Below are the steps for adding reference libraries to y

our Excel workbook

  1. Open Excel workbook and press Alt + F11 to get Visual Basic Editor (VBE)
  2. Go to the Tools menu and select References from the drop down menu.
  3. References- VBAProject” dialog will appear.
Reference Libraries
Reference Libraries

Read moreVBA-Excel: Reference Libraries in Excel WorkBook.

VBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.

To Create Dynamic or Run-time WorkSheets using Macro, follow the steps mentioned below

Steps:

  • Get the Active WorkBook object
  • Add the worksheets at the run time using Add().Name() method

 

Get the Active WorkBook object

        Set mainWorkBook = ActiveWorkbook

Add the worksheets at the run time using Add().Name() method

Read moreVBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.

VBA-Excel: SUDOKU Solver

Download Link : SUDOKU Solver

Steps:

  • Open a new WorkBook
  • Create a SUDOKU template
  • Create a Button
  • Copy Paste the Complete Code
  • Press the button till the SUDOKU is solved.

Note: This Code works only for easy and medium level SUDOKU problems

 

After Creating the SUDOKU template you page should look like this:

NOTE: Make sure you create the template in correct cells else you will have to change the code

Create a SUDOKU template

SUDOKU SOLVER-1
SUDOKU SOLVER-1

Read moreVBA-Excel: SUDOKU Solver

VBA-Excel: Create a WorkBook at Runtime.

Using VBA (Visual Basic for Applications) , you can create Excel workbook at the runtime by using Add() method.

If you won’t provide any name for the workbook it will be created as BookN, where N will increase automatically, starting from 0, as you call WorkBook.Add() function.

 

Function FnCreateWorkBook()

         Dim objWorkBook As Workbook

         Set objWorkBook = Workbooks.Add

         objWorkBook.SaveAs Filename:="NewWorkBook.xls"

End Function

Read moreVBA-Excel: Create a WorkBook at Runtime.

VBA-Excel: Cells Ranges Offset – Active Cell

ActiveCell, as the name clearly indicates that the cell which is presently active in your worksheet, in other words you can if u start typing the value u entered will go to active cell.

Example:

Function FnActiveCell()

         Dim mainWorkBook As Workbook

         Set mainWorkBook = ActiveWorkbook   

         mainWorkBook.Sheets("Sheet1").Activate

     ActiveCell.Value = 5

Read moreVBA-Excel: Cells Ranges Offset – Active Cell

VBA-Excel : 3D-Ranges – FillAcrossSheets Method

 

By using FillAcrossSheets you can copy the Range of one worksheet to other multiple worksheets.

Steps:

  • Create an array and assign multiple worksheets names ( in which the data to be copied), as an array elements.
  • Copy the range of main worksheet and paste it to the other sheets using FillAcrossSheets.

Read moreVBA-Excel : 3D-Ranges – FillAcrossSheets Method

VBA-Excel : 3D-Ranges – Working with Multiple WorkSheets At The Simultaneously

You can update the multiple worksheets at the same time. If you want to do the similar action on the same range on multiple sheets, all you need to do is use Array Function to specify multiple sheets name as array elements

Sub MultipleSheets()

    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

    Range("J8").Select

    Selection.Value = 7

Read moreVBA-Excel : 3D-Ranges – Working with Multiple WorkSheets At The Simultaneously

VBA-Excel: Select and Activate Cells – Activate

Here is something interesting for you, you can use a Activate method to activate a cell within a selection.

Activating a Cell Within a Selection

Sub ActivateCellInSelection()

   Worksheets("Sheet4").Select

   Worksheets("Sheet4").Range("A1:H6").Select

   Worksheets("Sheet4").Range("D3").Activate

End Sub

Read moreVBA-Excel: Select and Activate Cells – Activate

VBA-Excel: Select and Activate Cells – Select

Select:

You can Select a cells or cells for performing many activities like putting some value in cell or doing formatting or copy-paste the data. You can put or enter values in cells with/without selecting the cells. Select is used mostly in the cases of copy-paste operation where you have to tell the compiler specifically that from which cell it has to copy the data and in which cell it needs to be pasted.

Note:

Select works only with actives worksheets, if you use Select on cells before activating the respective sheet, the select method will fail and you will get “Run-time error ‘1004’: Application defined or object defined error”

Read moreVBA-Excel: Select and Activate Cells – Select