Welcome to BlogEngine.NET 2.9

by Derek 28. December 2013 10:00

If you see this post it means that BlogEngine.NET 2.9 is running and the hard part of creating your own blog is done. There is only a few things left to do.

Write Permissions

To be able to log in to the blog and writing posts, you need to enable write permissions on the App_Data folder. If your blog is hosted at a hosting provider, you can either log into your account’s admin page or call the support. You need write permissions on the App_Data folder because all posts, comments, and blog attachments are saved as XML files and placed in the App_Data folder. 

If you wish to use a database to to store your blog data, we still encourage you to enable this write access for an images you may wish to store for your blog posts.  If you are interested in using Microsoft SQL Server, MySQL, SQL CE, or other databases, please see the BlogEngine wiki to get started.


When you've got write permissions to the App_Data folder, you need to change the username and password. Find the sign-in link located either at the bottom or top of the page depending on your current theme and click it. Now enter "admin" in both the username and password fields and click the button. You will now see an admin menu appear. It has a link to the "Users" admin page. From there you can change the username and password.  Passwords are hashed by default so if you lose your password, please see the BlogEngine wiki for information on recovery.

Configuration and Profile

Now that you have your blog secured, take a look through the settings and give your new blog a title.  BlogEngine.NET 2.9 is set up to take full advantage of of many semantic formats and technologies such as FOAF, SIOC and APML. It means that the content stored in your BlogEngine.NET installation will be fully portable and auto-discoverable.  Be sure to fill in your author profile to take better advantage of this.

Themes, Widgets & Extensions

One last thing to consider is customizing the look of your blog.  We have a few themes available right out of the box including two fully setup to use our new widget framework.  The widget framework allows drop and drag placement on your side bar as well as editing and configuration right in the widget while you are logged in.  Extensions allow you to extend and customize the behavior of your blog.  Be sure to check the BlogEngine.NET Gallery at dnbegallery.org as the go-to location for downloading widgets, themes and extensions.

On the web

You can find BlogEngine.NET on the official website. Here you'll find tutorials, documentation, tips and tricks and much more. The ongoing development of BlogEngine.NET can be followed at CodePlex where the daily builds will be published for anyone to download.  Again, new themes, widgets and extensions can be downloaded at the BlogEngine.NET gallery.

Good luck and happy writing.

The BlogEngine.NET team

Tags: ,


Using the User Agent StyleSheet to hide banner ads in Windows Live Hotmail

by Derek 3. June 2012 10:43

I've been using Windows Live Mail (hotmail) for years. All in all it works great and the current page design is very clean and usable. The one complaint I have is the somewhat large banner ad down the right side of the page. I'm using Google Chrome to login to my hotmail account and Chrome has a nice feature where I can modify a default css stylesheet that will be applied to all sites/pages I view using Chrome. I have modified this file to add in some css styles that will hide the banner ad section and increase the width of the email message area to fill the space.

To hide the banner ads in Windows Live Mail add the css styles below to your Chrome User StyleSheet, which is located at C:\Users\USERNAME\AppData\Local\Google\Chrome\User Data\Default\User StyleSheets\Custom.css on a Windows pc.

.Managed .WithSkyscraper #ManagedContentWrapper #MainContent {
right: 0 !important;
#ManagedContentWrapper #SkyscraperContent {
display: none;

Tags: ,

CSS | Chrome

Continuous Integration (Part 2–Jenkins CI)

by Derek 5. June 2011 15:39

In this second part of a two part post I will cover setting up a continuous integration system using Jenkins CI, an open source continuous integration server, I will be referring back to the MSBuild project file created in part 1 of this post.


The first thing you’ll need to do is to install Jenkins. I was installing Jenkins on a imageWindows 2003 Server so I downloaded the native windows package. Jenkins supports multiple operating systems including Windows, Linux (multiple distributions) and Mac, so download the appropriate one for your platform. The Windows installer is very straightforward. Once installed your default web browser is launched, loading the Jenkins configuration web site. The site failed to load for me the first time, but I believe this is because the Jenkins service was still starting. I simply refreshed the site and it came up properly.


