Tag Archives: VBA

#Excel Short and Sweep Tip #10 (Add Custom AutoCorrect with UDF) by David Hager

I was reading articles at exceloffthegrid.com and I came across this technique to add an autocorrect replacement in Excel.

https://exceloffthegrid.com/autocorrect-hack-to-speed-up-data-entry/

I thought that it would be useful to make a way to automate this process. So, I created the following VBA function procedure (put in a general module in VBE).

Function AutoCorrectAdd(ReplaceWhat As String, ReplaceWith As String)

Application.AutoCorrect.AddReplacement What:=ReplaceWhat,Replacement:=ReplaceWith

End Function

So, if cell A2 contained RobC and B2 contained https://powerpivotpro.com/, then the formula

= AutoCorrectAdd(A2,B2), run in a worksheet UDF

would add that autocorrect replacement the built-in AutoCorrect list. If you then type RobC, it would be replaced by https://powerpivotpro.com/.

You could also use this function in a Sub procedure. Say that you had an Excel list of nicknames and their corresponding e-mail addresses. You could create a Sub that operated on each row of the two column list with the AutoCorrectAdd function to convert the list to autocorrect replacements (not shown, a challenge to the reader 😊).

Hope that you find this useful.

Advertisements

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

http://stackoverflow.com/questions/828496/how-to-retrieve-this-computers-ip-address

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

IPaddress1

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

Next

 

GetIPAddress = strIPAddress

End Function

HTH!

#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

Randomize

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.

=TEXTJOIN(“,”,,PickRandomFromList(A2:A22,5))

PickList1

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.

PickList

#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.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

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:

http://www.listendata.com/2013/04/excel-udf-dependent-cell-comment.html

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

addcomment1

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.

AddComment

#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

Application.Volatile

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

Else

Arr(N) = “”

End If

Next

 

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).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

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).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Is3ddn1

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

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames

#Excel VBA: Create a Table of File Locations and URLs for Your Favorites by David Hager

This is one of my personal Excel applications that I have used over the years to collect information on my Internet Favorites. When you run the CreateURLListFromFiles procedure, it creates a list of URLs and favorites file location. You browse to your Favorites folder and select to generate the table. Column B contains the URLs that can be converted to hyperlinks by selecting them and running the Convert To Hyperlinks procedure. You can use this on multiple folders where your favorites are stored and the results will be appended. Enjoy!

You can download the file here.

MyURLCollector

Mine3D for #Excel: An Excel-based Game by David Hager

I would like to share with you an Excel game that I made back in the day. It is called Mine 3D, and it works like the popular minesweeper game, except that the playing board is “3 dimensional”. The worksheets are protected, but with no password. There is a interesting combination of formulas and programming used in the making of this game. So, feel free to explore how it works. Enjoy!

You can download the Excel game here.

Mine3D