Excel VBA, RowHeight, fail function(), OK sub()

Question

How does one set Excel RowHeight in VBA function()? Examples found on web so far were in VBA sub().

Thanks for guidance. Ray

----- background (the aim of the testing)

The task aims to parametrically resize RowHeight for dynamically generated QR codes in column A. There is no hard constant of QR size in VBA code since items to which QR codes would attach vary in sizes.

Two images of the Excel worksheets are attached.
Worksheet qr.txt contains the source text. Worksheet qr.img contains the end results. Cells A3 and A4 are anchor cells for QR code images. Cell A3 RowHeight was manually set. Cell A4 RowHeight was not set (RowHeight setting in VBA function() failed). Cells A5 and A6 are empty at present.

Worksheet qr.txt

Worksheet qr.img

----- in worksheet (testing)

A2 → = rr_hite_func( $B2 ) B2 → 50

----- in VBA module (testing)

Option Explicit


' RowHeight, OK in sub()
Public Sub rr_hite_sub()
Rows(2).RowHeight = 50
End Sub


' RowHeight, fail in function()
Public Function rr_hite_func(ByVal rr_hgt As Double) As Double
Dim now_cel As Range
Dim now_sht As Worksheet

Set now_cel = Application.ThisCell
Set now_sht = now_cel.Worksheet

' none of below worked
now_cel.RowHeight = rr_hgt
now_sht.Rows(2).RowHeight = rr_hgt
Rows(2).RowHeight = rr_hgt   ' same code as sub()

Set now_cel = Nothing
Set now_sht = Nothing

rr_hite_func = rr_hgt
End Function

Windows XP Pro, Service Pack 3, Excel 2007. Windows 10 Pro, Excel 2010.

Answer 1

The problem you have is not related to functions, but to functions that are used as UDF (=User defined functions).

An UDF is used to calculate something and works like any function that is provided by Excel (like sin, SumIf or VLookup). Such a function doesn't modify anything in your worksheet (neither content nor formatting), and it is not allowed to do so. There are some (dirty) workarounds (see the link Storax provided in the comments), but you should avoid it.

What you need is that a piece of code is executed when you change the value of the content of a specific cell, eg B2. This can be done using the Event routine Change. Put the following code into the Worksheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    
    Dim height As Double
    height = Target(1).Value
    rr_hite_sub height
    
End Sub

Public Sub rr_hite_sub(height As Double)
    If height <= 0 Then Exit Sub
    ActiveSheet.Range("2:3").RowHeight = height
End Sub

The routine rr_hite_sub can be in any module, but the Event-routine needs to be in the Worksheet module, else Excel cannot trigger it. Instead of a Sub, it could also be a function (but makes not much sense here).

Just change it so that it sets the height to the rows you really want. You could, for example change it so that it sets the height to all rows that contain an image in column A.

Public Sub rr_hite_sub(height As Double)
    If height <= 0 Then Exit Sub
    
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        If sh.TopLeftCell.Column = 1 Then
            sh.TopLeftCell.EntireRow.RowHeight = height
        End If
    Next
End Sub