Josje

Hi,

Previously I asked how to speed up and generalise a routine I wrote in VBA (and VBS and XAL( a darker portion of Microsoft our company works with)). Mainly the routine is to pass the next character substring from another character string using a string containing given delimiter characters like space, comma, semicolon, quote, double qoute, carrage return, linefeed etc. I got the answer to write a .dll in C++.

I successfully made a .dll in C++ passing integers to and from VBA fine, but no way I can find a way (I have tried several things,but VBA always crashes) or example of passing a string from C++ and VBA and vice versa. I confess I am C ignorant, but I am trying (I am Dutch so excuse my English). Even how a character string is supposed to represented in C(++) is an enigma to me this moment (I see Unicode character strings and Ascii/Ansi character strings discussions, but this seem irrelavent in VBA)

My aim would be:

VBA pseudo:

Declare Function ... "NextWord" ....

......

Dim Word as String * 255

Const Delim = " ,;'""" & vbCr & vbLf

Dim strParams as String

strParams = ..... (some string)

Word = String(Char(0),255) ' Reset Word To 255 Char(0)

Word = NextWord(strParams, Delim) 'Read first Word

Do While Word <> ""

.... process Word (first, next or last)

Word = String(Char(0),255) ' Reset Word To 255 Char(0)

Word = NextWord 'Read next Word

Loop

Can somebody point me in the right direction

Would be very gratefull.



Re: Visual C++ General passing a string from/to vba/c++ dll

Bite Qiu - MSFT

I have limited knowledge about VBA, so what I am going to say maybe a complete perversion, please forgive me if I am wrong. What I am thinking of is: using a c++ dll in VBA is similar to calling win32 API from VBA, which you have to deal with the string passing issue, so I think the article "office VBA and the windows APIs" may help you, furthermore, there is a section called "Returning Strings from DLL Functions" in the article seems addressing your problem.

hope it makes sense

rico






Re: Visual C++ General passing a string from/to vba/c++ dll

Josje

Hi, Rico,

I am so sorry, I did not get my problem clear. I have some knowledge of VBA/VBS passing parameters to from API's (the article you refer to is known to me (is that proper English I am Dutch), so my problem is not at the VBA/VBS side, but C++, where I am a novice.

My problem is simply to code my own DLL in C++. I want to translate the NextWord function (example in VBS included) in C++ and make a DLL from that. In examples I could find ways to pass integers to/from C++ DLL's, but in this case two character strings must be passed (Text and Delim) to the DLL and one character string from the DLL.

That was the point I got stuck. So my question would be how to define the character string (pointer)(s) in C++ so the VBA/VBS can handle them.

VBS example:

Function NextWord (Text, Delim)

Dim StartPos

Dim I, J, L

Dim D, T

' StartPos

If IsEmpty(Text) Then

StartPos = 1

End If

If StartPos > Len(Text) Then

Exit Function

End If

L = Len(Text)

' Skip blanks

For I = StartPos To L

If Mid(Text, I, 1) <> " " Then

Exit For

End If

Next

' Delim defined

If IsEmpty(Delim) Then

Delim = "'"",; "

End If

' Search for Delim in Text

For StartPos = I To L

D = Mid(Text, StartPos, 1)

If InStr(1, Delim, D) > 0 Then

Exit For

Else

NextWord = NextWord & D

End If

Next

If StartPos > L Then

Exit Function

End If

' Literal

If D = """" or D = "'" Then

For J = StartPos + 1 To L

T = Mid(Text, J, 1)

If T = D Then

Exit For

Else

NextWord = NextWord & T

End If

Next

StartPos = J + 1

End If

StartPos = StartPos + 1

End Function





Re: Visual C++ General passing a string from/to vba/c++ dll

Bite Qiu - MSFT

Hi Josje,

Are you tring to have a c++ implementation of NextWord function If so, following code may help you:

Code Snippet

#include <iostream>
#include <string>
using namespace std;

string NextWord(string &Text,string Delim){
string tmp;
int index;

index = Text.find_first_of(Delim); // Find the position of first delimiter
if( index == -1 ) // Did not find the delimiter
{
tmp = Text; // Text is a single word
Text = ""; // Make Text a empty string
return tmp;
}
else if( index == 0 ){ // Has leading delimiters
int posDelim = Text.find_first_not_of(Delim);
Text.erase( 0, posDelim ); // Erase leading delimiter
index = Text.find_first_of(Delim); // Find the position of first delimiter again
}

tmp = Text.substr(0,index); // Get the string before the delimiter
Text.erase(0,index+1); // erase the delimiter and the string before it

return tmp;
}

int main(){
string Text = "passing a string from/to vba/c++ dll\nNext line";
string Delim = " /\n";
while( !Text.empty() )
cout << NextWord(Text, Delim) << endl;
}

hope it helps

rico







Re: Visual C++ General passing a string from/to vba/c++ dll

Simple Samples

There is one thing that can make things easier; probably more than one.

One thing you don't need help with is English; your English is as good as someone for whom English is thier native language. Okay, back to the C++ language.

As far as I know, it is not possible to call a "regular" DLL from VBS; for VBS you need to use a COM object (also called other things such as Automation). I am assuming you are developing a "regular" DLL.

Visual Basic (including VBA and VBS) uses the BSTR type internally. When VB calls a VC regular DLL, it will convert to and from C style strings when it thinks it needs to, and it usually guesses correctly. If you can use a BSTR instead then that can make things easier, but I forget the details of how to declare a function in a DLL in VB that uses a BSTR as parameters. There is a page in the documentation, but I forget whether it is in the VC or the VB documentation, about VB calling VC. You should check both. I think there is a relevant sample in the VB samples of a C regular DLL called from VB. If you search the MSDN for BSTR and another keyword such as VB (as in http://search.msdn.microsoft.com/search/Default.aspx query=bstr%20vba&brand=msdn&locale=en-us&refinement=00&lang=en-us) then you can get many articles and forum threads that could help. The following might also help.

How to: Convert Between Various String Types






Re: Visual C++ General passing a string from/to vba/c++ dll

Josje

Thanks very much, Rico (and Sam Hobbs,)

Indeed I am trying to translate the VBS (or VBA or XAL) routine NextWord to C++ (which I hope is faster).

So I think you pointed me in the right direction. I will let you know.....

Bye now, Josje