You should now setup the configuration options for Jenkins. To do this click Manage Jenkins then Configure System. The main items you will need to configure are the Jenkins server URL, email server settings. There are a number of other options included in the default install of Jenkins that you may want to look over. Each item has a question mark icon to the right of it that you can click to get more information. Be sure to click the Save button at the bottom of the page when you are done making changes.


Jenkins has a number of plugins available to extend its capabilities. Before setting up the build we’ll need to add the plugins for Jenkins that we’ll use. To install plugins click the Manage Jenkins link in the left hand menu from the Jenkins main page (the dashboard), then click Manage Plugins. From the plugin manager screen, click the Available tab and scroll through the list to find the plugins you would like to add. Check the box next to each plugin and then click the Install button at the bottom of the page. Wait until ALL plugins have installed and click the Schedule Restart button at the top of the page. Jenkins will restart and the dashboard will be displayed once the restart has completed.

Here is the list of plugins that I have installed.

  1. imageAuthentication and User Management
    1. Active Directory Plugin – Enables Jenkins to authenticate users against active directory/LDAP.
  2. Build Reports
    1. MSTest - This plugin converts MSTest TRX test reports into JUnit XML reports so it can be integrated with Hudson's JUnit features.
    2. Violations - This plugin does reports on checkstyle, pmd, cpd, fxcop, pylint, jcReport, and findbugs violations.
  3. Build Tools
    1. MSBuild - This plugin makes it possible to build a Visual Studio project (.proj) and solution files (.sln).
  4. Build Notifiers
    1. Email-Ext - This plugin is a replacement for Jenkins's email publisher. It allows you to customize the emails that Jenkins sends.
    2. Twitter – This plugin allows Jenkins to tweet build notifications on the Twitter social network.
    3. Hudson Speaks! - This plugin gives Hudson a voice using FreeTTS.
    4. Jenkins Sounds - This plugin allows Jenkins to play audio clips as build notifications.
    5. Instant Messaging - This plugin provides abstract support for build notification via instant-messaging.
  5. Other Post-Build Actions
    1. Subversion Tagging - This plugin automatically performs Subversion tagging (or copy) on successful build.
    2. Workspace Cleanup - This plugin deletes the project workspace after a build is finished.

Configuring the plugins

Once the plugins are installed we need to set some configuration settings in order to use them. To do this click Manage Jenkins from the left hand menu and then click Configure System. You will notice that this screen now has more options than it did the last time you were on it.

  • imageMSBuild – You will need to configure the path to the MSBuild.exe program. The location of this file depends upon the version of the .Net framework you are compiling against. There is a “Framework64” folder on 64 bit machines. As long as the .Net framework version you are targeting is installed, these folders should be present. You do not need Visual Studio to be installed for MSBuild to be present. I would suggest using the .Net framework version as the name for each path, as shown in the screenshot. Here are some typical paths for a 32 bit system.
    • .Net 4.0 - C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
    • .Net 3.5 - C:\WINDOWS\Microsoft.NET\Framework\v3.5\MSBuild.exe
    • .Net 2.0 - C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe
  • Extended E-mail Notification – Choose the format (html or plain text) and configure the subject and body you would like in the email notifications. These can be overridden by each build project.

If you plan to use the Twitter, Hudson Speaks, or Jenkins Sounds plugins, configure them here as well. As before be sure to click the Save button at the bottom of the page when you are done making changes.

Setting up the continuous integration build

Now that Jenkins has been installed and configured we are ready to setup our first project/job.

imageFrom the main Jenkins dashboard, click New Job. Enter the name you would like to use to identify this build. This name will be used in notifications so make it something meaningful. Choose Build a free-style software project from the radio options and click ok. The project configuration screen will appear.


Click the “Advanced” button in the Advanced Options section to expand the section. I would recommend checking the Quiet Period checkbox and giving it a value between 5 and 60 seconds. This will prevent the build from occurring for the specified time period after the build trigger (source code change) has occurred. I like this because this allows the user committing the code to commit multiple times before the build is run, which allows a small window of time to commit multiple changes with different commit comments that will be included in the same build. It also allows the coder to redeem themselves before the infamous broken build email is sent in the event that the first check in would have broken it.

