0

I am writing a C# DLL which must be COM visible as it's main use case will be embedded into Microsoft Excel VBA (I know). Whilst developing I have been using Visual Studio's "Register for COM Interop" checkbox and having the process done for me. This has been working fine on my PC as I am able to get valid outputs in Excel, however now I am trying to register the DLL on client machines (manually) and I seem to be having issues with making valid calls as Excel only returns !VALUE.

This following has been my process:

AssemblyInfo.cs:

[assembly: ComVisible(true)]

Class1.cs:

namespace ManualRegister
{
    public class Class1
    {
        public string test()
        {
            return "Working!";
        }
    }
}

Copy ManualRegister.dll into SysWOW64

Register.bat:

cd/
C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe ManualRegister.dll /tlb:ManualRegister.tlb /codebase
pause

Adding reference to VBA module.

Adding VBA reference

VBA Code:

Function test()
    Dim object As New ManualRegister.Class1
    test = object.test()
End Function

VBA error:

VBA error

Can anyone see what step I'm missing / doing wrong?

(I have also tried specific CPU architecture builds to no avail)

Eog
  • 102
  • 11
  • It's showing `#VALUE!` not because that's what's returned, if you look at the top you can see the cell contains `=test()`, it says `#VALUE!` because of formatting/ a formula applied to the cell and `=test()` is invalid for whatever it is – MindSwipe Oct 20 '22 at 11:43
  • @MindSwipe I am aware the DLL is not returning #VALUE! I'm trying to figure out what's going wrong in the process such that VBA cannot make valid calls to my DLL. That exact VBA works in my actual project which I have registered by Visual Studio – Eog Oct 20 '22 at 11:46
  • What does the message say when clicking the warning triangle? – Olivier Jacot-Descombes Oct 20 '22 at 11:47
  • @OlivierJacot-Descombes "A value used in the formula is of the wrong data type." Not very helpful sadly – Eog Oct 20 '22 at 11:49
  • Strongly type your VBA function `Function test() As String` and make sure the cell is formatted either as "General" or "Text". – Olivier Jacot-Descombes Oct 20 '22 at 11:53
  • Your life will be a lot easier if you write your activex dll in twinBasic. Its the up and coming drop in replacement for VBA which can run VBA 'as is' but is a much more modern take on VBA. One of the examples you can create as a starter project is for an activex dll. https://twinbasic.com/preview.html – freeflow Oct 20 '22 at 12:23
  • @freeflow As good as that sounds I am unfortunately completely tied to Microsoft Excel for reasons to do with my employer – Eog Oct 20 '22 at 12:33
  • Are you calling test() from a VBA module? Check this for more details: https://stackoverflow.com/a/22736330/403671 Otherwise make sure Excel is running with the same bitness as the the registry where the COM object is registered. From the question you register in x86 so you must run Excel as x86. – Simon Mourier Oct 20 '22 at 13:29
  • @SimonMourier Thanks for the reply Simon, Yes I was calling test() from a VBA module. How do you know I register in x86? and how do I register in x64? (my excel is x64) – Eog Oct 20 '22 at 14:30
  • 2
    C:\Windows\Microsoft.NET\Framework\v4.0.30319 points to the x86 framework. For x64 you want C:\Windows\Microsoft.NET\Framework64\v4.0.30319. Make sure you compile for x64 (or better Any CPU so you can use both Excel x64 and Excel x86 but in this case register in both frameworks) and don't use SysWow64 but regular System32 for the type library. – Simon Mourier Oct 20 '22 at 15:47

0 Answers0