Manipulating Entries in Excel via VBA

April 2017


How To Manipulate Data in Excel Using VBA

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. Visal Basic for Applications, or VBA, 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

Analyze and Manipulate Entries in a Spreadsheet

To get started, search for the Developer menu on the Excel ribbon. If it is not displayed on the ribbon, you will need to activate it in the Excel Settings menu. Next, make a work sheet and name it "Qualifiers." This is the sheet that we will check for all of the things that qualify our selections.

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


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

Locate the Range and Construct an Array

The range in this function 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, so the list starts at 9. In this case note, (qstCnt=1).

To construct an array out of entries on the worksheet Qualifiers, place some random words in cells J9-J13.


Moving Forward!


If you have entered those values in, then we can move forward with the mechanics and methods of finding and manipulating data within 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! We can take some time to talk about what is going on with the above code snippet, assuming you haven't just cut and pasted the code and are trying it in your own project (literally as we speak).


Dim sheetcount
Dim WS As Worksheet

sheetcount = 0


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


I am not going to bore you with the DIM statements, but if you don't know what they do, then I encourage you to look into programming, and not just cutting and pasting!

So, after we initialize the sheetcount, we set it to 0, so we can start the count. We haven't counted any sheets yet, so we start with "0". There will be times when your loops will start with other numbers, like 1, or 1256. Once again, I encourage you to read more, cut and paste less!

You are probably wondering, "What is this logging ("*****Starting Scrub*********"), entry?"

Logging() is another subroutine that keeps track of all actions, for auditing the selections. We will get into that when it is time, stay with me.

The next FOR loop, sets up the ACTIVE WORKBOOK worksheet for counting. WS is the variable as we initialized, and ThisWorkbook.Worksheets are the TABS in the book that is active. Please understand, because we do not NAME the workbook we are working in, this module can be, and 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. The end result could be bad, so make certain you take precautions to name your specific workbook, or only work on one workbook at a time! That is your warning about that!

Then for every time the loop fires, it adds one to the variable of sheetcount, keeping 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 tab named "Selected". Go ahead, I will pause while you cut and paste the above code!

Here, we look to see if the variable WS is equal to "SELECTED", and if it is, then we log it, and fire the subroutine SCRUBSHEET. If it doesn't find selected, it is logged that sheet was skipped, and the action is counted. We then go back to the top and look again.

So, the above code is an example of how to count the number of TABS, and how to find a particular TAB. TABS are worksheets, just to be clear!

There will be more later, kids. Write me if you wish to have anything clarified!

Have FUN!

Related


Published by ac3mark. Latest update on April 24, 2017 at 02:47 AM by Daniel_CCM.
This document, titled "Manipulating Entries in Excel via VBA," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).