Dutch Gemini's Weblog Pages

March 19, 2012

Excel VBA Oddity – Replace function not returning entire string

I just boomed into this funny oddity using VBA with Excel.

I wanted to generate data to a text file in the TEMP folder, so I used the FileSystemObject’s GetSpecialFolder() to obtain the TEMP folder name and I crafted a file name using the system time. Then I joined the 2 strings with the backslash character (\).

To avoid problems with double backslash characters, I used Replace() to replace them with single backslash. Since I did not want to replace them at the beginning of the string because I am not sure if the temp folder is not something like for instance in ‘\\server\share\...‘, I decided that I would start replacing from position 3 onwards, like in:

sFileName = VBA.Replace(sFileName, "\\", "\", 3, -1, vbTextCompare)

From Help, I read (and highlight only the relevant parts):

Syntax

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description



start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.

Reading this, I presume that the Replace() statement above should start replacing only from position 3 onwards. Well, it does not. What happens is this:

Replace() replaces ‘find‘ in ‘expression‘ with ‘replace‘, starting at position ‘start‘ but returns a string that does not contain the characters up to ‘start‘.

In other words, it appears that VBA first Mid()‘s the string at position 3, then starts replacing, and finally returns the shortened string. See here as well (includes workaround).

Dutch

PS: if you read Help to the end, it says «If start > Len(expression) then zero-length string is returned». This implicitly confirms that if you want something from the string back using Replace() then you should definitively set start to a value below Len(Expression). And I say that if you want back everything (obviously, with the replacements done), set start to 1.

Advertisements

March 12, 2012

Excel oddity – Embedded UDF is not executed when workbook is opened

The Situation

I have a workbook containing a UDF, let’s call it for convenience MyUDF(), that returns a calculated value. I also have an Add-In (XLA/XLAM) that contains a function with the exact same name. This is not coincidental: the function in the workbook acts as a wrapper for the one in the Add-In.

In the workbook, on one of the sheets I have (these are just a sample):

Cell Function
[A1] =MyUDF("String")
[A2] =MyUDF("String", 0)
[A3] =MyUDF("String", 1, TRUE, FALSE, 200)

In the workbook, MyUDF has 2 parameters:

Public Function MyUDF(ByVal p1 As Variant, ParamArray p2 As Variant) As Variant

The ParamArray p2 As Variant allows me to specify any number of additional parameters to my workbook function, and transforms it into an Array of values (this is what I want). This workbook function finally calls the Add-In equivalent as per:

vResult = Application.Run("MyAddIn.MyUDF", p1, p2)

The UDF in the Add-In has a small but determining difference with the one in the workbook, and that is the number and type of parameter declaration. The function in the Add-In also has 2 parameters but these are defined as:

Public Function MyUDF(ByVal p1 As Variant, Optional ByVal p2 As Variant) As Variant

By design, the Optional ByVal p2 as Variant is processed inside the UDF as an Array of values, which is how it passed by the UDF in the workbook.

In general this works fine, and when I debug I see that control goes first to the workbook UDF and then to the Add-In UDF. So what’s the deal, isn’t Excel designed to do this? Yes, if only…

The Problem

…if only it did not show this when I opened the workbook:

Cell Function Result (sample)
[A1] =MyUDF("String") 25
[A2] =MyUDF("String", 0) 25
[A3] =MyUDF("String", 1, TRUE, FALSE, 200) #VALUE

I was surprised to see the #VALUE but I also saw it rapidly disappear after I refreshed the workbook, so for me it was just a matter of refreshing the data and blamed Excel for it also because I saw sound values in the other cells. Until a customer reported it to me as a bug and I gave it some extra question time to finally discover why it was returning #VALUE and why only for those cells where I used 3 or more parameter values.

This is what I discovered:

  • If you have a workbook containing a UDF, that you have used in your cells and that you have embedded in the VBA code, then Excel will NOT use it when you open the workbook.
  • If you have a module loaded in Excel that provides the namely equal UDF, Excel WILL use that UDF when you open the workbook

  • When you refresh the workbook/worksheets, Excel will use the UDF embedded in the workbook

The Technical Explanation

Using my examples above, in the UDF in the workbook the parameters are a variant value (p1) and a variant array of values (p2). In the UDF in the Add-In the first parameter (p1) is the same but the second parameter (p2) is also a variant value.

When the workbook’s UDF is triggered (situation #3, second image), it forwards the call to the namely equal UDF in the Add-In passing the same number of parameters which do not change, and the Add-In receives a variant value and a variant array of values.

However, if the UDF in the Add-In is called directly (by Excel when opening the workbook—situation #2, first image) then the first parameter is passed as a variant value, the second parameter is passed as a variant value instead of a variant array of values, and from the third parameter onwards they are discarded. This caused the UDF in the Add-In to fail and that was the reason for the #VALUE.

Presumably, when you open a workbook Excel first loads the worksheets and evaluates the cell formulas but it is yet unaware of any UDF in the code modules that the cells may refer to. Only at a later stage these will become active but at that point no recalculation is performed.

The Solution

What lesson have I learned? Well, it’s simple: never trust Excel.

For the rest, make sure the name for a UDF in your workbook’s code modules is unique and cannot be confused with any other [public] name provided by any other module loaded in Excel’s memory (Add-In, XLL, etc.).

In the desperate case you do want the same names and you want to use the UDF in the workbook as a wrapper for a UDF in your Add-In, make sure the parameters match exactly —OR— make sure the either call processes the input parameters correctly.

Dutch

Create a free website or blog at WordPress.com.