How To Manipulate Data in Excel Using VBA

August 2017



Microsoft Excel is a powerful tool that can be used for data manipulation. In order to get the most from the software, it is imperative to use VBA. Visual Basic for Applications, or VBA (or VISUAL basic for those who actually read what is being edited and published), gives Excel users the ability to create macros, which are powerful time-saving custom functions for data manipulation and analysis. Macros processes VBA code in order to manage large data sets that would otherwise take time to modify. For example, with VBA you can create a macro to automatically format certain fields that meet your determined criteria.

Below is an example of a VBA script used in Excel:


Sub ConfigureLogic()
Dim qstEntries
Dim dqstEntries
Dim qstCnt, dqstCnt

qstEntries = Range("QualifiedEntry").Count
qst = qstEntries - WorksheetFunction.CountIf(Range("QualifiedEntry"), "")

ReDim QualifiedEntryText(qst)
'MsgBox (qst)

dqstEntries = Range("DisQualifiedEntry").Count
dqst = dqstEntries - WorksheetFunction.CountIf(Range("DisQualifiedEntry"), "")
ReDim DisqualifiedEntryText(dqst)
'MsgBox (dqst)

For qstCnt = 1 To qst
QualifiedEntryText(qstCnt) = ThisWorkbook.Worksheets("Qualifiers").Range("J" & 8 + qstCnt).value
'MsgBox (QualifiedEntryText(qstCnt))
logging ("Configured Qualified Entry entry #" & qstCnt & " as {" & QualifiedEntryText(qstCnt) & "}")
Next

For dqstCnt = 1 To dqst
DisqualifiedEntryText(dqstCnt) = ThisWorkbook.Worksheets("Qualifiers").Range("M" & 8 + dqstCnt).value
'MsgBox (DisqualifiedEntryText(dqstCnt))
logging ("Configured DisQualified Entry entry #" & qstCnt & " as {" & DisqualifiedEntryText(dqstCnt) & "}")
Next

includeEntry = ThisWorkbook.Worksheets("Qualifiers").Range("IncludeSibling").value
'MsgBox (includeEntry)
logging ("Entrys included in search - " & includeEntry)

End Sub

How To Analyze and Manipulate Entries in a Spreadsheet

In order to use VBA for data analysis, you will need to check the settings in Excel for the Developer tool. To check, locate the Excel Ribbon and search for the Developer tab. If it is not displayed, you will need to activate it in the Excel Settings menu. Next, create a new worksheet and name it "Qualifiers." We will use this sheet to check for all of the things that qualify the selections.

Next, set up the qualifiers on the sheet according to the code. It must be entered manually; cut and paste will not work.


ThisWorkbook.Worksheets("Qualifiers").Range("J" & 8 + qstCnt).value

How To Locate the Range and Construct an Array

The range in the function above is cell J9. The range function notes an 8; however, the actual range is 9 because:


For qstCnt = 1 To qst

The above statement starts at 1, not 0. Therefore, the list starts at 9. In this case, note (qstCnt=1).

To construct an array out of entries on the worksheet Qualifiers, place random words in cells J9-J13. Once the rows are completed, we can move forward with finding and manipulating data in Excel.

Private Sub CountSheets()
Dim sheetcount
Dim WS As Worksheet

sheetcount = 0


logging ("*****Starting Scrub*********")
For Each WS In ThisWorkbook.Worksheets
sheetcount = sheetcount + 1

If WS.Name = "Selected" Then
'need to log the date and time into sheet named "Logging"
ActionCnt = ActionCnt + 1
logging ("Calling sheet: " & WS.Name)
scrubsheet (sheetcount)
Else
ActionCnt = ActionCnt + 1
logging ("Skipped over sheet: " & WS.Name)
End If


Next WS
'MsgBox ("ending")
ActionCnt = ActionCnt + 1

logging ("****Scrub DONE!")
Application.ScreenUpdating = True

End Sub

There is an example of a working tab counter.


Dim sheetcount
Dim WS As Worksheet

sheetcount = 0


logging ("*****Starting Scrub*********")
For Each WS In ThisWorkbook.Worksheets
sheetcount = sheetcount + 1

After initialising the sheet count, set it to 0 in order to restart the counter.

Logging() is another subroutine that keeps track of all actions in order to audit selections.

The next For loop sets up the Active Workbook for counting. WS is the initialised and ThisWorkbook.Worksheets is the active tab in the book. Since we have not named the workbook, this module will run on any active workbook. If you are working on multiple workbooks and have the wrong one activated, it will attempt to run on it. To avoid errors, take precautions to name your specific workbook or only work on one at a time.

Every time the loop fires, it adds one variable to the sheet count to keep track of the number of tabs. Then we move to:

If WS.Name = "Selected" Then
'need to log the date and time into sheet named "Logging"
ActionCnt = ActionCnt + 1
logging ("Calling sheet: " & WS.Name)
scrubsheet (sheetcount)
Else
ActionCnt = ActionCnt + 1
logging ("Skipped over sheet: " & WS.Name)
End If

Here, we look for the Selected tab.

If the variable WS is equal to Selected, then we log it and fire the subroutine Scrub Sheet. If the variable WS is not equal to Selected, it is logged that sheet was skipped and the action is counted. The above code is an example of how to count the number of and locate a particular tab.


Well to wrap this little thing up, as I have been asked, "Mark, what is the point?"

I will post the code to the most asked questions concerning HOW TO do this or THAT with EXCEL. Just remember, kids, if you are trying to get help here at CCM.NET (ME specifically), post some code and we will help! Ok, I will quit stalling, and just post the code. Once again, if you cannot reverse engineer these examples, then you need to start with "HELLO WORLD"!

++Counting Tabs in a worksheet
 Dim TAB
For Each TAB In ThisWorkbook.Worksheets
'some routine here
next


++Finding last row on a worksheet named "worksheet"
Dim cellcount
cellcount = Cells(ThisWorkbook.Worksheets("worksheet").Rows.Count, 1).End(xlUp).Row



++Applying a filter or sort based on range A5
 Range("A2:Z99").Sort key1:=Range("A5"), order1:=xlAscending, Header:=xlNo


++Applying Autofit to a particular column
Columns("A:A").EntireColumn.AutoFit


++Getting a Value from Cell F1 of a sheet named "worksheet"
 dim newvalue
newvalue = ThisWorkbook.Worksheets("worksheet").Range("F1").value


++Inserting a column to A1 (1,1)
Dim Row, Column
Cells(Row, Column).EntireColumn.Select
Selection.Insert


++Inserting N columns into a worksheet named "worksheet"
 Dim insertCnt
Dim Row, Column
For insertCnt = 1 To N
ThisWorkbook.Worksheets("worksheet").Select
Cells(Row, Column).EntireColumn.Select
Selection.Insert
Next


++Adding a Named Range ("Status") to a particular sheet ("worksheet"), cell reference (C2)
 ThisWorkbook.Worksheets("worksheet").Names.Add Name:="Status", RefersToR1C1:="=worksheet!C2"

++Inserting Entire row
Dim Row, Column
Cells(Row, Column).EntireRow.Select
Selection.Insert


++Copying entire row A1 for Pasting
ActiveSheet.Range("A1").EntireRow.Select
Selection.Copy

++Delete an entire row A1
ActiveSheet.Range("A1").EntireRow.Select
Selection.Delete


++Selecting a particular Sheet, named worksheet
 ThisWorkbook.Worksheets("worksheet").Select


++Comparing values of a range
                                Dim firstrange
Dim Logictest
Logictest = "some word or value"
If (Range(firstrange).value = Logictest) then
'some routine here
End If


I hope you all have found what you are looking for! Have FUN!


Image: © Microsoft

Related


Published by ac3mark. Latest update on June 2, 2017 at 05:51 AM by Daniel_CCM.
This document, titled "How To Manipulate Data in Excel Using VBA," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).