Tutorials For Excel everyday with EVBA.info part 36[Free PDF ebook] - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Saturday, November 30, 2019

Tutorials For Excel everyday with EVBA.info part 36[Free PDF ebook]

Tutorials For Excel everyday with EVBA.info part 36[Free PDF ebook]



EVBA’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

At some point you have probably inherited or created a worksheet that contains active hyperlinks, when all you want in the cells is the hyperlink text itself. It’s annoying to unwittingly click onto a hyperlink cell that interrupts your work by suddenly taking you away from your worksheet.




You could right-click on each hyperlink cell and select “Remove Hyperlink”. But if you have dozens or hundreds of hyperlinks on a worksheet as depicted in the above picture, you’ll need a faster and easier method, for example by following these 3 simple steps:
Step 1
From your worksheet press Alt+F11, which takes you to the Visual Basic Editor. From there, press Ctrl+G to show the Immediate Window where your mouse cursor will be flashing.
Step 2
As you see in the picture of the Immediate Window in the VBE, type the command
ActiveSheet.Hyperlinks.Delete
and press the Enter key.



Step 3
That’s it!
Exit the VBE by pressing Alt+Q to return to your worksheet
.

Tom’s Tutorials For Excel: Spreadsheet Passwords — The Facts About Protection

Tom’s Tutorials For Excel: Spreadsheet Passwords — The Facts About Protection
Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.
It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection, or has questions about just how secure a password-protected spreadsheet really is. When people know the facts without scare tactics or hyperbole, they can make the best decisions for themselves when armed with objective, unbiased information.
As protection platforms go, Microsoft’s products have inherent weaknesses. In its defense, Microsoft has never claimed to have reliable spreadsheet protection. In Office applications, a password is like the lock on your home’s front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.
Try this: open a new workbook, go to Sheet1 and protect it with the password “test” (without the quotes, lower case just as you see it here). Now unprotect Sheet1 but instead of using the password “test”, use the password “zzyw”.
Take comfort that Microsoft is like any other company, in that virtually any application is hackable. Here’s some background on Excel spreadsheet password protection:
When someone password protects a sheet in Excel, they generate a 16-bit 2-byte hash, a technical term for a number generated from a string of text by a function called the MD5 Message Digest Algorithm. An MD5 hash has fewer numeric characters than the actual password text, making it unlikely but not impossible to be replicated. Note that “replicated” is not the same as “duplicated”.
When unprotecting a protected sheet, the password value is compared to the MD5 hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Since it is a case-sensitive scheme, there are over 90 acceptable characters, which translate into the multiple trillions of password text possibilities. Since the combination of possible passwords is much greater than the combination of possible MD5 hashes, some passwords can share the same MD5 hash value.
The MD5 hash is a standard mixing algorithm, executed as follows:
• Take the ASCII values of all characters.
• Shift left the first character 1 bit.
• Shift left the second 2 bits.
• Continue for quantity of characters up to 15 bits, with the higher bits rotated.
• XOR those values.
• XOR the count of characters.
• XOR the constant 0xCE4B.
As you may know, XOR is a logical term associated with a mathematical compound statement, an acronym for “exclusive or”. In this case, statement “A” is the password value you type in. Statement “B” is the generated MD5 hash. The XOR operation returns TRUE when only one of its combinations is TRUE. This translates to more than one password value possible in the context of a truth table:
A B XOR Result
T T FALSE
T F TRUE
F T TRUE
F F FALSE
By the way, if you wanted to reproduce the actual password, and not just a compatible one, it’s a virtual certainty that it literally could not be accomplished during your lifetime.
There are 94 standard characters (26 of A-Z; 26 of a-z; 10 of 0-9; and 32 special such as #,%,!, and so on). That means, for every character there are 94 possibilities.
To extrapolate using the example of an eight-character password, the number of characters to test is
94 x 94 x 94 x 94 x 94 x 94 x 94 x 94
which equals
6,090,000,000,000,000
At the hefty pace of 100,000 password attempts per second, it would take 1,932 years to recover the exact password. And that’s just with 8 characters; with the 255 max it can take millions of years.
What all this boils down to is, if you don’t want to expose your Excel spreadsheets to *any* possible password circumvention, don’t share them. However, the likelihood of someone guessing a compatible hash is very slim, though there are commercially-sold password cracking programs.
One thing is sure, you are in good company: the whole world is in the same boat with this Excel protection issue. As you understand the spreadsheet password protection scheme, you can make your own informed decisions about what and what not to risk putting in your workbook, and how or with whom you share access to your workbooks.

Tom’s Tutorials for Excel: TRIM and CLEAN your data

Tom’s Tutorial For Excel: TRIM and CLEAN your data

Sometimes you will receive output files from data warehouse applications, or you will copy data from a web page onto your Excel worksheet. Many cells can look jumbled with the data they hold, because of extra (and usually invisible) spacebar or ASCII characters that were not meant to be a part of the actual data.



Two functions named TRIM and CLEAN work well together to return a list of such data into a more Excel-friendly format. You can see the formula bar in the picture that with the original example data in cell A1, the formula in cell B1 is
=TRIM(CLEAN(A1))
The TRIM function corrects irregular spacing by removing all spaces from text except for single spaces between words.
The nested CLEAN function in the formula removes all non-printable characters from the text.
These two functions do not correct everything; there are some characters you’ll encounter that require other tactics to eradicate. But when it comes to excess spacebar characters (ASCII #32), TRIM can usually do the job, and CLEAN handles the eradication of ASCII characters 0 to 31.
To see a list of ASCII characters, in a fresh worksheet, select cell A1, enter the formula
=CHAR(ROW())
and copy it down to cell A255.
The characters you see in cells A1:A32 will be rid by TRIM and CLEAN. At this point in typical practice, you’d copy column B and paste special for values to yield a more user-friendly list. Finally, delete column A and you are good to go.

Tom’s Tutorials for Excel: Transpose a Table Range

Tom’s Tutorials For Excel: Transpose a Table Range
In three fast, easy steps you can transpose a multi-row and multi-column table to gain a fresh analytical look at your data. The result is similar to an effect with a pivot table, but with the advantage that you can see both views of the data at the same time, side by side.
Be aware that with this manual method the two tables will not be linked. A change to data in one table will not appear in the other table. There are formula and VBA methods to establish such links, but for this example the objective is to transpose a quick snapshot of an entire table range.
Here are the three steps and a view of the final result:







Tom’s Tutorials for Excel: Selecting a Current Region

Tom’s Tutorials for Excel: Selecting a Current Region

There are times when you will only want to select a particular range of cells on your worksheet, such as a table to be sorted, or from which you will create a pivot table or chart.
Plan ahead by separating that table’s range from the rest of the worksheet’s other data. That way you can quickly select and work with the table’s Current Region, a range of contiguous cells bordered by an empty row and empty column.



In the above picture, the Current Region of A1:C10 shows a shoe store’s inventory of size 10 shoes. The range has been selected, which you can do quickly using either of two methods.
First, select any cell within the current region range. In the picture, I selected cell A1. Then…
Method 1:
Press the F5 key, click the Special button, select Current Region and click OK as indicated in the following picture.



Method 2
From your keyboard, press the Ctrl+Shift+*8 keys as shown in the next picture, where the “*8” is that key along the top of keyboard.

Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group

Tom’s Tutorials for Excel: Dynamic Conditional Formatting By Group

To improve the readability of your lists, you can conditionally format alternating groups of primary items. The challenge is to make this happen when the count of each client name varies in the list, which can change at any time.



In the above picture, Widgets, Inc maintains its list of clients by their name and purchasing activity. To quickly identify each client group, Conditional Formatting has been applied with an alternating scheme of color/no color at each change of client name in column A.
Here’s how it’s done, using the next picture as a guide. Notice the two pictures are identical except that column G has now been unhidden.



Step 1
In cell G3 (same row as the list header) a zero is entered.
Step 2
In cell G4 enter the helper formula
=IF(A4=A3,G3,G3+1)
and copy down to cell G22, (same row where the list ends).
Step 3
Select the list’s data range, in this example A4:C22.
Step 4
Show the Conditional Formatting dialog by pressing Alt+O+D.
Step 5
In the Conditional Formatting dialog, enter a new rule formula — that is, use a formula to determine which cells to format — with this formula:
=AND(LEN($A4)>0,MOD($G4,2)=0)
Note the absolute $ references in the formula.
Step 6
Click the Format button.
Step 7
Click the Patterns tab (in versions 2003 or before) or the Fill tab (in versions 2007 or after) and select a color from the palette.
Step 8
Click OK to accept the selected color, and click OK to exit the Conditional Formatting dialog.
Step 9
Optional and recommended, re-hide the column (which is column G in this example) where you have the helper formula.

Tom’s Tutorials for Excel: Count and Sum Between Dates

Tom’s Tutorials for Excel: Count and Sum Between Dates



In the picture, a table contains several columns of data, including a column of dates. Three examples show how you can perform various mathematical operations on multiple-column criteria between dates using the SUMPRODUCT function.
These tasks are also achievable with array formulas but many users prefer not needing to remember to press Ctrl+Shift+Enter, which is why SUMPRODUCT is used for these examples.
Note that although the pictured table shows dates listed in ascending order, these formula examples will work the same way regardless of what order (or disorder) the dates are listed in your table.
In the yellow section for Example 1, the task is to count how many dates during June 2011 show Widgets in Item column B.
Based on the table, the answer is 2.
The solution formula in cell G6 is
=SUMPRODUCT((A2:A17>=DATE(2011,6,1))*(A2:A17<=DATE(2011,6,30))*(B2:B17=F6))
In the green section for Example 2, the task is to sum the total of Wallabies that were sold during July 2011. The formula must find Wallabies as an Item listed in column B between July 1, 2011 and July 31, 2011 and sum for each found Item's Unit Count in column C.
Based on the table, the answer is 23.
The solution formula in cell G11 is
=SUMPRODUCT((A2:A17>=DATE(2011,7,1))*(A2:A17<=DATE(2011,7,31))*((B2:B17=F11)*(C2:C17)))
In the pink section for Example 3, the task is to calculate gross sales of Wombats in August 2011. Notice that a field for total gross sales does not need to exist in the source table for you to be able to calculate that number, based on information in the other existing fields. The SUMPRODUCT function finds Wombats in the Item column B during August, multiplies each by its Unit Count in column C, each multiplied product by its Unit Price in column D, and sums each of those multiplied products for the final answer.
Based on the table, the answer is 249.
The solution formula in cell G17 is
=SUMPRODUCT((A2:A17>=DATE(2011,8,1))*(A2:A17<=DATE(2011,8,31))*((B2:B17=F17)*(C2:C17)*(D2:D17)))

Tom’s Tutorials for Excel: Reverse Lookup

Tom’s Tutorials for Excel: Reverse Lookup
As you probably know, the VLOOKUP function searches the first column of a range of cells and returns a value from a cell in the same row. But what if your criteria item — that is, what you are trying to look up — is not listed in the first column of the range? What if you need to return a value from a column to the left of where your criteria is listed in the table?


In the picture, a department store keeps a daily table of transactions, with typical column fields relating to their sales. To the right of the table are colored cells with examples for doing a reverse lookup using the INDEX and MATCH functions.
In cell H2, the criteria for Order ID is manually entered to return its associated Product ID in the green cell I2. Notice in the table that Order ID is located in column F and Product ID is located in column B, so an alternative to VLOOKUP is needed.
In this example, the formula in cell I2 is
=INDEX(A1:F100,MATCH(H2,F1:F100,0),2)
You can interpret this formula as:
=INDEX(table-range, MATCH(criteria, criteria_column, match_exact), return_column_2)
Note that column 2 is column B, Product ID.
The second example in yellow cell I7 demonstrates the same principle. The formula there to find the Sales associated with the criteria for Manager is
=INDEX(A1:F100,MATCH(H7,E1:E100,0),4)
In this example, it is the same table range of A1:F100, “Tony” as the Manager criteria is located in column E, and the Sale associated with Tony would be in column D which is column #4 in the table.
Using INDEX and MATCH is a powerful combination for lookup routines, and it can be used for looking up values to the right of the criteria column just as VLOOKUP does. Personally, I almost never use VLOOKUP because I find INDEX and MATCH to be more versatile, not requiring the table to be sorted in any way, nor having its column fields arranged in a certain order. Every Excel user has their own preferences. If you are a dedicated VLOOKUP user, perhaps consider the INDEX and MATCH approach, which you may find to be a valuable alternative.

Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients

Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients

With a macro from Excel, you can create an email in Outlook that will attach your active workbook and fill in the To field with a list of multiple recipients. Suppose you have a workbook that you email to the same people on a regular basis. On a hidden worksheet, you can list all the recipients’ email addresses, such as I have done in column A of Sheet3.



This macro will create an email in Outlook, attach your workbook, and write your list of recipients in the To field as seen in the following picture.



Sub EmailAttachmentRecipients()
'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
'Declare a String variable for the recipient list, and an Integer variable
'for the count of cells in column A that contain email addresses.

Dim strTo As String
Dim i As Integer
strTo = ""
i = 1
'Loop through the recipient email addresses to build a continuous string
that separates recipient addresses by a semicolon and a space.

With Worksheets("Sheet3") 'change sheet name where list is kept.
Do
strTo = strTo & .Cells(i, 1).Value & "; "
i = i + 1
Loop Until IsEmpty(.Cells(i, 1))
End With
'Remove the last two characters from the recipient string, which are
'an unnedded semicolon and space.

strTo = Mid(strTo, 1, Len(strTo) - 2)
'Display the email message with the attached active workbook.
With objMailItem
.to = strTo
.CC = "CEO@yourcompany.com; President@yourcompany.com"
.Subject = "Example attachment to multiple recipients"
.Body = _
"Hello everyone," & Chr(10) & Chr(10) & _
"Here's an example for attaching the active workbook" & Chr(10) & _
"to an email with multiple recipients."
.Attachments.Add ActiveWorkbook.FullName
.Display
 'Change to Send if you want to just send it.
End With
'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing
End Sub

Tom’s Tutorials for Excel: Unique List From Multi-Column Table

Tom’s Tutorials For Excel: Unique List From Multi-Column Table
You may know that from a single-column list containing repeated items, you can extract a list of unique items using Advanced Filter. But what if you want to extract a unique list from a table having many columns of repeatedly listed items?
UniqueList
In the picture, a fictional quarterly survey keeps a list of top 10 vacation destinations. Many of those destinations are repeated among the four quarterly columns. The following macro lists all vacation destinations that are in the table, making sure that each is named only once in column G.
Sub UniqueList()
'Turn off screen updating
Application.ScreenUpdating = False
'Declare and define variables
Dim cell As Range, TableRange As Range
Dim xRow As Long, varCell As Variant
Set TableRange = Range("B4:E13")
xRow = 2
'Clear column G (column #7) where the list will go.
Columns(7).Clear
'Enter the header label in cell G1 and bold cell G1.
With Range("G1")
.Value = "Unique list:"
.Font.Bold = True
End With
'Loop through each cell in the table range,
'and add that cell's value to the list if it
'does not exist in the list yet.

For Each cell In TableRange
varCell = Application.Match(cell.Value, Columns(7), 0)
If IsError(varCell) Then
Err.Clear
Cells(xRow, 7).Value = cell.Value
xRow = xRow + 1
End If
Next cell
'Clear the TableRange object variable from system memory.
Set TableRange = Nothing
'Sort the list in alphabetical order.
Range("G1").CurrentRegion.Sort Key1:=Range("G2"), _
Order1:=xlAscending, Header:=xlYes

'Autofit column G.
Columns(7).AutoFit
'Turn screen updating back on.
Application.ScreenUpdating = True
End Sub
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment