Solutions @ hans-eric.com

Problem

You want to automate the exporting of a chart within an Excel worksheet to a PNG image file using a VBA macro.

Solution

Private Sub ExportChartToFile(Worksheet as String, ChartNumber As Integer, Filename As String)
    Dim objChrt As ChartObject
    Dim myChart As Chart
    Dim FilePath As String

    Set objChrt = Sheets("Worksheet").ChartObjects(n)
    Set myChart = objChrt.Chart

    FilePath = ThisWorkbook.Path & Filename & ".png"

    On Error Resume Next
    Kill FilePath
    On Error GoTo 0

    myChart.export Filename:=FilePath, Filtername:="PNG"
End Sub

Here’s an example of its usage:

Public Sub ExportBurndownChart()
    ExportChartToFile "Burndown", 1, "Burndown Chart"
    MsgBox "Burndown chart was exported successfully"
End Sub

More Info

The code was based on the answer in a Stack Overflow question.

Problem

You have a Team Foundation Server connected worksheet in Excel 2010, and want to automate the clicking of the Refresh button on the Team ribbon.

Solution

Add these two procedures to your VBA macro:

Private Sub RefreshTeamQueryOnWorksheet(worksheetName As String)

    Dim activeSheet As Worksheet
    Dim teamQueryRange As Range
    Dim refreshControl As CommandBarControl

    Set refreshControl = FindTeamControl("IDC_REFRESH")

    If refreshControl Is Nothing Then
        MsgBox "Could not find Team Foundation commands in Ribbon. Please make sure that the Team Foundation     Excel plugin is installed.", vbCritical
        Exit Sub
    End If

    ' Disable screen updating temporarily so that the user doesn’t see us selecting a range
    Application.ScreenUpdating = False

    ' Capture the currently active sheet, we will need it later
    Set activeSheet = ActiveWorkbook.activeSheet
    Set teamQueryRange = Worksheets(worksheetName).ListObjects(1).Range

    teamQueryRange.Worksheet.Select
    teamQueryRange.Select
    refreshControl.Execute

    activeSheet.Select

    Application.ScreenUpdating = True
End Sub

Private Function FindTeamControl(tagName As String) As CommandBarControl

    Dim commandBar As commandBar
    Dim teamCommandBar As commandBar
    Dim control As CommandBarControl

    For Each commandBar In Application.CommandBars
        If commandBar.Name = "Team" Then
            Set teamCommandBar = commandBar
            Exit For
        End If
    Next

    If Not teamCommandBar Is Nothing Then
        For Each control In teamCommandBar.Controls
            If InStr(1, control.Tag, tagName) Then
                Set FindTeamControl = control
                Exit Function
            End If
        Next
    End If
End Function

You can then create a macro that updates the query on a specific worksheet, for example “Open Bugs” like in the below example:

Public Sub UpdateAndExportBurndownCharts()
    RefreshTeamQueryOnWorksheet ("Open Bugs")
End Sub

More Info

The code in this solution is based on the code in the following blog posts:

• Connect TFS to Excel
• Programming for the TFS Excel Add-in

Problem:

You want a simple input dialog to have the user enter a text in iOS.

Solution:

You can use an UIAlertView:

UIAlertView * inputAlert = [[UIAlertView alloc] initWithTitle:@"New Event" message:@"Enter a title for the event" delegate:self cancelButtonTitle:@"Cancel" otherButtonTitles:@"OK", nil];
inputAlert.alertViewStyle = UIAlertViewStylePlainTextInput;
[inputAlert show];

You also need to add the UIAlertViewDelegate protocol to the view controller:

@interface ViewController : UIViewController

And the delegate method when the user taps the Cancel (index 0) or OK (index 1) buttons:

- (void)alertView:(UIAlertView *)alertView clickedButtonAtIndex:(NSInteger)buttonIndex
{
    if (buttonIndex == 1)
    {
        NSLog(@"Entered: %@",[[alertView textFieldAtIndex:0] text]);
    }
}

Problem:

In iOS, updating the user interface should be done in the main thread. However, many asynchronous methods invoke the callback on an arbitrary thread. How can I make sure a particular piece of code is run in the main thread?

Solution:

You need to use the grand central dispatch:

dispatch_async(dispatch_get_main_queue(),^{
    // The code to run on the main thread goes here
    [self.tableView reloadData];
});

Problem:

To avoid using singleton models in your iPhone/iPad apps, it’s necessary to create the root view programmatically. This way you’re able to inject the model at creation time instead of in the viewDidLoad method.

Solution:

Here’s how to set up a navigation-based application in code in XCode 4.2 (with automatic memory management enabled).

In the application:didFinishLaunchingWithOptions method of the application delegate (AppDelegate.m file), insert the following code:

[objective-c]

