to use variables from macro of personal.xlsb

Have you tried this?

Public Sub counter(ByRef fr, ByRef fc, ByRef lr, ByRef lc)

OK, I've checked and ByRef doesn't work. You have a few options as far as I can see:

1. (easiest and most sensible) Copy the sub "counter" into the active workbook
2. Rename your VBAProject in the active workbook (e.g. proj1), set up a reference in personal.xlsb (Tools > References), and change the values directly using the reference (e.g. proj1.fr = proj1.fr + 1)
3. Create a function instead of a sub to output a new value, e.g.

Function ctr(vinput) As Variant
    ctr = vinput + 1
End Function

    fr = 1
    fr = Application.Run("personal.xlsb!ctr", fr)
    Debug.Print fr

Originally Posted by Stormin'

OK, I've checked and ByRef doesn't work. You have a few options as far as I can see:

It will work as long as you use a late bound variable to hold the Application instance (i.e. a Variant or Object type). So this will work

    Dim fr, fc, lr, lc
    Dim app
    Set app = Application
    app.Run "Personal.xlsb!counter", fr, fc, lr, lc

whereas this will not

    Dim fr, fc, lr, lc

    Application.Run "Personal.xlsb!counter", fr, fc, lr, lc

The example I gave above works. Could you post your full code, with [CODE][/CODE] tags around it?

Please use [CODE][/CODE] tags around any code you post. It's part of the forum rules.

If you're going to be writing code in the target workbook you might as well copy the "counter" code in there too. It's by far the easiest option and means that if someone else uses the target workbook it won't break.

However, it is possible to use the code in personal.xlsb to fill the values from the target workbook by outputting into an array. I mocked something up for you.

In target workbook:

Sub test()
    
    Dim fc0 As Long
    Dim lr  As Long, lc As Long
    Dim fr  As Long, fc As Long
    Dim arr As Variant
    
    fc0 = 0
    
    arr = Application.Run("personal.xlsb!counter", fc0)
    
    fr = arr(0)
    lr = arr(1)
    fc = arr(2)
    lc = arr(3)
    
    Debug.Print "fr:", fr
    Debug.Print "lr:", lr
    Debug.Print "fc:", fc
    Debug.Print "lc:", lc
    
End Sub

In personal.xlsb (I repaired some less-than-ideal coding as well):

Function counter(Optional fc0 As Long) As Variant
    
    Dim ws As Worksheet
    Dim rng As Range
    
    Dim lr  As Long, lr1 As Long, lr2 As Long
    Dim fr  As Long, fr1 As Long, fr2 As Long
    Dim lc  As Long, lc1 As Long, lc2 As Long
    Dim fc  As Long, fc1 As Long, fc2 As Long
    Dim i   As Long
    
    Set ws = ActiveSheet
    Set rng = ws.Range( _
        ws.Cells(1, 1), _
        ws.UsedRange.Cells(ws.UsedRange.CountLarge))
        
    Debug.Print "Workbook", ws.Parent.Name
    Debug.Print "Range", rng.Address(0, 0)
    
    lr2 = 1
    fr2 = 1
    lc2 = 1
    fc2 = 1
    
    If Not (fc0 >= 1 And fc0 <= rng.Columns.Count) Then
        For i = 1 To rng.Columns.Count
            lr1 = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
            If lr1 = 1 Then
                fr1 = 1
            Else
                fr1 = ws.Columns(i).Find("*", ws.Cells(ws.Rows.Count, i)).Row
            End If
            lc1 = ws.Cells(fr1, ws.Columns.Count).End(xlToLeft).Column
            If lc1 = 1 Then
                fc1 = 1
            Else
                fc1 = ws.Rows(fr1).Find("*", ws.Cells(fr1, ws.Columns.Count)).Column
            End If
            With Application.WorksheetFunction
                lr = .Max(lr1, lr2) 'IIf(lr2 > lr1, lr2, lr1)
                lr2 = lr
                fr = .Min(fr1, fr2) 'IIf(fr2 > fr1, fr1, fr2)
                fr2 = fr
                lc = .Max(lc1, lc2) 'IIf(lc2 > lc1, lc2, lc1)
                lc2 = lc
                fc = .Min(fc1, fc2) 'IIf(fc2 > fc1, fc1, fc2)
                fc2 = fc
            End With
        Next i
    Else
        fc = fc0
        lr = ws.Cells(ws.Rows.Count, fc).End(xlUp).Row
        If lr = 1 Then
            fr = 1
        Else
            fr = ws.Columns(fc).Find("*", ws.Cells(ws.Rows.Count, fc)).Row
        End If
        lc = ws.Cells(fr, ws.Columns.Count).End(xlToLeft).Column
    End If
    
    counter = Array(fr, lr, fc, lc)
    
    Set ws = Nothing
    Set rng = Nothing

End Function

I hope this helps.

Happy to help

---

If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
Thanks!

Ooo, thanks xlnitwit, really handy to know