Skip navigation
All Places > All Things PI - Ask, Discuss, Connect > Blog > Author: rkoonce
rkoonce

Excel Is Our Friend – Part V

Posted by rkoonce Employee Dec 14, 2016

Excel Is Our Friend – Part V


Congratulations! You have escaped Interface Island! And if you are ever caught on another similar island, you have developed the survival skills to survive and eventually escape. The bad news is that some of your toolbox was left behind on that island. When you find yourself in need the tools there, you could always swing by there and get a “copy” You left your shelter behind, it will have to be rebuilt. On a new island, project references will need to be re-added, functions re-created in the new Island’s project. But what if there was a way to take the recyclables with you? Like that user defined function? That might come in handy anywhere. So, the good news is that you can have your cake and eat it too.

How to: Making Your Custom Spreadsheet Functions Available Anywhere

To use a custom function, the workbook containing the module in which you created the function must be open. If that workbook is not open, you get a #NAME? error when you try to use the function. Even if the workbook is open, if you use the function in a different workbook, you must precede the function name with the name of the workbook in which the function resides. For example, if you create a function called DISCOUNT in a workbook called Personal.xlsb and you call that function from another workbook, you must type =personal.xlsb!discount(), not simply =discount().

You can save yourself some keystrokes (and possible typing errors) by selecting your custom functions from the Insert Function dialog box. Your custom functions appear in the User Defined category:

ExcelFig7.png

In the instructions, a workbook type of xlsb is referenced above. But the screenshot has an xlsm… my workbook was created long ago, originally as an .xls (I am a dinosaur).

* Tip: An even easier way to make your custom functions available at all times is to store them in a separate workbook and then save that workbook as an add-in. You can then make the add-in available whenever you run Excel. Detailed instructions are found at the office.microsoft.com site linked here. This varies slightly from my own “old skool” methods, but I agree with the authors that this new way seems an easier way. I’ll be converting to an add-in when I get the chance.


Note: An .xlsb extension indicates Excel Binary Format. The .xlsm extension denotes a macro enabled workbook. An .xlam extension indicates Add-in. All of these formats require a trust for location and/or a much more complicated trust for publisher.

  • How to: Let Excel Teach You VBA


All of us can learn a lot of VBA by using the “Record Macro” function. After recording your actions within Excel, just examine the VBA code afterward and learn. It’s like pulling the curtain back on the Wizard of Oz. Prior to VBA, I was using Excel 4’s macro language, and the macro recorder was instrumental for me, personally, when first learning VBA. I still use it on occasion.


* On the Developer Ribbon, click “Record Macro”.
* Note the Macro Name and location.
* Perform some actions within the workbook, select a range of cells, change the font, change the number format, type some text or a formula.
* Click the Stop Recording button (same location as above).
* Examine the code that was generated.

ExcelFig8.png

  • FizzBlog Exercise:


  • Create a new macro-enabled workbook.
  • Copy the mySplitter function into this new workbook (the functions that we created earlier in this blog series).
  • Save this workbook as an Excel Add-In > “MyFunctions.xlam”
  • In Excel>File>Options> Add-ins>Load the newly created add-in in Excel.
  • Create a new workbook. Click Insert Function
  • FizzBlog Challenge:


  • In the dialog to select the user defined functions, is the function name prepended with the add-in worksheet name? workbook name? Or none? Prove it with a screenshot.
  • Were you able to make it work with a regular workbook? Or does it only work in macro-enabled workbooks?
  • Did you have to do anything special to make it work? (Did I leave out any significant steps in the FizzBlog Exercise?) If so, please explain.
  • Sharing your recyclables:


  • If you have a useful reusable function(s), please share it below.
  • Perhaps you just had an idea for one, please post it as a comment! (Santa might be reading this thread?)


This entry concludes my blog series for "Excel Is Our Friend" (perhaps I will append it someday). I hope that some of you have learned a few things along the way. Regardless, I really hope you enjoyed the series.

I plan to begin another series soon, but first I will have to choose my next destination. I have several ideas that I have been kicking around, but I would welcome any suggestions regarding any "islands" you would like to explore.


Cheers! (from the grass hut)

Excel Is Our Friend – Part IV


While writing this blog series, I became consciously aware… my Dad knew that I didn’t know the names of those tools. I bet he was smiling to himself under the truck… It was a sneaky way of teaching me something useful, without my consent.

  • How to: VBA and FSO


Aside from importing the MDB, and getting current values with DataLink, we have confined ourselves to within the boundaries of the workbook. But with column G, we will access the File System Object, and extract a file property (version) from external files. This same library has other useful methods as well, such as testing for file and folder existence, reading and writing files, to name a few. The following will be limited to getting the file versions, but you can find plenty of information on the web should you have the need to utilize those other methods.


Note: The following code/ example will not always work. It is dependent on a number of things, but most particularly security. If accessing files on a remote machine, and your current logged in credentials don’t equate to an administrator on the remote machine, or if a firewall or policy prevents access. The code utilizes the hidden administrative shares of the remote machine. The remote machine could be in a different domain or workgroup, causing Windows Authentication to fail also. A quick test of accessibility, using start> run>
\\YOURinterfacemachinenameORipaddress\c$

Enough with the disclaimers!

  • In the Visual Basic Editor (VBE), add a reference for Microsoft Scripting Runtime (scrrun.dll) -> Tools menu> References (if you skipped “Foundation” entry, Part II)
  • Create an ActiveX Commandbutton on your worksheet, where the click event will execute the following VBA code (in the screenshot, the button is labeled “GetVersion” and has the name “cmdGetVers”)
  • The following code belongs in the worksheet module for the sheet that is the “parent” container for our button>


Note: Regarding #2, I use an ActiveX Commandbutton, but Excel’s Form Control Button will work too (with some adjustments that I won’t cover here).

 

 

Private Sub cmdGetVers_Click()  ‘The name is unimportant, but with AcitveX, it should match the object name’
    Dim strPath As String     
  If Selection.Columns.Count > 1 Or Selection.Column <> 7 Then
       MsgBox "Please select cell(s) in column G before executing GetVersions"
       Exit Sub
  End If
  For Each c In Selection         
       strPath = "\\" & c.Offset(0, -5).Value & "\" & Replace(c.Offset(0, 11).Value, ":", "$", 1) & c.Offset(0, -2).Value & ".exe"
       c.Value = GetVersion(strPath)     
  Next c
End Sub 

 

The code above calls the function below, passing the path\filename:


4. The following code belongs in the Module1, created previously to hold our User Defined Function "mySplitter", Part III.
Public Function GetVersion(strFilePath As String) As String
   Const ForReading = 1, ForWriting = 2, ForAppending = 3
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   Dim fs As Scripting.FileSystemObject, f As File, ts, s
   
  On Error Resume Next
     Set fs = CreateObject("Scripting.FileSystemObject")
     GetVersion = fs.GetFileVersion(strFilePath)
     If GetVersion = "" Then    ‘if no version, the mod date instead’
       Set f = fs.GetFile(strFilePath)
       GetVersion = f.DateLastModified
  End If
  If GetVersion = "" Then 'just in case it ain't there’
       GetVersion = "no data or file"
  End If
        
End Function 
Note: This function could also exist on the worksheet module above. (it is good practice to use a module for functions (IMO). However, Excel requires that User Defined Functions be in a module, like “mySplitter” above. They won’t work otherwise.) Notice the use of “Public” in the declaration. This allows the function to be called by the remote procedure in the worksheet module. Declaring it as Private would result in an error when we clicked the button above, “Sub or Function not defined”. Translation= I don’t “see” anything named “GetVersion”. (Access to a Private function would only be available to functions and subroutines that also reside within Module1, Public functions would be available globally at the Project level.)


5. Select a range of cells with your mouse, like cells “G28:G30”, and click the button! Hopefully, you will see versions popping into those cells in the blink of an eye.




