Daily posts of Excel tips…and other stuff
Archive for the ‘User Defined Functions’ Category.
I have a need to verify some user input is hexadecimal (0-9 and A-F).
Public Function IsHex(sInput As String) As Boolean
Dim lResult As Long
On Error Resume Next
lResult = CLng("&H" & sInput)
IsHex = sInput = "0" Or lResult > [...]
I don’t use user defined functions in add-ins all that often, but I happen to have a particular add-in with one UDF. And I happened to have recently moved that add-in. And all my links happen to be broken.
Fortunately, Jan Karel has an exhaustive write-up on how to fix and prevent this problem. [...]
I’m working on some VBA to take some of the drudgery out of payroll.
Public Function ThisFriday() As Date
ThisFriday = Date + 8 - Weekday(Date, vbFriday)
End Function
Public Function LastFriday() As Date
LastFriday = Date + 1 - Weekday(Date, vbFriday)
End Function
I thought I needed to know [...]
In Excel, you can get the Username by using Application.UserName. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user’s initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via [...]
I need to do a VLOOKUP but find occurrences later than the first one. I’ve listed out all of my assemblies and all of their component parts like so:
Parent Child Quantity
To list the the children of a particular parent, I need a function that can find multiple occurrences of the parent. I considered [...]
Excel provides an easy way to create a table of hyperlinks and even save the workbook as a web page. The built-in method for creating a hyperlink is Insert > Hyperlink (Ctrl+K). The problem with this method is mass changes, like renaming your server. Editing each one (if you have a lot) [...]
Hi All,
Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF).
UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s work as expected. You get into trouble [...]
I have two custom add-ins loaded that use application-level events. I only want those events to run when a workbook associated with my application is active. Otherwise, the events in my purchase order application will try and do stuff to my invoices and vice versa. Not good.
For every application, the first thing [...]
