Control + Alt + Delete in Remote Desktop
Posted by admin on February 25, 2013
No comments
So when you’re remoted in another computer with Windows Remote Desktop Application pressing Control + Alt + Delete to bring up the task manager, to change your password, or to log off, you’ll pull it up on the main computer instead of the remote computer. To get around this press : Control + Alt + End, sometimes Control + Alt + Home will work too. Tl;Dr: On a remote desktop Ctrl + Alt + Del = Ctrl + Alt+ End or Ctrl + Alt + End Excel – VBA to Select Columns in VBA with Numbers instead of Letters
Posted by admin on November 5, 2011
No comments
Excel – Close and Save active workbook in VBA with no Prompt
Posted by admin on November 5, 2011
No comments
This VBA code will save an Excel workbook file and close it when it runs. You could put a button or some other trigger on your worksheets to initiate the macro so that it’s easier for your users to access the macro. Instructions to Install VBA to Save Workbook with No Prompt Step 1: Copy paste this Save and Close VBA macro in to a new module Sub SaveAndClose()
ActiveWorkbook.Close SaveChanges:=True
End Sub
Step 2: Make whatever changes you want to the worksheets / workbook. Step 3: Run the macro and it will save the changes that you made and close the work book. [Excel] Vlookup in VBA
Posted by admin on November 5, 2011
No comments
Using a Vlookup in VBA for your macros isn’t as straight forward as you would think. The trick is that Vlookup is a worksheet function that you need to reference using the Application variable in VBA, when you code your macro. Note: If you’re looking for something similar for Access VBA, it’s called Dlookup. There are a couple ways of calling up a vlookup in a macro, both should work. Application.VLookup(Range("A3"), Range("A3:B9"), 2, False)
Or Application.WorksheetFunction.VLookup(Range("A3"), Range("A3:B9"), 2, False)
[Excel] How to find a lost password to a Worksheet, Step by Step
Posted by admin on November 5, 2011
No comments
If you’re like me, you might have a tendency to forget passwords. I always have good intentions on trying to keep workbooks secure and the formatting and data stable, and sometimes those efforts fall flat when I forget the password. Or you might have gotten the worksheets from someone else and they’re might have forgotten the passwords, just like I do. So if you need to unlock the password protection on a worksheet here’s some quick and easy instructions on using a macro to help you unprotect the sheet. Please don’t use these steps for unlawful purposes. Also these steps will only work for password protected sheets, which are the ones you can’t edit but you can still see the data on them. For advanced users you can skip straight to the code on step 3. Step 1: Press ALT + F11 on your keyboard to open up the Visual Basic Editor Step 2: Click on [Insert] then [Module]
Step 3: Copy & Paste this code into the module window that pops up: Sub PasswordBreaker()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
Just like this:
Step 4: Go back to the password protected sheet and press ALT + F8 Step 5: Select the PasswordBreaker macro and click run. It takes a little while to run, but it should do the trick!
Have two Excel Workbooks Open at the Same Time
Posted by admin on October 9, 2011
No comments
I know that this is one of my pet peeves. If you open multiple workbooks in Excel, they all get stuck together in the same window, and it’s hard to do split screens or multiple monitors. Thankfully there’s an easy way to get two (or more) independent windows of Microsoft Excel to open. All you have to do is open Excel from your start menu each time you want an independent instance of Excel to run, and then open the worksheet in your new program window. Volia! Two ribbons, two spreadsheets, and you can drag them around to two different monitors if you please. They can finally be two different sizes, and whatever else you need them to do. One minor downside though, is that referencing cells between the 2 instances of Excel doesn’t work. [Cheatsheet] VBA String Functions
Posted by admin on October 9, 2011
No comments
Simple Regular Expression Tutorial for Excel VBA
Posted by admin on October 8, 2011
No comments
I’ll go through a simple tutorial for using regular expressions for VBA. This is a great quick start guide for people who have used regular expressions before in other languages. Just as a review, regular expressions are used to match patterns in strings. This is a powerful tool that can help you data cleanse and data mine. The directions are the same for Microsoft Access as well. First thing first, let’s enable the reference in Excel so that we can use this functionality. Go to Tools > References
Then find Microsoft VBScript Regular Expressions 5.5
Click Ok, and now you’ll be able to create a RegExp object to use in your VBA. Here’s a basic function that finds 5 letter words in a string. Here’s a copy of the Excel file that I am using in the example: RegEx Excel Tutorial XLS File Function findFiveLetter(sentance)
Dim regEx As New VBScript_RegExp_55.RegExp
Dim matches, s
regEx.Pattern = "\W\w{5}\W"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occurance
s = ""
If regEx.Test(sentance) Then
Set matches = regEx.Execute(sentance)
For Each Match In matches
s = s & " Position: " & Match.FirstIndex
s = s & " Word: " & Match.Value & " "
s = s & Chr(10)
Next
findFiveLetter = s
Else
findFiveLetter = ""
End If
End FunctionNow let’s break it down. First you need to declare your regEx obejct with: Dim regEx As New VBScript_RegExp_55.RegExpNext you give your object some parameters. The most important is .Pattern, which is the pattern of characters that you want your function to find. Then there are a couple of settings like Global, if you want it to search for all occurrences (TRUE) or just the first occurrence (FALSE), and also IgnoreCase. Here’s a simple guide to get you started on writing regex patterns. regEx.Pattern = "\b\w{5}\b"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occuranceNow to test if the pattern has been found in a string you can use regEx.Test(sentance)To get an array of matches from your regex you simply have the regex execute. Set matches = regEx.Execute(sentance)To get the results from this array object, you can reference matches(n) or use a For Each loop. There are two important pieces of information returned in the array, the index of the match and the actual match. I used a For Each: For Each Match In matches
s = s & " Position: " & Match.FirstIndex 'Position of the match
s = s & " Word: " & Match.Value 'The actual match
s = s & Chr(10) 'Prints a new line
NextHere’s the example output:
Here’s a copy of the Excel file that I used in the example: RegEx Excel Tutorial XLS File Basic Regular Expression Patterns for Beginners
Posted by admin on October 8, 2011
1 comment
This is super simplified, but it’s enough to get started with. I remember being confused by the gigantic tables with all the special clauses, so when you’re beginning keep it simple. Remember if you want to match a literal character you can just type it out, unless there’s a special character. Special characters have meanings in regex patterns, so you need to type a \ in front of them if you want to specifically match them in the pattern. Common special characters are: + . \ – [] {} $ ? |
[Demo] Use a PHP & MySQL database to load markers on a Google Map
Posted by admin on September 17, 2011
No comments
Setting up a Google Maps to use PHP and a MySQL back-end isn’t the most straight forward thing to do, especially in comparison to using Google Map’s JavaScript API V3. I wanted to load a map from a MySQL database with lat, longs, the name of the marker, and a description to put in an information window (it’s the little text bubble that pops up when you click on a marker). I didn’t want to learn how to use the Maps API through PHP, so what did I do?
Here’s a link to the finished version demo, it should work in Chrome, Firefox and IE: http://www.macrostash.com/wp-content/plugins/big-ten-demo.php There’s a lot going on behind the scenes, so this is a pretty long demo. Features this demo shows:
The MySQL Database I’m using the locations of the 12 colleges/universities in the Big Ten Conference. The XLS of the data that I used is available: Big Ten Schools Demo Data Basically, what I’ve got there is the latitude, longitude, name of the location, address of the location, and a random fact. In this case the random fact is which division the school is in. Legends or Leaders. Here’s the first school, so that you get an idea of what the data looks like.
The PHP //Connect to the MySQL database that is holding your data, replace the x's with your data
mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
die("Could not connect: " . mysql_error());
mysql_select_db("xxxxx_xxxx");
//Initialize your first couple variables
$encodedString = ""; //This is the string that will hold all your location data
$x = 0; //This is a trigger to keep the string tidy
//Now we do a simple query to the database
$result = mysql_query("SELECT * FROM `big-ten`");
//Multiple rows are returned
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
//This is to keep an empty first or last line from forming, when the string is split
if ( $x == 0 )
{
$separator = "";
}
else
{
//Each row in the database is separated in the string by four *'s
$separator = "****";
}
//Saving to the String, each variable is separated by three &'s
$encodedString = $encodedString.$separator.
"<p class='content'><b>Lat:</b> ".$row[1].
"<br><b>Long:</b> ".$row[2].
"<br><b>Name: </b>".$row[3].
"<br><b>Address: </b>".$row[4].
"<br><b>Division: </b>".$row[5].
"</p>&&&".$row[1]."&&&".$row[2];
$x = $x + 1;
}The HTML (Body) We have 2 parts to the body. The input part has the encoded string, and it’s hidden. The map_canvas is where the Google map will be loaded into.
The JavaScript //This javascript will load when the page loads.
jQuery(document).ready( function($){
//Initialize the Google Maps
var geocoder;
var map;
var markersArray = [];
var infos = [];
geocoder = new google.maps.Geocoder();
var myOptions = {
zoom: 9,
mapTypeId: google.maps.MapTypeId.ROADMAP
}
//Load the Map into the map_canvas div
var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
//Initialize a variable that the auto-size the map to whatever you are plotting
var bounds = new google.maps.LatLngBounds();
//Initialize the encoded string
var encodedString;
//Initialize the array that will hold the contents of the split string
var stringArray = [];
//Get the value of the encoded string from the hidden input
encodedString = document.getElementById("encodedString").value;
//Split the encoded string into an array the separates each location
stringArray = encodedString.split("****");
var x;
for (x = 0; x < stringArray.length; x = x + 1)
{
var addressDetails = [];
var marker;
//Separate each field
addressDetails = stringArray[x].split("&&&");
//Load the lat, long data
var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
//Create a new marker and info window
marker = new google.maps.Marker({
map: map,
position: lat,
//Content is what will show up in the info window
content: addressDetails[0]
});
//Pushing the markers into an array so that it's easier to manage them
markersArray.push(marker);
google.maps.event.addListener( marker, 'click', function () {
closeInfos();
var info = new google.maps.InfoWindow({content: this.content});
//On click the map will load the info window
info.open(map,this);
infos[0]=info;
});
//Extends the boundaries of the map to include this new location
bounds.extend(lat);
}
//Takes all the lat, longs in the bounds variable and autosizes the map
map.fitBounds(bounds);
//Manages the info windows
function closeInfos(){
if(infos.length > 0){
infos[0].set("marker",null);
infos[0].close();
infos.length = 0;
}
}
});
All Together With the HTML *The comments are getting highlighted a little bit weird (I set it to HTML syntax), but the code should copy paste properly into a new file. <html>
<head>
<script type='text/javascript' src='jquery-1.6.2.min.js'></script>
<script type='text/javascript' src='jquery-ui-1.8.14.custom.min.js'></script>
<style>
BODY {font-family : Verdana,Arial,Helvetica,sans-serif; color: #000000; font-size : 13px ; }
#map_canvas { width:100%; height: 100%; z-index: 0; }
</style>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false" /></script>
<script type='text/javascript'>
//This javascript will load when the page loads.
jQuery(document).ready( function($){
//Initialize the Google Maps
var geocoder;
var map;
var markersArray = [];
var infos = [];
geocoder = new google.maps.Geocoder();
var myOptions = {
zoom: 9,
mapTypeId: google.maps.MapTypeId.ROADMAP
}
//Load the Map into the map_canvas div
var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
//Initialize a variable that the auto-size the map to whatever you are plotting
var bounds = new google.maps.LatLngBounds();
//Initialize the encoded string
var encodedString;
//Initialize the array that will hold the contents of the split string
var stringArray = [];
//Get the value of the encoded string from the hidden input
encodedString = document.getElementById("encodedString").value;
//Split the encoded string into an array the separates each location
stringArray = encodedString.split("****");
var x;
for (x = 0; x < stringArray.length; x = x + 1)
{
var addressDetails = [];
var marker;
//Separate each field
addressDetails = stringArray[x].split("&&&");
//Load the lat, long data
var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
//Create a new marker and info window
marker = new google.maps.Marker({
map: map,
position: lat,
//Content is what will show up in the info window
content: addressDetails[0]
});
//Pushing the markers into an array so that it's easier to manage them
markersArray.push(marker);
google.maps.event.addListener( marker, 'click', function () {
closeInfos();
var info = new google.maps.InfoWindow({content: this.content});
//On click the map will load the info window
info.open(map,this);
infos[0]=info;
});
//Extends the boundaries of the map to include this new location
bounds.extend(lat);
}
//Takes all the lat, longs in the bounds variable and autosizes the map
map.fitBounds(bounds);
//Manages the info windows
function closeInfos(){
if(infos.length > 0){
infos[0].set("marker",null);
infos[0].close();
infos.length = 0;
}
}
});
</script>
</head>
<body>
<div id='input'>
<?php
//Connect to the MySQL database that is holding your data, replace the x's with your data
mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
die("Could not connect: " . mysql_error());
mysql_select_db("xxxxx_xxxx");
//Initialize your first couple variables
$encodedString = ""; //This is the string that will hold all your location data
$x = 0; //This is a trigger to keep the string tidy
//Now we do a simple query to the database
$result = mysql_query("SELECT * FROM `big-ten`");
//Multiple rows are returned
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
//This is to keep an empty first or last line from forming, when the string is split
if ( $x == 0 )
{
$separator = "";
}
else
{
//Each row in the database is separated in the string by four *'s
$separator = "****";
}
//Saving to the String, each variable is separated by three &'s
$encodedString = $encodedString.$separator.
"<p class='content'><b>Lat:</b> ".$row[1].
"<br><b>Long:</b> ".$row[2].
"<br><b>Name: </b>".$row[3].
"<br><b>Address: </b>".$row[4].
"<br><b>Division: </b>".$row[5].
"</p>&&&".$row[1]."&&&".$row[2];
$x = $x + 1;
}
?>
<input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />
</div>
<div id="map_canvas"></div>
</body>
</html>
|