Use Custom Workspace – This will allow you to change the default location from the Jenkins install directory to a folder of your choosing. The default install path on a windows machine for Jenkins is in the Program Files folder, which can make it difficult to get your build setup correctly due to the space in the path. Although you should be able to get this working by adding quotes around paths, it may be easier to simply change the workspace.

imageSource Code Management - Setup the access to your source code repository. In my case I’m using Subversion so I simply need to select Subversion from the list and enter the URL to my subversion server. If your repository does not allow anonymous read access, you will need to enter a username and password as well.

Check-out Strategy – I typically use the "Always check-out a fresh copy” as this gives you the guarantee that the workspace has a clean copy of the latest version every time the build is preformed.

Build Triggers - For this build we will use the Poll SCM option to have source code changes trigger the build. Jenkins has a robust schedule configuration. I wanted Jenkins to check for changes every 5 minutes so I set the schedule to “*/5 * * * *”. Click the question mark icon to the right of the schedule box to get more details on how to define your schedule.

imageBuild - Now it’s time to define what is being built. In the Build section click the Add build step button. We are using MSBuild project files to define the build so we will choose “Build a Visual Studio project or solution with MSBuild” from the menu that appears. The following options now appear:

  • MSBuild Version – This is a drop down list of all the paths that you configured earlier. Select the appropriate version for the project you are building. The example project was created in Visual Studio 2010 using .Net framework version 4.0.
  • MSBuild Build File – Here you will specify the path the the MSBuild file you will be using. Visual Studio project files (*.csproj for c# and *.vbproj for VB) are valid MSBuild files. Solution files (*.sln) are not. We will be using the custom MainBuild.proj file in the sample project. The path to this file is relative to the workspace folder. In my sample the MainBuild.proj file is in the root of the SVN repository so we can simply specify the filename:
    • MainBuild.proj
  • Command Line Arguments – This is where you specify which targets within the build file will be built, as well as any other MSBuild options needed. In this first build step will will want to rebuild our custom build actions (see part 1 of this post for more information). To do this will will tell MSBuild to build the RebuildBuildActions target.
    • /t:RebuildBuildActions

Now that we have our custom build actions rebuilt, we will need to build the rest of the project. Click the Add build step button again and select “Build a Visual Studio project or soluction using MSBuild.”

  • MSBuild Version – Set this to the same setting as before, .Net 4.0
  • MSBuild Build FileMainBuild.proj
  • Command Line Arguments/t:IntegrationBuild

With our continuous integration build we want our unit tests to be run to make sure that the check-in did not break any functionality. In the sample project I am using the Microsoft unit testing framework to create the unit tests. In order to run the tests I need to add another build action. Click the Add build step button and select “Execute Windows batch command” from the list. In the Command box that appears enter the following two commands. The first will delete the old results file in case it hasn’t been cleaned up by Jenkins already, and the second runs the tests and tells MSTest to create the results.trx output file.

del /f/q results.trx
mstest /testcontainer:"CI.Tests\bin\Debug\CI.Tests.dll" /resultsfile:results.trx

Post-build Actions – Now we need to tell Jenkins what we want it to do after the build has completed. For the continuous integration build we will want the results of running our unit tests to be published and we will want to send an email. Check the “Publish MSTest test result report” checkbox and enter results.trx into the “Test report TRX file” field that appears. This will tell Jenkins to interpret the test results file and it will add a trend graph to this project’s status page. To have Jenkins send an email check the E-Mail Notification checkbox.

Now that all of the settings have been configured, click the Save button at the bottom of the page.

Setting up the nightly full rebuild

If you’re not already on the dashboard click the Back to dashboard link on the left. Now click the New Job link. Like before enter a job name and select Build a free-style software project from the radio options and click ok. The project configuration screen will appear. Enter the same settings as before for the Source Code Management section.

Build Triggers – For the nightly rebuild we will be using the Build Periodically option instead of Poll SCM. Check the box and enter “0 0 * * 1-5” which will tell Jenkins to build this project every weekday at midnight.

Build – For the nightly rebuild we will rebuild the entire solution again, but this time we will skip running the tests since they were run after every code check-in.

  1. Add a build step to rebuild the custom build actions
    1. Click Add build action and select Build a Visual Studio project or solution using MSBuild.
    2. Select the correct msbuild version .Net 4.0
    3. Enter MainBuild.proj as the build file.
    4. Command Line Arguments: /t:RebuildBuildActions
  2. Add a build step to perform the main build
    1. Click Add build action and select Build a Visual Studio project or solution using MSBuild.
    2. Select the correct msbuild version .Net 4.0
    3. Enter MainBuild.proj as the build file.
    4. Command Line Arguments: /t:IncrementBuildNumber;MainBuild

Now setup email notifications and any other post build actions you would like, then click the Save button at the bottom of the page.

That’s it. You should now have a fully functional continuous integration and nightly rebuild system.


c# | Continuous Integration | Qualtrax

Continuous Integration (Part 1 - MSBuild)

by Derek 1. June 2011 21:22


In this two part post I will be discussing setting up a Continuous Integration and nightly build system for an ASP.Net web application using MSBuild build scripts and Jenkins CI Server. I will be using Subversion (SVN) for source control.

I recently setup a continuous integration server for Qualtrax using the same methods I’ll describe here. To follow along you will need to

(Visual Studio 2010 solution, Asp.Net 4.0.) I do not believe the unit test project included will load in Visual Studio Express Edition. You will also need to download and install the MSBuild Community Tasks library. The sample project is a very basic web application that utilizes Entity Framework and the Adventure Works SQL Server sample database.



Unzip the sample project and open the CI_Sample.sln solution file. Once you have opened the sample project in Visual Studio you will see the following project structure within Solution Explorer.

  • BuildActions – Custom tasks to be run during the build.
  • Core – The data layer for the web application (uses Entity Framework and Adventure Works sample database.
  • Solution Items – Contains the MainBuild.proj MSBuild file that defines the build, GlobalAssemblyInfo.cs file that is used by all projects for version numbering, as well as some files used in unit testing.
  • Tests – Unit tests to be run for the integration build
  • CI.Web – The sample web application

Build Actions

The build actions project defines two custom actions that can be used within your MSBuild build script. Each task that will be used from within an MSBuild script will need to inherit from Microsoft.Build.Utilities.Task. See Task Writingon MSDN for more information on creating custom tasks.

  • MinifyCss – a C# implementation of the YUI Compressor. This task can be used to combine multiple CSS files into a single file and compress that file by removing blank lines and shortening various attributes.
  • UpdateAssemblyInfo – a custom task that can be used to increment the build number of your application each time a build is run.


    The data layer for this sample application was created using Entity Framework. The Asp.net website has a good exampleof creating an ASP.Net website with an Entity Framework backend. I won’t cover this in any more detail as this tutorial is meant to assist in creating the MSBuild script to build the completed project.

    Solution Items

    The MainBuild.proj file is an XML formatted document that will be used by MSBuild to perform the build. The MSDN websiteis a good reference for getting to know the format and concepts of a MSBuild project file.

    Property Groups
    A property group is used to define one or more properties (variables) that can be used throughout the build script. A property group can have a condition which must be met in order to be applied. Take the following example:

      <PropertyGroup Condition="$(Configuration) == ''">

    This property group has a condition that the $(Configuration) property must be empty. If this condition is met, the same property is set to “Release”. The reason that the condition is specified on this property group is that properties can be specified on the command line and we do not want to overwrite command line arguments.

    Any number of properties can be defined within the same property group. The values for properties can use other properties within them. For example:

    MSBuildCommunityTasks</MSBuildCommunityTasksPath> <CIBuildActionsTasksLib>$(MSBuildProjectDirectory)\BuildActions\bin\
    $(Configuration)\CI.BuildActions.dll</CIBuildActionsTasksLib> </PropertyGroup>

    As you can see, two properties are defined within this property group. The second of which uses the $(Configuration) property as part of the path to the CI.BuildActions.dll file. Line breaks have been added within the properties for readability here.

    UsingTask tags can be added to the project file to include custom tasks that you create. The following statements are used to include the custom tasks from the BuildActions project.

      <UsingTask AssemblyFile="$(CIBuildActionsTasksLib)" 
    TaskName="CI.BuildActions.UpdateAssemblyInfo" /> <UsingTask AssemblyFile="$(CIBuildActionsTasksLib)"
    TaskName="CI.BuildActions.MinifyCss" />

    Again we see that properties defined earlier are used within this statement. Each task that you define will need to have a separate UsingTask tag. You can also group these together in a separate file and import the entire file. The Import tag is used to import these separate files. In the example below we import the MSBuild Community Tasks project, which is an open source package containing numerous tasks you can use. We will use the Zip task from this library later on.

    <Import Project="$(MSBuildCommunityTasksPath)\MSBuild.Community.Tasks.Targets" />

    Each target defines a list of tasks to be run. A build may consist of one or more targets to run. The sample target below displays the message “Building the entire solution” in the build output and then calls MSBuild to build the entire CI_Sample.sln solution. This is the target we will be using for the Continuous Integration build in part two of this article.

      <Target Name="IntegrationBuild">
        <Message Text="Building the entire solution" Importance="high"></Message>
        <MSBuild BuildInParallel="true" Projects="$(MSBuildProjectDirectory)\CI_Sample.sln" 

    You may have noticed that the project file reference includes $(MSBuildProjectDirectory). This is a property/variable that is included in MSBuild and is automatically defined based on the directory in which your project file is located. Since the MainBuild.proj file and the CI_Sample.sln file are both located in the same directory we define the path as $(MSBuildProjectDirectory)\CI_Sample.sln. Additional properties are available (see MSDN site for more info) and you can also define your own properties through the use of ProperyGroup tags as described above. We also tell MSBuild that it can build the projects within this solution in parallel (if possible based on references) and that it will preform a full rebuild.

    Targets defined in the MainBuild.Proj file and their uses:

    • RebuildBuildActions – This target will rebuild just the BuildActions project. This is done as a separate task because the BuildActions dll is used within other targets and as such cannot be modified during a build. It is important to note that this target uses the Exec Command tag to build rather than using the MSBuild tag. Exec Command can be used to execute any command line. In this case we are executing DevEnv, which is the Visual Studio development environment. This will require Visual Studio to be installed on the build machine. This can be modified to use the MSBuild tag instead and was only done in this manner for illustrative purposes.
    • IncrementBuildNumber – As its name implies this task will increment the build number within the GlobalAssemblyInfo.cs file. We will use this target during the nightly full rebuild, but not during the integration build.
    • IntegrationBuild – This target will define the tasks that will be completed during the continuous integration build. This build will occur each time new code is checked into the source code repository.
    • MainBuild – This task will be used for the nightly rebuild. This will perform all necessary tasks to complete a full build of all projects, publishing the web application and zipping up the resulting files.

    It is also important to note that many of the tags used in this project will not be recognized by Visual Studio’s IntelliSense. As such they will be underlined as errors. Do not be alarmed as these will still function correctly.

    An Item Group is a way of defining a property that has multiple values. You can think of this as an array. We use an Item Group to define the list of files to include in the zip file that we create at the end of the main build.

          <ZipFiles Include="$(WebPublishDirectory)\**\*.*" Exclude="*.zip" />

    In the example above “\**\*.*” is used to recursively (\**\) include all files (*.*). Instead of using a recursive path such as this, you could specify each file individually by adding the same inner tag multiple times with different values such as:

          <ZipFiles Include="$(WebPublishDirectory)\*.*" Exclude="*.zip" />
    <ZipFiles Include="$(WebPublishDirectory)\bin\*.*" Exclude="*.zip" />
    <ZipFiles Include="$(WebPublishDirectory)\Styles\*.*" Exclude="*.zip" />


    When using an Item Group variable within a statement, you will need to use the
    @(VariableName) syntax instead of the $(VariableName) syntax.

    <Zip Files="@(ZipFiles)" ZipFileName="Website.zip" 
    WorkingDirectory="$(WebPublishDirectory)\" />

    I hope this example has helped you to create a MSBuild project file. In the next post I will use the MSBuild project file in this example to setup a continuous integration system.



    Tags: , , , , ,

    c# | Continuous Integration | MSBuild | Qualtrax

    Down with IE 6!

    by derek 1. December 2009 16:58

    Microsoft actively urges IE 6 users to upgrade,and I certainly agree! Continuing to support IE 6 in any modern web application can be a major headache. I have seen a number of display, JavaScript and other issues that will only occur on IE 6. My company currently tests our software on IE 6-8, Firefox 3 and many of the developers use Google’s Chrome browser (although not officially supported by Qualtrax yet.)

    IE 6 has an intolerably slow JavaScript engine, which only adds to our frustrations at having to continue to support this browser. Qualtrax 4.0+ utilizes a great deal of JavaScript in order to make the site more responsive and to lessen load on the server. With IE 6’s slow JavaScript engine the benefits of our approach are completely negated on the client side. IE 7 and 8 are quite a bit better in JavaScript performance then IE 6, however they are both quite slow when compared to Firefox and Chrome. Microsoft’s Windows President Steven Sinofsky has himself admitted that they need to do some more work on their JavaScript performance for Internet Explorer 9.I really hope IE9 will be a true competitor with regard to JavaScript performance.

    Microsoft is specifically targeting the home users of Windows XP in their statements about upgrading. I would like to see more corporations upgrade their user’s machines as well. I know some of these companies have legacy applications that will not work correctly in IE 7 or above, which is their main reason for not upgrading.  For these companies I would highly recommend you at least test the application(s) in question in IE7 or IE8, or urge the vendor to fix the compatibility issues. I know this is not always possible and moving to a different system can be extremely time consuming and costly, but as time goes on you will find that more and more applications will stop supporting IE 6 (something I would love to be able to do) and you will find yourself in a tough situation.


    Rants | Qualtrax | Internet Explorer

    I hate spammers!

    by Derek 23. November 2009 22:58

    I know it's been said a million times but really it is incredibly annoying! I can overlook the emails. All those stupid ones with the dollar signs in place of s' in order to try to sneak "penis enlargement" through the spam filters. Yeah they're annoying but I have to say that most spam filters seem to have caught up to most spammers. I rarely see anything in my main mailbox that shouldn't be there. My most hated form of spam are text messages. If it isn't already that should be illegal. Great. Thanks. That informative text about the cheap Rolex's just cost me money. That's like them mailing you letters COD. I'm also quite annoyed that every time I take a look at this site (which I'll admit is not very often) I see that some spammer has left comments on just about every one of my posts that are completely irrelevant to the topic discussed and have about as good grammar as a two year old. I guess it's about time for me to add a CAPTCHA to my comment posting page.



    New widget FMyLife

    by Derek 3. July 2009 19:15

    I just added my first new item to this site for your enjoyment. I created a new widget to display random entries from the FMyLife.com website. This widget was incredibly easy to create. FMyLife has an API that simply presents the data as XML. Using the .Net Framework's System.Xml namespace I was able to easily parse the XML data.


    [code:c#]private void Page_Load(object sender, EventArgs e)
        String FmlXml = GetWebPage("http://api.betacie.com/view/random?key=readonly&language=en");
        XmlDocument xDoc = new XmlDocument();
        XmlNodeList xnl = xDoc.GetElementsByTagName("item");
        // we only want the details of the first one.
        if (xnl.Count > 0)
            foreach (XmlNode node in xnl[0].ChildNodes)
                if (node.Name == "text")
                   this.lblRecentFML.Text = node.InnerText;

    public static string GetWebPage(string Uri)
        StringBuilder sb = new StringBuilder();
        byte[] buf = new byte[8192];
        HttpWebRequest request = (HttpWebRequest) WebRequest.Create(Uri);
        HttpWebResponse response = (HttpWebResponse) request.GetResponse();
        Stream resStream = response.GetResponseStream();
        string tempString = null;
        int count = 0;
            count = resStream.Read(buf, 0, buf.Length);
            if (count != 0)
                tempString = Encoding.ASCII.GetString(buf, 0, count);
        } while (count > 0);
        return sb.ToString();

    Be sure to add using statements for both System.Xml and System.Net.

    You can also download the widget here:

    FMyLife.zip (3.54 kb) Simply download and unzip into the widgets folder.

    Tags: , ,


    Qualtrax version 4.2

    by Derek 30. June 2009 22:18

    Now that Qualtrax version 4.1 SP1 has been released we have begun development of the next release of Qualtrax. Version 4.2 will include Active Directory integration, a WYSIWYG workflow designer tool, and many enhancements to the testing and training features. This is of course subject to change without notice. Smile Today I worked on adding some new features to the Mass Import Tool. This tool allows users to import a large number of documents into Qualtrax at one time. For this next version of Qualtrax the Mass Import Tool will now have the ability to import documents as Uncontrolled Documents, associate standards with documents, as well as import the documents as dual extension documents but only with the view file being an auto-generated PDF.

    Tags: ,


    Welcome to my new website and blog

    by Derek 25. June 2009 22:49

    Although I had a blog with blogger previously I had completely neglected it for 2 1/2 years. I recently purchased my own domain name and I have decided to put it to good use. First with this new blog and later for some more content although I'm not sure what just yet. All the posts from my old blogger site dpinkerton.blogspot.com (excluding a farewell message from a few minutes ago) have been copied to my new blog here on this site. This site was created using the free, open source blog engine aptly named BlogEngine.Net. I have customized the theme slightly, but I am not satisfied with it yet, so more changes will come as soon as I get around to it. I'm hoping the new blog engine and website will help motivate me into keeping up with the site. Only time will tell but hey as of right now I've posted more in the last 20 minutes than I had in the last two years.


    Excel: my new favorite program

    by Derek 1. December 2006 15:53

    I have an excel spreadsheet with a custom vba function that will simply return the document property with the name given in the parameter. This function works wonderfully. I can enter it as the formula for a particular cell in the worksheet (such as =DocProperty("MyVariable")) and that cell will now display the value of that document property.

    Although this does work as expected the first time the cell's value is calculated it does not automatically update if the document property changes. Excel seems to cache the value of each cell. This makes sense for performance issues however I am using DSOFile.dll to update these document properties from another application (namely Qualtrax.) With this setup the values are changed when the document is closed and I would like the new values of these variables to be displayed each time the document is opened.


    I have seen similar behavior with "Fields" in Word documents where the displayed value does not correspond to the actual value stored. The solution in Word documents was to add a VBA macro that runs automatically when the document is opened, so I figured I would create a similar macro that would update my Excel documents. I figured something like the following would work:


    Public Sub Auto_Open()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    Next ws
    End Sub


    However I wanted to be sure that in the event of a worksheet with a large number of calculated cells I only update the ones that need to be udpated. So I came up with the following code:


    Public Sub Auto_Open()
    ' - This macro will refresh any cells where the formula
    ' - is a call to the DocProperty function
    Dim ws As Worksheet
    Dim cell As Range

    For Each ws In ThisWorkbook.Worksheets
    For Each cell In ws.UsedRange.Cells
    If InStr(cell.Formula, "DocProperty") > 0 Then
    End If
    Next cell
    Next ws
    End Sub


    Much to my surprise this function did not work. I stepped thorough and discovered that the cell.Calculate call was being made for each cell whose formula was a call to my DocProperty function (shown below), however for some reason Excel is not actually recalculating the value! After a couple hours of torment I found that resetting the formula on the cell will force a recalculate so simply changing cell.Calculate to cell.Formula = cell.Formula fixes the problem and actually makes Excel recalculate the value of that cell. WHY OH WHY does the Range.Caclulate function not actually force a recalculate of a cell whose formula calls a vba function?!?!?

    Complete working code.


    Public Function DocProperty(property As String) As String
    Dim WB As Workbook
    On Error Resume Next
    If TypeOf Application.Caller Is Range Then
    Set WB = Application.Caller.Parent.Parent
    Set WB = ActiveWorkbook
    End If
    DocProperty = WB.CustomDocumentProperties(property)
    WB.Saved = True
    End Function

    Public Sub Auto_Open()
    ' - This macro will refresh any cells where the formula
    ' - is a call to the DocProperty function
    Dim ws As Worksheet
    Dim cell As Range

    For Each ws In ThisWorkbook.Worksheets
    For Each cell In ws.UsedRange.Cells
    If InStr(cell.Formula, "DocProperty") > 0 Then
    cell.Formula = cell.Formula
    End If
    Next cell
    Next ws
    End Sub

    Tags: , ,


    <<  March 2017  >>

    View posts in large calendar

    Page List

      Month List