Using UDF in VBA with arrays

[Vonsteiner]

Hello everyone,

I am having some trouble using a UDF in my VBA code. I am attempting to enter a UDF into a cell within my workbook. Hopefully it is an easy fix. I am not sure if I have entered it into VBA correctly. Here are some snippets of the code I am trying. In the following I am entering some formulas in D and E and attempting to enter the UDFs 'Direction' and 'Street_Name' into F & G. The formulas in D & E work fine, but the UDF lines don't enter anything into the worksheet at all.

Range("D2").Select
ActiveCell.FormulaR1C1 = "=TRIM(LEFT(RC[-2],FIND("":"",RC[-2])-1))"
Range("D2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
Range("B2").Select
Selection.End(xlDown).Select
Selection.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=TRIM(LEFT(RC[-1],FIND("" "",RC[-1])-1))"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Range("E1").Value = "House Number"
    
Range("F2").Select
Selection.FormulaArray = "=Direction(RC[-2])"
    Selection.Copy
    Range("F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
Range("G2").Select
Selection.FormulaArray = "=Street_Name(RC[-3])"
    Selection.Copy
    Range("G3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

I have more code to show if needed, but it is mostly the same as this. Any help is greatly appreciated. Thank you.

 

[GWteB]

UDF's aren't suited for what you are trying to do. You might take a look over here...

Description of limitations of custom functions in Excel - Microsoft Support

Lists the functionality limitations of user-defined functions in Excel. These functions are called by a formula in a worksheet cell and cannot change the environment of Excel. You must use the Visual Basic subroutine to change the environment.

support.microsoft.com

 

[DanteAmor]

I did a test and it works for me.
You can put here the code of the Direction and Street_Name UDFs
By the way, if you are already running a macro to put a UDF, it would be better if the same macro got the results that the UDFs get.


You could explain the following code, because if there is no data in column D the formula is copied to ALL column

Range("D2").Select
ActiveCell.FormulaR1C1 = "=TRIM(LEFT(RC[-2],FIND("":"",RC[-2])-1))"
Range("D2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False