Tag Archives: Excel

Excel Short & Sweet Tip #9 (Get IP Address) by David Hager

Here is a way to get the IP address of your computer. This was originally reported at


Make sure that you have the required reference (WMI Scripting) selected under Tools, References in the VBE as shown in the figure below.


Copy/paste the following code to a general module in the VBE and then type =GetIPAddress() into a worksheet cell (or, use it in a VBA procedure).

Function GetIPAddress()

Const strComputer As String = “.”   ‘ Computer name. Dot means local computer

Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i

Dim strIPAddress As String


‘ Connect to the WMI service

Set objWMIService = GetObject(“winmgmts:” _

& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)


‘ Get all TCP/IP-enabled network adapters

Set IPConfigSet = objWMIService.ExecQuery _

(“Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE”)


‘ Get all IP addresses associated with these adapters

For Each IPConfig In IPConfigSet

IPAddress = IPConfig.IPAddress

If Not IsNull(IPAddress) Then

strIPAddress = strIPAddress & Join(IPAddress, “, “)

End If



GetIPAddress = strIPAddress

End Function



#Excel: Generating a Random Sampling From a List Using VBA and the TEXTJOIN Function by David Hager

You might have a need to generate a random sampling of items from an Excel list. The technique presented here accomplishes this without any helper columns. The PickRandomFromList VBA function shown below returns a random array of items from a worksheet list, the size determined by the 2nd argument of the function.

Function PickRandomFromList(rList As Range, sArray As Integer) As Variant

Dim N As Long

Dim Arr() As Variant

Dim lArr() As Variant

Dim Temp As Variant

Dim J As Long

Application.Volatile False

Arr = rList.Value


ReDim lArr(LBound(Arr) To sArray

For N = 1 To sArray

J = CLng(((UBound(Arr) – N) * Rnd) + N)

Temp = Arr(N, 1)

lArr(N) = Arr(J, 1)

Arr(J, 1) = Temp

Next N

PickRandomFromList = lArr

End Function

It is important to note that this UDF does not recalculate with every change in the worksheet by using the line of code Application.Volatile False. It will only recalculate if a change is made in the cell containing the formula or in the specified worksheet range. The conversion of the array to a delimited list is done through the use of the TEXTJOIN function.



This technique may be particularly useful for the selection of random committees from an employee list. I hope that this will give you some ideas about situations requiring random sampling.

You can download the workbook here.


#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

There was a lot of interest in my post on modifying a shape with a worksheet UDF.


The original idea was posted in 2007. I seem to remember, though, that the use of a UDF to modify cells occurred before that time. The initial discovery was that a UDF could add a cell comment to ANY cell. I can’t find the original reference, but this technique was last documented at:


I have modified the the UDF shown in that article to add a timestamp feature.

Function AddComment(rng As Range, str As String) As String

If Not rng.Comment Is Nothing Then rng.Comment.Delete

TimeStamp = Date & ” ” & Time

If Len(str) Then rng.AddComment.Text str & ” ” & TimeStamp

rng.Comment.Visible = True

End Function

In the example workbook, I entered the AddComment function in cell D6, but the range argument can point to any cell. In fact “range formulas” can also be used.

The INDEX, OFFSET and INDIRECT Excel functions all return ranges, so any formulas built with these functions can be used in a UDF where a range argument is required. The following example uses the INDEX function.

=AddComment(INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)),”MAX value in NumRange”)

where NumRange is defined as =OFFSET(A$1,,,COUNTA($A:$A),) ‘auto-expanding range

In this example, the formula INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)) returns the range of the cell containing the max value of NumRange, and as such it can be used in the first argument of the UDF. So, as numbers are added to column A as shown in the figure


the function will add a timestamped comment to any cell in that range that is the max value.

Obviously, there are numerous and more complex examples that can be built using this technique. I hope that you will find this useful in your projects.

The example file can be downloaded here.



#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant


Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name


Arr(N) = “”

End If



DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).


In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).



Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.



#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.


So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:


So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.


where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:


By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:


Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.


You can download the example file here.



#Excel: Four Super Filter Techniques by David Hager

I wanted to improve a few design features and eliminate a few bugs in some of my related recent posts,

so I have combined the concepts demonstrated by the following 4 articles located at:





into a single workbook. Among the changes I did/did not make are:

  1. No change to the formula that given occurance number for records in a filtered list.
  2. I moved the formula list from above the filtered list to another worksheet.
  3. I fixed some issues with the VBA code for the Filter Criteria UDF. I then located it with the
  4. the static list on the other worksheet.
  5. I included the Advanced Filter search with a custom list with this model.

In moving the static calculated list to another worksheet, I had significant problems adjusting the formulas to work in their new location. For the formula:

I had to add the sheets names in the parts of the formula that point to the filtered list on Sheet1. Also, to create the correct positioning of the first formula(s) in the list (place on row 3 instead of row 2), I had to change:




The FilterCriteriaEnh function was amended to fix several faults – an error handler was added to fix the scenario where .Criteria2 did not exist and adding a line of code (Criteria2=.Criteria2) in case it did (see code in the example workbook).

The criteria UDF was relocated to the row above the static list. The following formula in A1 on the Static worksheet is:


Note that the range in the UDF argument points at the filtered list on Sheet1.


The list used for the advanced filter lookup is also located on the Static worksheet. As expected, when I activated the advanced filter the filter criteria UDF did not return a result, since no “filter” was applied to the list.

I hope that I have explained what is available with this new filter model, but if not, please go back and reread the 4 base articles.

You can download the (enhanced) file here.




#Excel: Using Advanced Filter with a Custom List By David Hager

In a previous article about Excel’s advanced filter,


I used a reference to Charley Kyd’s use of SUMPRODUCT to filter a list based on a custom list.


In his article, he created a formula used in a helper column to filter based on a custom list of criterias.

I realized that the same type of formula could be used in Excel’s advanced filter. A lot of the utility of using the Excel advanced filter feature is hindered by the fact that an array formula cannot be used as a criteria. However, since the SUMPRODUCT function creates a non-array formula, I surmized that it could be used as a criteria.

BTW, Rob Collie and I published an article on this same subject in PowerPivot a few years ago.


In this scenario, a list containing a column of employee numbers needs to be filtered by a custom subset of those numbers. This could be done by manually selecting those numbers from the filter dropdown criteria, which would take a very long time for a large custom list. The custom list resides on a different worksheet than the list to be filtered (in this case, on the List worksheet defined as EmployeeList).

The criteria formula (in B2) is:


Note that this formula “starts” at A5, the first item in the column.

Before applying the filter, the column looks like this:


The advanced filter information is set up as shown below.


Finally, the list after filtering looks like this:


In this example, there is only one column in the list/table. Obviously an employee table would contain many more columns. Also, the employee custom list could potentially could contain 1000’s of employee numbers. I hope that you use this technique useful in your work on employee records.

You can download the example file here.