You may notice that I have several unused variables in the function. These are standard variables that I copied from another routine. I use FSO a lot, for creating, reading, writing, renaming files, etc. More details can be found here:

Visual Basic for Applications Reference for FileSystemObject Object
http://msdn.microsoft.com/en-us/library/aa242706(v=vs.60).aspx

When exploring and understanding any block of code, stepping through the code one line at a time in debug mode is an invaluable learning technique. (see this link http://www.cpearson.com/excel/DebuggingVBA.aspx)

Hopefully, if you have followed along with no major issues, you have a working document. One that saves you a bit of time, monitoring and accessing your own interfaces. But more importantly, you have added new things to your “toolbox”. We have covered quite a bit of ground here: Navigation, Relative vs Absolute Addresses. PI MDB, VBA, Project References, VB Modules, Public vs Private Declarations, FSO, IF Statements, FOR loops, User Defined Functions, Functions (VLOOKUP, MID, LEN, FIND, HYPERLINK, SUBSTITUTE, ISNA), and the use of Nested Functions. Hopefully, you don’t feel like you have been drinking from a fire hose. And even if you are not fully versed in the usage of each and every one of these, just knowing that they exist, and some idea of when to use them, is a huge step in itself. I hope you can use this information to escape from an island…

Part V in the series will be posted soon, concluding this series and contains a FizzBlog Challenge (A bit of a contest). Winners will receive restroom breaks, automatic adjustments (for DST) to their system clocks, and an assortment of free junk mail!

Excel Is Our Friend – Part III


Toolbox

As I child I remember my Dad doing most of the auto repair himself. And if anyone was outside doing anything, I was there (a simpler time, before Xbox, even before cable TV). So, when I use the word toolbox, I remember asking Dad… What is this one called? And What is this one for? And I remember him asking, hand me the so and so… my standard clueless response was “this one?”

While escaping the island has the appearance of being our main objective, the true focus is on learning the tools.

  • How to: VLOOKUP – built-in function

 

This built-in Excel function is something you definitely want in your “toolbox”.


The VLOOKUP function, which stands for vertical lookup, can help you find specific information in large data tables. In addition, and equally useful, it can determine what is not in the table. In the example below, I use VLOOKUP to gather external information into a list formatted to my preferences. After creating so many PI Interfaces here, I was having trouble remembering the specifics without having to look for them. So, I built a spreadsheet for a quick reference. But I did not want to manually populate this listing. So with a few items from the toolbox, I was able to semi-automate the gathering of the data, as you will see below:


First, I imported the Module Database from my PI Server, into a separate sheet in my workbook (renamed that sheet “R2-Int1MDB”). I used the PI-SMT > Module Database Builder Add-in > Import Items. I just imported the tree where the interfaces reside (“\\R2-PI\%OSI\Interfaces”), including all subitems, values, etc., everything.

 

After determining which values were of use, I created column headings for my list, some of my columns are visible in the example below.

 

ExcelFig6.png


The next step was to get a listing of the interfaces, for column A. To get this, I went to the sheet “R2-Int1MDB”. After selecting any cell in the column headings of the “list”, I clicked Excel’s Data Menu, and clicked the “Filter” button. (Excel has a help topic on “Quick start: Filter data by using an AutoFilter”, You can point at the icon in the ribbon and press F1).

 

Using “AutoFilter”, for my “Type” column, I selected “Module”, for the “Name” filter, I used the Text Filter > “contains”, and entered a filter value of “\\*\*\*\*\*” (without the quotes). This filtered my list to machine\interface instance. Placing my activecell on the first item in the filtered list (clicking on that cell), I used the shortcuts mentioned above (Ctrl+Shift+DownArrow) to select the entire list. Then, Ctrl+G, followed by Alt+S (or click “Special”), Alt+Y (or click the “Visible cells only” radio button, then . Next, I navigated back to the list I was building. Pasting the values that you see in column labeled “ModulePath”.


So at this point, I have a complete listing in column A of all of the Interfaces used for this PI Server Collective, “R2-PI”. Not very useful, by itself, but this provides the basis for gathering data for our list.


So, let’s utilize the various tools available to us to populate our columns. Our first victim is InterfaceNode, in column B. There are actually several ways to get that tidbit, since it is present in the ModulePath in column A, the last segment, when parsed with “\”.
  • How to: Using Nested Functions and User Defined Functions t
  • Since the root path is always the same length (24), we could use this formula in B28: =MID(A28,25,FIND("\",MID(A28,25,99),1)-1). <– the Find function here is a “Nested Function” within the third argument of the Mid function
    • Explanation of arguments: Explanation of arguments: Since we know the values of interest all begin with the string and fixed length, LEN("\\R2-PI\%OSI\Interfaces\")=24, we can begin our search at character 25, seeking the position of the next "\", subtracting 1 from the length to exclude the "\". If the string was not of fixed length, we would have to nest additional “LEN” functions where we presently use “25”.
  • Alternatively, we could create a User Defined Function, outlined in this post
    • By creating a module for our workbook, in VBE, creating “Module1”, and pasting the UDF’s custom code from the linked post, we can use the following in cell B28: “=mySplitter(A28,"\",5)”
Continuing on, while skipping the “UFOTags” column for now, we move to the “Binary” column. Here we will use a combination of Concatenation and VLOOKUP.
So, looking at E1, you see that I have typed “||InterfaceBinary”. This is the attribute that we want to extract from the MDB Import, for each of the interfaces in column A.

So, we need the contents of A28 appended with the contents of E1, using the & operator, like so: $A28&E$1. Note the position of the $ signs in the concatenation. $ indicates absolute references, as in “absolutely column A”, and the row is relative to the formula location. Meaning that we can fill right and the formulae with always use column A. Likewise, we can fill down, and the formulae with always reference $1, row 1.
We will nest this function inside an argument of the VLOOKUP function.
So, selecting E28 with our mouse, and typing =VLOOKUP( and then clicking “fx” on Excel’s formula bar will present the dialog below (fx is circled red in Screenshot).

Lookup_value = what to “find” in the Table Array (it searches only in the 1st column of the Table_Array, returning only the first matching entry) You can see the use of &, in the first argument in the screenshot’s dialog box.
So, in the screenshot, our Lookup_value = \\R2-PI\%OSI\Interfaces\R2-INT1\RDBMSPI1||InterfaceBinary
Table_Array = where to look for the value. Note the format of this argument, it can be drag-selected, if you click the icon at the right edge of the field. Note that you will have to manually add the $, so that you can filldown and fillright. The rows from the mdb import have an absolute address. Filling down, without absolute cell references would omit rows from the top of the list… You can also toggle between absolute and relative references, by selecting them, in the formula bar or a dialog, and using the key, repeatedly.
Note that the first column is B, where we would our Lookup_value = \\R2-PI\%OSI\Interfaces\R2-INT1\RDBMSPI1||InterfaceBinary
Col_index_num = which column of the Table_array do you wish to return if Lookup_Value is found in column 1 of Table_array
This is the column in the array, not the sheet. In my array, column 19 is the “PropertyValue” column.
Range_lookup = False means find an exact match. I almost never use True for this argument, but it can be useful on occasion.



After completing the dialog with , the formula is usable in the other columns/ rows with the use of filldown and fillright, with exceptions for columns C, D, G. and I. Note that row 1 of these columns are blank. Other methods are used for these.


In the interest of brevity, I won’t go as deeply into the details of columns C, D, and I, other than providing my formulae here:
C28 is =IF(ISNA(O28),"none",E28&K28&"_UFO2_State_"&O28)
O28 is out of view in the screenshot, but it is a lookup for “||Arguments|/UFO_ID”
D28 is =IF(C28="none","none",PICurrVal(InterfaceList!$C28,0,InterfaceList!$H28))
So, if the UFO tags are specified in the MDB, I use DataLink to show me the current values… if the interface running?
I28 is =HYPERLINK("\\"&B28&"\"&SUBSTITUTE(R28,":","$",1),"Explore Folder")
R28 contains the “||CurrentInstallationPath”, and since I am an admin on the remote machine, I can access this through the administrative share, replacing the “:” with a “$”


· Tip: System overload

Sometimes I find a spreadsheet behaving sluggishly, due to the calculation burden that I have created. With larger lists, and more and more functions, the spreadsheet will tax the CPU as it recalculates. As a lot of the functions above have already served their purpose, they can be replaced with the values which have already been calculated or retrieved. So, in these cases, copy those cells containing functions and replace, using Copy > Paste Special > Values. This will replace the functions with their static values unburdening your CPU from the unnecessary.


For those of you that have been eyeballing the Version column and the CommandButton… I saved it for Part IV.

Excel Is Our Friend – Part II

Foundation

Before diving into our subject matter too deeply, I need to be true to my words (from my Welcome to Treasure Island”blog entry):

As I proceed, I hope that I remain mindful of the less experienced “islander”.

Below, I have included some background information, explanations of terminology, the “extra” information for the beginners and the perplexed. Recognizing each “island” as being different, this one needs a map. Some advanced readers may prefer to just skim this one and move on to Part III. Below, we will tweak our environment by changing the options available on the Excel ribbon and discuss controls we plan to use. Then, by adding a project reference, we will extend our functionality of our workbook.

Stepping out of the surf:

  • First things first: Create a new workbook.
  • Save it as a macro-enabled workbook. This will give it a filename extension of .xlsm, saved to “My Documents” (trusted location – more on that here).
Note: You should use a trusted location to store a file when you don't want that file to be checked by the Trust Center, or if you don't want it to open in Protected View.
  • How to: Show the Developer Tab on the Ribbon
  • http://msdn.microsoft.com/en-us/library/vstudio/bb608625.aspx
  • In Excel> On the File tab, choose the Options button.
  • Select the Customize Ribbon
  • Select the Developer Ribbon, so that it is displayed on the Toolbar Ribbon
  • How to: Using ActiveX controls
  • I prefer the ActiveX for a number of reasons - Access to properties (in the 2nd screenshot below) gives me visibility and greater control over the configuration. It provides a different user experience, overall (for the developer).
  • Clicking the Developer Ribbon > Insert > Choose the control > cursor changes to crosshairs > drag select the area for size and position on your sheet ExcelFig2.png
  • Clicking the Developer Ribbon >Design Mode > Right-click the control > Properties
  • Note the Name and Caption fields have different values, configurable.
  • It is best to name the button before adding the code.
    • If renamed, the code will lose its assignment to the button.
    • Double-clicking the renamed button takes you to a new procedure, abandoning the old procedure/
    • Renaming the old procedure should be avoided. Instead just move the code with cut/ paste.
  • Double-Click the ActiveX Commandbutton (in design mode) to create/ edit the code
    • And you will find yourself in the VB IDE, at the highlighted area, ready to add the code for the click event of your object (button)
    • At the top of the code pane, you will see 2 dropdown lists. 1 to select the object, the other selects the event.
    • If an event exists, it will show as bold
    • ExcelFig3.png
  • Back on the worksheet, You may notice the formula bar has “=EMBED("Forms.CommandButton.1","")”
    • This just Excel’s way of denoting the embedded object. Resembles a function, but it should be ignored.

ExcelFig4.png




· How to: Adding a reference to your project

Just as Excel is our friend, we can recruit “a friend of a friend” by adding a reference to our VBA project. Once we have this reference, we can utilize the functionality of the referenced project as well.

o In the Visual Basic Editor (VBE), add a reference for Microsoft Scripting Runtime (scrrun.dll) -> Tools menu> References (refer to screenshot below)

o We will utilize this reference in Part IV of the series

 

ExcelFig5.png



Note: Remember to exit Design Mode, by clicking it again, when finished.


So, with the preliminaries out of the way, we have established our base camp on the island. In Part III in this series, we will begin building upon the foundation we created above.

rkoonce

Welcome to Treasure Island

Posted by rkoonce Employee Nov 6, 2013
Treasure Island


A bit of introduction is in order. People need answers. Purpose and direction demand explanations. Inquiring minds want to know... Even as children we learned the value of the question... Why?



What

The name of the blog has several meanings implied. Obviously, I would want you to come away believing that you received something valuable for the time spent here. And I think we can all relate to occasionally being stranded on a "technical" island. Some place in a project, where you feel stuck, like you don’t have the resources, knowledge or tools to "escape" the challenges of the situation.

When stranded in such a manner, we eventually find treasure, including lessons learned. We learn survival, and remember the skills. Partly because we know that, someday, we may find ourselves on another island. If forgotten, we could be eaten by the cannibals (they live in conference rooms and rarely visit the islands - but beware, some will make sacrifices of the islanders encountered).


Why

These days, our technical islands come with internet access. So when we find ourselves stranded, we reach for Google or Bing, our basic survival tools. But what if we didn’t know what to ask; if we had no "hits" that outlined our escape? No “easy button”? We need survival skills, so we can apply them accordingly.

So, with this blog, I hope to provide a few “messages in a bottle”, for those times when you find yourself washed up on a deserted beach.




Who

I still remember when computers and everything about them was mysterious. At first, my learnings were forced. I practically made myself read things that may as well have been written in Hebrew. It was a steep path for me. I have been stuck on so many islands, I no longer need two sticks to make fire… I am not special, I am just a survivor that would like to share.


My goal is to share information and to provide “How-To” articles. As I proceed, I hope that I remain mindful of the less experienced “islander”. Besides, I think I can help them the most. Too often, in my own searches for answers, I have found information that excluded me from finding joy by the omission of fundamental details. So, I will try to include the obvious assumptions, while recognizing that “obvious” is a relative term among the audience. And this fits with me personally. I am one who gives you all the info, leaving you to filter out what you already knew. But should I leave you perplexed, with a key detail omitted, please ask. The only dumb question is the one that goes unasked.


I look forward to sharing some stories and lessons learned… from my time on Treasure Island.
Excel Is Our Friend

With the initial “Welcome to Treasure Island” blog post, I answered the What, Why and Who. With subsequent blog entries, it is all about the How. I’d like to start off with something not directly PI related… but often used in tandem, Microsoft Excel. I will be the first to admit, I love Excel, hence the naming of my first blog series – “Excel Is Our Friend”.

It is only natural that OSIsoft would have add-ins for the “duct tape” that holds the business world together. I marvel at the fact that our higher education systems hasn’t fully grasped its role in the big scheme of things. To me it would seem perfectly logical for someone to make the statement, “ I have a bachelor’s in business, but I continue to focus on achieving my doctorate in Microsoft Excel.”

I plan to discuss Excel here, initially talking through some overlooked “treasures” and features. But as I proceed, the add-ins will inch into the discussion, including those delivered by OSIsoft. And since OSIsoft products are VBA compliant, I plan to jump in with both feet there, as well. Often, during Bomgar and WebEx sessions, work is halted when someone says “wait, how did you do that?” (I admit it, sometimes it is me that is speaking). Thinking about it now, I realize that the question is usually asked with some urgency, for fear of missing the opportunity. There are so many little things that can be done the hard way, and it is those which compel us to ask the question.. “How did you do that?” I hope you find some answers here, without having to ask.

Let me tell you about the island that I found myself upon. It was a maze of PI interfaces, with a variety of data sources, talking to several PI Servers. Some tags were input and some output. I had working samples that I could not easily find or quickly trace. I needed a map to find my way around. So, I pointed my mouse at Excel and started making a list… in the blog entries that follow.


Excel Is Our Friend – Series Overview

  • Series Introduction
  • Part I – Exploring the Island
    • Navigating Excel
    • Moving Around and Selecting
    • Utilizing shortcuts with PI DataLink
  • Part II – Foundation - Building your shelter
    • Environment
      • Developer Ribbon
      • ActiveX Controls
      • Adding a Reference to a Project

  • Part III – Toolkit - Building a raft
    • Spreadsheet functions
    • Nested Functions
    • User Defined Functions – “roll your own”
    • System Overload
  • Part IV – Toolkit – adding a sail
    • Microsoft Scripting Runtime, aka FSO
    • VBA and FSO – using File System Object
  • Part V – Recycling
    • Personal.xlsm, aka MyFunctions Add-in
    • Recording Macros - Let Excel teach you VBA
    • Challenge

Excel Is Our Friend – Part I



Navigating Excel



So, what can I tell you about navigation in Excel? That there are tabs for sheets and scrollbars right and bottom? No, I want to highlight the less known, navigational “tricks”. Shortcuts for quickly moving around or selecting a large range, without the clumsiness of scrolling.


Environment > Take in the following familiar looking screenshot:

Excelfig1.png

· How to: Moving Around and Selection Range

With a list this small, we can, obviously, just take our mouse and click where we want to go. But what if the list was 1000 rows x 1000 columns?

The following assumes that all of cells have values (except for A22, A23), no blank cells:
With B2 active, using Ctrl+DownArrow, you would move to B1000, in a single keystroke.
With B2 active, using Ctrl+RightArrow, you would move to ALL2, in a single keystroke.



So, with blank cells present, like A22, A23
With A2 active, using Ctrl+DownArrow, you would move to A21. It stops there because that is the end of the contiguous range of non-blank cells.
Pressing Ctrl+DownArrow again would take you to A24 (skip blanks, stops on next value)
Pressing Ctrl+DownArrow again would take you to A1000
Pressing Ctrl+DownArrow again would take you to A1048576 (end of sheet)
Pressing Ctrl+Home takes you back to the top…

So, this is much faster navigation than the scroll bar.

So, while Ctrl+ is navigating, Ctrl+Shift+ is selecting.
With A2 active, using Ctrl+Shift+RightArrow, your active cell remains A2, but you have extended the “Selection” from A2:ALL2 = 1000 columns, faster than a speeding mouse.
With A2 active, hold down Ctrl+Shift, then RightArrow, then DownArrow (3 times), you have extended the “Selection” from A2:ALL1000 = 1000 columns x 999 rows (we started on row 2).
With A1 active, hold down Ctrl+Shift+End, you have extended the “Selection” to include all cells with data, even the non-contiguous data, including previously populated cells that are now clear or “UsedRange”.

  • Definition > UsedRange - Range object that represents all the cells that have contained a value at any time.


· How to: Using Shortcuts in PI DataLink

You have the taglist in column B, and you want to use DataLink > CurrentValues, in Column J:

Click in Cell J2, Click the DataLink Ribbon > Current Values
Your cursor in the datalink dialog will be in the field for the tags or “Data Items”, depending on your version.
Now, click in cellB2, Ctrl+Shift+DownArrow (you just selected 999 tags - selects all of the tags in the column, until it hits a blank cell), hit or modify the settings for timestamp in the dialog.

OK, so hopefully, you can see the value here, both in “zipping around” and for selecting a large or small range of cells within Excel sheets.

More favorites:

Ctrl+End would move the active cell to ALL1000 (Ctrl+Home would take you to A1) These two are also quite handy in notepad, try it out in a pipc.log?
Ctrl+PageUP moves you to previous tab (sheet), Ctrl+PageDown moves you to the next tab.
Ctrl+G is GoTo
Ctrl+G, followed by Alt+S (GoTo Special - I use this for selecting/ copying only the visible cells (used later in the article).


While I have only mentioned a few, a complete listing of shortcuts can be found here:
http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-in-excel-2010-HP010342494.aspx

There are other features that can also help with navigation, and some serve both in navigation and also in simplifying the complexities, like the use of defined names. There are also shortcut equivalents for VBA in Excel’s Object Model.


This blog series will continue in a few days, so keep your spyglass pointed towards Treasure Island. I welcome your comments and feedback.

Filter Blog

By date: By tag: