Assigning excel function to a variable in Excel VBA

user_14463623 0
Viewed 233

I am trying to assign am excel function to a VBA variable. I have tried a few different data types and none of them seem to work. I am trying to count the columns and rows in this table "tblAdsorpsionColumn" and I dont want to type out "Application.WorksheetFunction" every time I use an excel function in VBA. I know there other ways to do this but I want to use this way to learn how to use this functionality later. (what doesn't work: Worksheet,Double,String,Object)

Private Sub UserForm_Initialize()
    Dim App As Object
    Dim Table As Range

    Set App = Application.WorksheetFunction
    Set Table = ThisWorkbook.Worksheets("Sheet1").Range("tblAdsorpsionColumn")

    lstTable.ColumnCount = App.Columns(Table)
End Sub
vba excel
2 Answers

Say you use the SUM() worksheet function frequently in VBA and don't want to repeat typing Application.WorksheetFunction constantly. Just make a VBA wrapper like:

Option Explicit

Public Function summ(rng As Range) As Variant
    With Application.WorksheetFunction
        summ = .Sum(rng)
    End With
End Function

You will get a very small performance hit using it, but it may be tolerable.

user_24746560

You can use a function to "shortcut" Application.WorksheetFunction :

Sub tester()
    Debug.Print wsf.Average(Selection), wsf.Sum(Selection)
End Sub

Function wsf() As Object
    Set wsf = Application.WorksheetFunction
End Function
user_4788840
Related