0

I'm trying to resize a table using VBA using this code:

Function MirrorTable()
    Dim testTbl As ListObject
    Set testTbl = Sheets("Testing").ListObjects(1)
    
    MsgBox testTbl.Range.Address
    
    testTbl.Resize testTbl.Range.Resize(15)
End Function

If I execute this function by writing =MirrorTable() into a cell the message box is displayed correctly, but then I get an #VALUE in the cell and the table is not resized. What am I doing wrong here?

s1624210
  • 627
  • 4
  • 11
  • 1
    [A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel.](https://support.microsoft.com/en-us/kb/170787) The statement is [not entirely true](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) though but one should avoid changing the environment of Microsoft Excel using a UDF. – Siddharth Rout Jan 28 '21 at 13:16
  • @SiddharthRout Thank you! How would recommend me to call my function? – s1624210 Jan 28 '21 at 13:25
  • Create a proc in a module and then run the macro from the developer tab? You can also insert a form control in the worksheet and run the macro from there? – Siddharth Rout Jan 28 '21 at 13:28

0 Answers0