Navigating Excel Cells in VBA

Posted on by
Reply

Fixed Reference

There’s plenty of documentation on how to move around Excel in VBA if you know exactly what cell you want to move to.

For example to move to cell D3 all you need to do is add this line of code:

ActiveSheet.Range("D3").Select

Or let’s say you want to select not just a single cell but a range of cells, like D3 thru E4:

ActiveSheet.Range("D3:E4").Select

Relative Reference

But let’s say you want to run your macro from anywhere on the spreadsheet instead of a fixed location.

For example, you’re cursor is on some cell and you want to move left 2 columns and up 1 row:

ActiveCell.Offset(-1, -2).Select

Or maybe instead you to stay on the same row, but move to the right 5 columns:

ActiveCell.Offset(0, 5).Select

Now let’s say you want to move left 9 columns and up one row.  Once you get there you want to select from the new starting cell to one cell to the right and one row down so that four cells are selected:

ActiveCell.Offset(-1, -9).Select
Range(ActiveCell, ActiveCell.Offset(1, 1)).Select

Now just imagine that once you’ve selected those four cells what you really want to do is copy the values, or the formulas, from the top two cells to the bottom two cells.  Enter the following code all on one line:

Selection.AutoFill Destination:=Range(ActiveCell, _
ActiveCell.Offset(1, 1)), Type:=xlFillDefault

If you need to fill more rows then just extend the range by changing the row offset to whatever number you need.

The Function

ActiveSheet.Range("{cell}").Select
ActiveCell.Offset({row}, {column}).Select

Orphaned SQL Server Users

Posted on by
Reply

The Scenario:

You’re working on a SQL database locally and when you restore the database on the  production server the login no longer works.  The user has been orphaned.

Solution #1 – Delete and recreate the user from scratch

Solution #2 – Restore the orphaned user

Check to see if the server registers any orphaned users.  Open SQL Server Management Studio and select the database you restored.  Run this stored procedure:

EXEC sp_change_users_login 'Report'

The resulting list will confirm what users are orphaned, if any.  As long as the Login exists under the SQL Server Security -> logins you can reattach the user to the login by running this stored procedure:

EXEC sp_change_users_login 'Auto_Fix', '{user name}'

If the login doesn’t exist under the SQL Server Security -> logins you can create a new login for the user using the following stored procedure:

EXEC sp_change_users_login 'Auto_Fix', '{user}', '{login}', '{password}'

Care and Feeding of SQLParameters and SQLHelper

Posted on by
Reply

I chased my tail for hours on this one, but finally found the combination that works.  Here’s the basic layout

Function GetProjects(ID as int64, Name as string)

Dim arParams As SqlParameter() = New SqlParameter(2) {}

arParams(0) = New SqlParameter("@ID", IIf(ID = 0, _
    DBNull.Value, ID))
arParams(1) = New SqlParameter("@Name", IIf(Name = "", _
    DBNull.Value, Name))

Return CType(SqlHelper.ExecuteReader(ConnectionString, _
    CommandType.StoredProcedure, _
    GetFullyQualifiedName("GetAT_LU_Positions"), _
    arParams), IDataReader)

End Function

To instantiate the SQLParameter array you must declare the number of variables available.  In this case, there are two.

To build the parameters I’ve included code to null out the variables if they are not used.  This becomes important if you are creating multiple overloads of the function (in this case the GetProjects function).

When calling the SQLHelper there are several overload possibilities.  In this case we want the overload with the following options: 1-connection, 2-command type, 3-command name, 4-SQL Parameters.  There is one version of the overload that accepts options 1,3, and 4 and will work without an error, but the problem is nothing will be returned.  Obviously, that’s a problem.

This is a very open ended scenario, but the important piece for me was getting the SQLHelper set up and properly called and this was the way to do it.  If you are implementing SQLParameters and SQLHelper and have questions for your particular routine, let me know.

How to Uninstall a DotNetNuke module

Posted on by
Reply

These instructions cover uninstalling a module from DotNetNuke 05.06.03, but should be good for DotNetNuke 5 installs.

If you are having trouble with a module on a particular page and are unable to remove it from the page, uninstalling the module will remove the offending code from the page as well.

1. Login as host to your DotNetNuke site and select “Extensions” from the host menu.

 

2. Find your module in the extensions list (in this case “Widget World”) and click the “X” icon.

3. The uninstall package screen confirms what module you are attempting to uninstall.  If this is correct click “Uninstall Package”.

4. Once the uninstall is done DotNetNuke reports the steps taken.  Any errors will appear in red.

Provided there were no errors you’re all done.  If there were errors you will likely need to delete each file and database component manually.

How to Manually Install a DotNetNuke Module

Posted on by
Reply

These instructions cover setting up a dynamic DotNetNuke module in Visual Studio 2010 and manually installing the module in DotNetNuke.  While these instructions are based on DotNetNuke 05.06.03 they are applicable to all of DotNetNuke 5 versions.

These instructions do not cover in detail how to program each piece of the module, but does detail what files are need for a basic dynamic module and how to install them.

For the sake of clarity we’re going to create and install a module called “Widget World” and our company will be “iWidgets”.

If you’ve already install the Visual Studio Starter Kit follow steps A-D, skip steps 1-3 and continue at step 4.

A. In Visual Studio, with your project loaded, right click on the project root and select “Add New Item”

B. In the dialogue select “DotNetNuke Dynamic Module”.  Dynamic Module includes a database backend.  If you do not need a database backend select “DotNetNuke Simple Dynamic Module”.  Clear all text in the “Name” entry box at the bottom of the dialogue, enter “WidgetWorld” and click “Add”.

C. The Starter Kit will automatically add a folder called ModuleName to both the App_Code and DesktopModule folders.  Rename the ModuleName folder to WidgetWorld in both cases.  In the DesktopModules\WidgetWorld folder you can delete the Documentation folder.  You won’t need it.

D. This is the most tedious part.  Open all of the files that were generated by the VSSK.  Find and Replace all instances of “YourCompany” with “iWidgets”.

Skip steps 1-3 if you used the Visual Studio Starter Kit.  Otherwise start here.

1. In your Project create the following folders on the root of your project:

App_Code\WidgetWorld
DesktopModules\WidgetWorld

2. A dynamic module has a database backend, so you will need several database connecting files in the App_Code\WidgetWorld folder:

WidgetWorldController.vb (entry point to the functionality)
WidgetWorldInfo.vb (maps fields to properties)
DataProvider.vb
SQLDataProvider.vb (makes the actual calls to the database)

To get started the only file that needs code in it is the WidgetWorldController.vb.  Copy the following lines into it:

Namespace CUIM.Modules.CUIM_BusinessDir
Public Class CUIM_BusinessDirController
End Class

End Namespace

3. The Desktop Modules file is a bit more complicated and will have several files and folders

01.00.00.SqlDataProvider (build the sql components for auto install)
WidgetWorld.dnn (base manifest file for auto install)
EditWidgetWorld.ascx and .vb (add/edit control)
Settings.ascx and .vb (special settings for the module)
Uninstall.SqlDataProvider (uninstalls sql components for auto uninstall)
ViewWidgetWorld.ascx and .vb

App_LocalResources\EditWidgetWorld.ascx.resx
App_LocalResources\Settings.ascx.resx
App_LocalResources\ViewWidgetWorld.ascx.resx

*You don’t actually need any code in these files to install the module, although without any code when you place it on a page in DotNetNuke it will be totally anti-climatic.  :)

4. Log in to your DotNetNuke site as “Host” and click on: “Extensions” in host menu

At the bottom of the page click the “Create New Extension” Link.

5. In the Create New Extension page select “Module” from the drop down and wait for the page to refresh.  The “Name” field must match the folder name in the file system.  “Friendly Name” should be a user-friendly title.  Description is a general overview of the module.  In our case this is version one, but in subsequent updates you have the flexibility of using build, major revision, and minor revision settings.  Click “Next”.

6. In Module Specific Details the “Folder Name” will be filled in for you.  The “Business Controller Class” entry can be pulled from the WidgetWorldController.vb file.  The general format can be seen in the figure below.

If the module will be exported from one site to another, check “Is Portable”
If the module contains searchable data, check “is Searchable”
If the module will be upgraded at a future date, check “is Upgradable”
If you are the host for the site and will allow users to select modules, but certain modules require payment first, check “is Premium module”

Click “Next”.

7. General info, just fill in these fields with your information.  Click “Next”.

8. DotNetNuke returns you to the Extensions list and you should see the Widget World module listed near the bottom of the module section.  Click the pencil icon.

7. The configure extension settings screen shows all of the information you’ve entered in the install process.  If you’re planning on selling the module, or distributing the module you’ll want to fill in the “License” and “Release Notes” sections.  To complete the setup click on the “Add Definition” link.

This will bring up the “Create Definition” link. The “Default Cache Time” is typically for content that will not change often.  Since our module is databased we expect the content to change regularly so it’s better to leave the cache time at 0 so we always get a fresh copy.  If the content doesn’t change often, setting the cache will decrease load time.

Enter “Widget World” in the friendly name and click “Create Definition”

Click “Add Module Control”

8. Add the View control which is the default control.  Leave the “Key” field blank for this control.  By leaving the “Key” field blank the module will default to this control when the page containing the module is loaded.  The fields left blank and unchecked are not necessary for a basic install, but you can click on the “?” icons to learn more about them.

The “Source” is the View control itself.  Scroll through the drop down list until you find the control “ViewWidgetWorld.ascx”.

Set “Type” to “View” and click “Update”

9. Add the “Edit” and “Setting” controls by clicking “Add Module Control” again and following the layouts below.  Notice that while “Edit” and “Settings” have different key fields, they have the same “Type” setting.

Your “Module Controls” list should now look like this

10. Assign the module to a page by returning to the home page of your site and select “Add’ from the Edit mode Page Functions menu.

 

 

11. On the new page select the module in the “Add New Module” mode and set the Title, Visibility, Pane, and Insert settings as you need them.

And that should do it!

 

How to Create a Module Install Package (Basic)

Posted on by
Reply

These instructions

  1. cover creating a basic Install Package for DotNetNuke version 05.06.03 (45).  Stay tuned for updates on more complex installs.
  2. assume that you have successfully manually installed your module and that the App_Code and DesktopModules folders contain all the required files.  Required files change based on the complexity of your module.  For example if there is no back end database to your module you will likely not need the App_Code folder and you definitely won’t need the version.SqlDataProvider, or uninstall.SqlDataProvider files.

Once your custom module has been successfully installed on your Dot Net Nuke site:

1. Log in as “Host” and click on “Extensions” in host menu

To select the extension, click on the pencil icon next to it in the list of extensions.

At the bottom of the page click the Create Package link.

 

2. The Create Package screen (shown below) will present the information you have already entered for Name, Type, Friendly Name, and Version.  Below that leave the “Use Existing Manifest” unchecked, as we will be creating a new manifest.  Make sure “Review Manifest” is checked.  If everything looks good, click “Next”.

3. The file selection page should be automatically filled in, but check the list to make sure no files were missed.  Leave the “Include Source” unchecked.  If everything looks good, click “Next”.

4. Assemblies are not covered in these instructions because a basic install would likely not include any assemblies.  Leave the box blank and click “Next”.

5. Reviewing the manifest.  The manifest should all ready be filled in for you since DotNetNuke creates the manifest.  If this page is blanked something went wrong and you’ll need to start over.  If it’s not blank simply review it to see how it’s built.  This will help get you familiar with the manifest when creating more complex packages.  If everything looks okay, click “Next”.

6. Create Package.  This page will also be filled in for you.  While it is possible to alter, or shorten, the Manifest and Archive file name there is no need outside of aesthetics and naming convention.  For now, leave them as is and click “Next”.

7. At this point the package will be created.  The listing will show all the steps that were completed and if there are any errors they will show up in red.  If there are no errors you can find your package in your DotNetNuke installation in the Root\install\module folder.

8. To test your install download the package and simply install it on a DotNetNuke installed of equal or greater version.  Note that your package will like only be compatible with whatever version of DotNetNuke you use to create your install package.

Retrieve Dot Net Nuke Custom Module Settings

Posted on by
Reply

If you’re creating custom modules for DotNetNuke you’ve certainly tried to add your own settings to the settings.ascx in the default module setup.

Creating your settings happens in 5 steps. Where ever you see “settingname” replace with your setting’s name:

1) Add the html to the Settings.ascx file like this (the formatting doesn’t matter; customize to fit your site

<table>
  <tr>
    <td>
       <dnn:Label ID="lblSettingName" runat="server"
        ControlName="txtSettingName Suffix=":"></dnn:Label>
    </td>
    <td>
       <asp:TextBox ID="txtEventModuleLink" CssClass="NormalTextBox"
        Width="350" runat="server" />
    </td>
  </tr>
</table>

2) Add code to the Settings.ascx.vb to load the value for the setting into the textbox created in step one.  Do not worry that the setting doesn’t actually have a value yet.

Public Overrides Sub LoadSettings()
  Try
    If (Page.IsPostBack = False) Then
      If CType(TabModuleSettings("settingname"), String) _
             <> "" Then
        txtSettingName.Text = _
             CType(TabModuleSettings("settingname"), String)
      End If
    End If
  Catch exc As Exception           'Module failed to load
    ProcessModuleLoadException(Me, exc)
  End Try
End Sub

3) Add code to the Settings.ascx.vb to save the value from textbox created in step one.

Public Overrides Sub UpdateSettings()
  Try
    Dim objModules As New Entities.Modules.ModuleController

    objModules.UpdateTabModuleSetting(TabModuleId, "settingname" _
      , txtSettingName.Text)

    ' refresh cache
    Entities.Modules.ModuleController.SynchronizeModule(ModuleId)

  Catch exc As Exception           'Module failed to load
    ProcessModuleLoadException(Me, exc)
  End Try
End Sub

That completes the code to Edit and Save the custom setting.  Now you’ll want to retrieve that setting in your module.

4) In your user control (eg View_ModuleName.ascx, Edit_ModuleName.ascx), whether it’s the View, Edit, or another custom control, create a module level variable for the setting.  In this case we’ve created a string, but you can “ctype” the variable into whatever type you need.

Private mSettingName As String = Common.Utilities.Null.NullString

5) Get the value of the setting and assign it to the module level variable mSettingName.  This code should be placed in the Page Load event.  Normally, it will assigned whether the page is in postback, or not.

If CType(Settings("settingname"), String) <> "" Then
  mSettingName= CType(Settings("settingname"), String)
End If

The variable is now available to the entire user control (eg Edit_ModuleName.ascx.vb).