self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
// Override point for customization after application launch.
self.window.backgroundColor = [UIColor whiteColor];
self.myViewController = [[MyView alloc] initWithNibName:@”MyView” bundle:nil];
self.myViewController.myAppModel = <create and set the application model here>;
UINavigationController *navigationController = [[UINavigationController alloc]
initWithRootViewController:self.myViewController];
[self.window addSubview:navigationController.view];
[self.window makeKeyAndVisible];
return YES;

[/objective-c]

Note that it’s important to keep the root view controller (myViewController in the above example) as a retained property in the application delegate. Otherwise the automatic memory handling may release it prematurely in which case your app may fail with an EXC_BAD_ACCESS message code. Se the following link for more information:

http://stackoverflow.com/questions/2070204/exc-bad-access-with-ibaction

[objective-c]

@property (strong, nonatomic) MyView *myViewController;

[/objective-c]

Problem:

Copy and other command shell commands don’t work with UNC paths, i.e. \\server\dir.

Solution:

Use pushd <path> to temporarily assign a drive letter and map it to the path.

pushd \\server\rootfolder\subfolder

When done, use popd to unmap the letter.

Problem:
We want to extract a date and time of the format YYYY-MM-DD MM:SS from a body of text using Ruby.

Solution:
This is extremely easy in ruby, and other languages that features regular expressions.

require "Date"

text = "Some text containing a date and a time, for instance 2010-03-19 17:25."

if text =~ /(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})/
  dt = DateTime.new($1.to_i, $2.to_i, $3.to_i, $4.to_i, $5.to_i)
end

Problem:

You’re using a third party wordpress theme but have made changes to customize it for your own needs (changed a picture, added advertising, etc.). When the third party releases an upgrade to the theme you’d like to upgrade but don’t want to lose your changes.

Solution:

Let your version management system move your changes over to the new version for you. I use Subversion, but any modern VMS should do fine as well. Here’s the steps I use to upgrade from, let’s say Cool Theme 1.0 (that contain my changes) to Cool Theme 1.1.

1. Import the original version of Cool Theme 1.0 (without my changes) to Subversion. I use a repository path like /reposroot/cooltheme/trunk.
2. Create a tag to freeze the 1.0-version. In Subversion you just copy the above directory, I name the new directory something like /reposroot/cooltheme/tags/1_0.
3. Create a branch from the 1_0 tag. I’d name the branch /reposroot/cooltheme/branches/r1_0.
4. Checkout the r1_0 branch to a local working directory.
5. Copy the files of the updated version of Cool Theme, the one that contain your changes, to your working directory.
6. Check in. Now you have the current version in the r1_0 branch.
7. Delete the working copy if you like, it has served it’s purpose and the time has come for the actual upgrade.
8. Checkout the trunk branch (reposroot/cooltheme/trunk in my example) to a working directory.
9. Copy the files of the upgraded theme to the working directory.
10. Check in the changes.
11. Once again, delete the working copy if you wish.
12. Create a tag for the new version (reposroot/cooltheme/tags/1_1 for example).
13. Create a branch for the new release (copy the 1_1 tag to reposroot/cooltheme/branches/r1_1)
14. Merge the changes between r1_0 and r1_1
15. Handle conflicts
16. Check in.
17. Voila! You should now have an upgraded version with your changes in the new release branch (reposroot/cooltheme/branches/r1_1)

Problem:

We want to insert linebreak character sequences into a string in C#. 

Solution:

The platform safe way of doing this is by using the Environment.NewLine property.

msg = "An error occurred: " + Environment.NewLine + e.Message;

Composing a string with more than one line break can become really messy, so using string.Format might be a good idea.

msg = string.Format("{1} An error occurred!{0}Exception: {0}{2}",
  Environment.NewLine, CurrentTime, e.ToString());

Problem: You want to change an action’s visible property and does so in its OnUpdate event handler, but it doesn’t seem to work. Once an action is hidden it can’t seem to become visible again.  Solution: By design Delphi won’t invoke OnUpdate event handlers for actions that have their Visible property set to false. Instead, one has to override the form’s UpdateActions method tod do the dynamic checking and setting of Visibility.

type
  TMainForm = class(TForm)
  ...
  protected
  ...
    procedure TMainForm.UpdateActions; override;
  ...
  end;

...

procedure TMainForm.UpdateActions;
begin
  inherited;
  // Those actions are updating their visible
  // properties and must therefore be updated
  // here and not in their respective update
  // event handlers. This is due to the fact
  // that invisible actions are not concidered
  // by the inherited UpdateActions loop.
  // This is by design according to CodeGear.

  CancelAction.Visible := IsRunning;
  StartAction.Visible := not CancelAction.Visible;
end;

More information can be found on the Delphi forum, here: https://forums.embarcadero.com/thread.jspa?threadID=18206&tstart=90