0

So I have this DLL code here...

Public Class Algebra
    Public Function Addition(ByVal x As Long, ByVal y As Long)
        Dim mysum As Long

        mysum = x + y

        Return mysum
    End Function
End Class

And my Excel VBA is ...

Public Sub CommandButton1_Click()
    Dim myAlgebra As Algebra
    Set myAlgebra = New Algebra

    Dim mysum As Long

    mysum = myAlgebra.Addition(4, 4)

    MsgBox ("HELLO WORLD " & mysum)
End Sub

Now all this works fine.

My next task is to edit a value of a Cell, like say Cells(8,1).Value = "HELLO", using the DLL.

I tried inserting-

ThisWorkbook.Sheets(1).Cells(10, 1).Value = "HELLO"

inside the Function but it just throws a syntax error. I guess I need to create an object that would contain Excel Workbook? Tho Im not sure how to do that....

EDIT: As advised by GSerg, here's my modifications..

For DLL Side...

Public Function EditCell(ByRef myWB As Object)
    myWB = "HELLO"
End Function

For VBA...

Set myrange = ThisWorkbook.Sheets(1).Range("A10")
myAlgebra.EditCell (myrange)

So this runs without errors but A10 cell was not populated...

EDIT#2: Question answered. I've posted an answer below. Thanks to all that helped!

HanSoloQue
  • 19
  • 4
  • Not sure what "Hello" has to do with algebra and addition - what is the context, what are you trying to do? Why not set it up as a UDF? – AJD Aug 16 '18 at 07:01
  • Have your DLL functions accept a `Range` and pass it as a parameter. You can use late binding (`As Object`) on the .NET side. If you don't like that, you will need to make your library a [VSTO addin](https://learn.microsoft.com/en-us/visualstudio/vsto/create-vsto-add-ins-for-office-by-using-visual-studio). – GSerg Aug 16 '18 at 07:08
  • @AJD What is UDF? And yeah sorry about the quick jump of subject (from algebra to editing cells). I'm actually trying to migrate a large chunk of VBA code into a DLL and currently experimenting what I could move over and what I can't. – HanSoloQue Aug 16 '18 at 07:20
  • @GSerg Hi, thanks for the advice. I tried doing what you said. It runs without errors but the cell was not populated by a value. I will edit my original post about what I did. Thanks for your time. – HanSoloQue Aug 16 '18 at 10:07
  • [Remove the parentheses](https://stackoverflow.com/a/8257503/11683) from `myAlgebra.EditCell (myrange)`. And even though that is not causing you a problem currently, declare the .NET side as `ByVal myWB As Object`. – GSerg Aug 16 '18 at 10:31
  • @GSerg the () could be causing a problem because it would be passing a value (cell contents) rather than an object (range). – AJD Aug 16 '18 at 20:46
  • @HanSoloQue: UDF - User Defined Function in Excel. Basically, just set it up as a function that returns the string you want in the cell. Rather than trying to manipulate the cell in the DLL, you use the VBA to put the return value into the cell. This is loose coupling and removing co-dependencies. – AJD Aug 16 '18 at 20:49
  • @HanSoloQue Also, going back to the example you have, cast the object to a Range, and then set the value otherwise you are using implicit casting and the DLL has to guess what you mean. `Option Strict` and `Option Explicit` are your friends. – AJD Aug 16 '18 at 20:53
  • @GSerg Hi Thank you! Question answered. EDIT: And thanks for your time! – HanSoloQue Aug 17 '18 at 06:05
  • @AJD Thanks for your time! Question answered! – HanSoloQue Aug 17 '18 at 06:06

1 Answers1

0

Question answered. Thanks to GSerg and AJD. Here's the final code.

DLL side

Public Function EditCell(ByVal myWB As Object)

    Dim myRange As Range

    myRange = CType(myWB, Range)

    myRange.Value = "HELLO"

End Function

VBA Side

Set myrange = ActiveWorkbook.Sheets(1).Range("A10")

myAlgebra.EditCell myrange

Additional step taken: - Add reference to Excel in Visual Studio to utilize Range Data type

Also, reviewing the code, it seems it would be simpler to make it a Sub instead of a Function since it's not returning anything.

HanSoloQue
  • 19
  • 4