LG G3 Bluetooth Turning Off and Won’t Stay On

TLDR; try pulling the battery out and putting it back in.
EDIT TLDR; My phone started to do the screen flickering thing. I followed the directions to bake the motherboard which worked for a little bit. I ended up buying a new phone.

Recently, the Bluetooth radio on my LG G3 started to turn off instantly. I would push the Bluetooth button on the pull down menu, and the little icon on the top of the screen would flicker on and then flicker off a couple seconds later. I Google searched for some help and there were posts about installing weird apps and custom ROMs, hard restarting, and reformatting.

It turns out none of those things were necessary. Before you go off and try all those things, try turning off your phone, pulling out the battery and then putting it back in. My Bluetooth is as good as new again.

Surviving Reddit Front Page on A Digital Ocean Droplet

I hope this post helps someone else out. On Monday, I had page on another website go viral via a Reddit post, and it briefly hit the front page (whooo #19 on r/all at around noon).  It sounds great right?  Well, I noticed this as I was sitting in an airport ready to fly back home after Christmas. My website is hosted on a Digital Ocean droplet, and I had kept it at the 2GB plan which was generous as it usually only has about 700-2000 users a day. At 9AM all was well, and my thinking was that I could resize to a larger droplet if I needed to.

At 10am, I noticed that my position on Reddit kept climbing and the number of active users was flat. I thought that was weird. So, I tried to access my website. It. was. soo. slow. But, this should be fine, all I had to do was resize the droplet. I shutdown my droplet and resized to 8GB / 4 CPU’s.  I waited a few minutes and fired up Google Analytics again, I was still at ~900 users and my website was still borderline non-responsive.

I was in a panic.  My flight was getting close to boarding. In a rash decision, I decided to try resizing again. Bigger is better, right? A couple clicks later, the droplet was racing around at the 64GB / 20 CPU plan.  It did not help. The post kept rising on Reddit, and my little droplet couldn’t keep up.

I enlisted a tech friend to help, while I was on the plane.  We tried increasing the memory limits on MySQL, PHP, and some obscure WordPress memory limit.  Nothing worked. Hours passed by, I knew there was a bottleneck somewhere, but I couldn’t figure it out.  My droplet basically had unlimited CPU and RAM for all intents and purposes, yet it was barely chugging a long.

The Solution

Finally after some hundreds of google searches, I came across this post on increasing the number of concurrent users in Apache. My server is running Ubuntu 14.0.4 LTS and the setup was a little bit different, but these changes finally helped. I basically doubled the max number of Request Workers that my Apache was allowed to generate:

/etc/apache2/mods-available/

mpm_prefork.conf
Added: ServerLimit 300
Change to: MaxRequestWorkers 300

<IfModule mpm_prefork_module>
	StartServers			 5
	MinSpareServers		  5
	MaxSpareServers		 10
	ServerLimit		500
	MaxRequestWorkers	  500
	MaxConnectionsPerChild   0
</IfModule>

mpm_event.conf
Change to: MaxRequestWorkers 300

mpm_worker.conf
Change to: MaxRequestWorkers 300

Then restart your Apache server for the changes to take place: service apache2 restart

If you need to roll back your settings, you may need to clear your cache by running:
sync; sudo echo 3 > /proc/sys/vm/drop_caches

Finally I saw the number of users climb past ~900 well over 1000, and my site was as responsive as normal. By this time though, my site was dropping off of r/all and r/rising’s frontpages, so I can only imagine how many people I missed during the 5 hours that my server was throttled. I hope that if you’re reading this, and you are facing a similar problem that this will help you. I know I tried googling and people mentioned CDN’s and mirroring databases, but no quick fixes like this.

How to Use Sumproduct to Divide instead of Multiply

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:

dividebysumproduct

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

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.

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

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”.

2-18-2014 11-43-00 AM

 

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:

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

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

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

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

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 ulock a protected Excel Worksheet, Step by Step

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!