Macrostash - Excel, Access, VBA, PHP

How to Use Sumproduct to Divide instead of Multiply

Posted by admin on September 20, 2014 No comments

Sumproduct is powerful Excel formula to multiply 2 or more paired ranges (aka arrays or lists) together and sum the product. This is most commonly used in areas like payroll where you have a list of people, their hours worked, and their pay rate. If you wanted to get the total payroll for the period you have pulled up, you would use sumproduct([Range of Hours], [Range of Pay Rate]).

This formula is even more powerful as it can also handle division. This is why there is no analogous divideproduct formula, because you can do it all with sumproduct!

The trick is to put 1/ in front of the range that will be your divisor (aka denominator). Here is a screenshot with an example:

In essence, for each value in range D3:D6, Excel is transforming it into 1/D3 => 1/70, 1/D4 =>1/130, 1/D5 => 1/105, 1/D6 => 1/100 before performing the sumproduct between all 3 ranges.

There are a lot of situations you might need to use this where you are using rates, adjustment values, discounts, or percentages. In the example above, I basically needed to adjust for the cost of living in different areas using rates to see the differences in cost of living if I were to move to different cities. Basically, I needed to calculate a rate for each spending category to figure out the relative cost of living adjustment for each category. I could have added another column to do that calculation and used that for a sumproduct, but this method results in a much cleaner looking spreadsheet.

Note this works in Google Doc’s version of sumproduct as well! If other office programs don’t support the 1/[array] syntax, you’ll have to make an additional column to do the division and then use that new column in your sum product.

Here’s the demo spreadsheet that you can download and use to play around with:

Trigger VBS Script via PHP

Posted by admin on August 5, 2014 No comments

You probably won’t come into this situation very often where you have a Windows computer/server running PHP. Basically this is a web-service that allows the user to trigger a VBS script bu posting a variable to a web service. The web service then takes that information and runs the VBS script after updating the database with the appropriate information. The VBS script uses information in the database, which is why the first part of this code block is needed in the example.

This is also an example of a PHP script connecting to an Access database over ODBC as it’s data source.

Source code

header('Content-type: application/json');
require('session.php');
/* Get POST Parameters */
if(isset( $_POST['fmreq']) == True)
{
	$FM_REQ = $_POST['fmreq'];
/* connect to Database */
	if (!($con = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" . str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]) . "\data_source.accdb", "", "")))
			echo "<p>Connection to CollectOh failed.</p>\n";
		else
		{
/* Updating database so that when the script runs on the Access database it will have the correct information */
			$sql = "update frm_date_select set FMREQ = '" . $FM_REQ . "' where ID = 1";
			odbc_exec($con, $sql);
 
		}
/* This is the meat of where you set the path to your VBS script */
	$command = 'wscript.exe "' . str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]) . '"\FMREQ.vbs"'; 
 
	/* 
	 * wait for command to return a exit code? 
	 * 
	 * true = waits for the command to complete, before continuing this script 
	 * false = executes command then continues this script without waiting for command to exit 
	 * 
	*/ 
 
	$wait = false; 
 
	// run it 
 
	$obj = new COM ( 'WScript.Shell' ); 
/* Feedback via JSON */
	if ( is_object ( $obj ) ) 
	{ 
		$obj->Run ( 'cmd /C ' . $command, 0, $wait ); 
		echo json_encode(array('Saved at '.date('H:i').''));
	} 
	else 
	{ 
		echo json_encode(array('Failed at '.date('H:i')));
	} 
 
	$obj = null; 
}

Formula to Add Leading Zeros in Excel

Posted by admin on February 18, 2014 No comments

To make a set of numbers of varying length into strings with the same number of characters, try using the TEXT formula. This formula won’t handle strings, so if you need to add leading zeros to an alphanumeric identifier you might have to try modifying the VBA formula pasted at the end.

Here’s an example: =TEXT([Number to add trailing 0’s],”Put a 0 to match the desired length”)

So if you’re trying to make “1” six digits long, you would put six zeros in a row like: “000000”.

If you’re trying to do the same thing on an alphanumeric string, try adding this custom formula into your workbook. After you paste the code into a VBA module, you can call it by typing addZeros([the Alphanumeric string], [desired length of string])

So for example: =ADDZEROS(“AA”,4) will result in 00AA

Here’s the code:

Source code

Public Function addZeros(makeLonger As String, numberDigits) As String
    Dim x
    'Adds 0's to the front of the number until it's the correct length
    'Change "0" to another character if you need a different character than 0
    For x = 1 To (numberDigits - Len(makeLonger))
        makeLonger = "0" & makeLonger
    Next x
    addZeros = makeLonger
End Function

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 1 comment

So say you want to select a couple columns through a loop and you need to be able to iterate through columns numerically; so you would need to select a range numerically.

Here’s the simple way:

Columns(“A:B”).Select

Range(Columns(1), Columns(2)).Select

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)

Application.WorksheetFunction.VLookup(Range("A3"), Range("A3:B9"), 2, False)

[Excel] How to ulock a protected Excel Worksheet, Step by Step

Posted by admin on November 5, 2011 21 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