How to create a function to accept concatenated source value - Excel VBA

Question

I'm trying to create a vba function to replace multiple characters from cell value. I'm calling this function 'TestFunction' for now. The purpose of the function is to parse illegal characters from the data to be able to use it and the original data needs to remain in their own columns after parse.

I have pasted my code and table as a reference.

To use the function I would like to use =TestFunction(...) on a helper column on the same row as the data is in. The code works for a single reference e.g. =TestFunction(A1), but when trying to pass concatenated reference e.g. =TestFunction(A1&A2) it returns #VALUE!.

I've tried to fix this for a while with no success. I could make another helper column where the concatenated data is but I would really love to get this function to work without additional column.

Anyone know if this is possible to achieve?

Reference code:

Function TestFunction(CellContents As Range) As String
  Dim CellTextReplaced As String
  Dim char As Variant
  ' Characters to be replaced with "|" as delimiter
  Const SpecialCharacters As String = ".|,|!| |/|\|+|-|@|&"

  ' Replace all special characters
  CellTextReplaced = CellContents.Value
    For Each char In Split(SpecialCharacters, "|")
      CellTextReplaced = Replace(CellTextReplaced, char, "")
    Next
  ' Output

  TestFunction = CellTextReplaced
End Function

Reference table:

  |   A    |    B     |        C           |          D           |
-------------------------------------------------------------------
1 | .test  |   .test  |  =TestFunction(A1) | =TestFunction(A1&B1) |
2 | ,test  |   ,test  |  =TestFunction(A2) | =TestFunction(A2&B2) |
3 | test-  |   test-  |  =TestFunction(A3) | =TestFunction(A3&B3) |
4 | /test\ |   /test\ |  =TestFunction(A4) | =TestFunction(A4&B4) |

Change the UDF() to accept a String rather than a Range. — Gary's Student
– Gary's Student, Commented Mar 15, 2019 at 10:56

Pᴇʜ · Accepted Answer · 2019-03-15 11:09:41Z

3

The issue is that your function TestFunction(CellContents As Range) was waiting for a Range but A1&B1 actually is a String because the concatenation with & casts the values of the two ranges A1 and B1 into a string.

I suggest the following improvements:

Option Explicit

Public Function TestFunction(ByVal CellContents As String) As String
    Const SpecialCharacters As String = ".,! /\+-@&"

    Dim iChar As Long
    For iChar = 1 To Len(SpecialCharacters)
        CellContents = Replace$(CellContents, Mid$(SpecialCharacters, iChar, 1), "")
    Next iChar

    TestFunction = CellContents
End Function

edited Mar 15, 2019 at 11:09

answered Mar 15, 2019 at 11:03

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a function to accept concatenated source value - Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related