VB HTML Maker

[Juan Pablo González]

As you know, there is a new -free- addin available to download, the VB HTML Maker.

Its a simple xla, that will convert your VB code into HTML (Or BB) code so you can put it in this (And other) message boards.

There are two things that are bothering me, but time has not allowed me to dig more into them.

One. Comments...

The comment indicator is a good mark to notice when one comment starts, but that's not always the case, for example:

<font face=Courier New>
<SPAN style="color:darkblue">Sub</SPAN> Test()
    <SPAN style="color:green">'This will display the ASCII code</SPAN>
    <SPAN style="color:green">'of the comment indicator</SPAN>
    <SPAN style="color:darkblue">Debug</SPAN>.<SPAN style="color:darkblue">Print</SPAN> "The code is: " & Asc("<SPAN style="color:green">'") & ", enjoy !"</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

The Asc("'") has a comment indicator, BUT should NOT be marked as a comment.

Two. Reserved words.

<font face=Courier New>
<SPAN style="color:darkblue">Sub</SPAN> Test2()
    <SPAN style="color:darkblue">If</SPAN> MsgBox("Please <SPAN style="color:darkblue">Select</SPAN> the correct one !", vbOKCancel, "Error") = vbCancel <SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

In this case, the Select shouldn't be treated as a reserved word, because it is part of a string.

The code in the program is NOT password protected, so if you feel like modifying it, improving it, fixing it, etc., please, FEEL FREE TO DO IT, and let's see if we can have a better AddIn for everyone.

Post your solutions/comments in here.

 

[Todd Bardoni]

Juan,
Quick question. I don't know where I'm supposed to put this once I download it. I mean, its an add-in, right? Where do VBA add-ins go?

WinXp & OfficeXP

 

[Juan Pablo González]

Load it the same way as any other AddIn. Tools | AddIns in the Excel menu bar. However, the controls will be added to the VBE commandbars.

 

[Todd Bardoni]

Thanks. I thought it would go in a different spot.

 

[Todd Bardoni]

Hi Juan,
I have two problems.

1. Regarding the VB HTML MAKER, I have it checked in my AddIn List, but don't see the menu in the VBE. I'm also getting an error msg when I start Excel that says "Access to the VB Project is not trusted." Any ideas?

2. Regarding the HTML Maker for spreadsheets, this too I have loaded, but everytime I load Excel, I get an error message saying, "Error Number:=91; Error Discrp:= Object variable or With Block variable not set; You may have to manually set a reference to the [VBIDE = VBE6EXT.OLB]

Any ideas??

 

[Juan Pablo González]

When you go to the VBE, select either the HTML Maker or the VB HTML Maker, and go to Tools | References. Do you see any marked as MISSING ?

 

[dk]

The Asc("'") has a comment indicator, BUT should NOT be marked as a comment.

Two. Reserved words.

<font face=Courier New>
<SPAN style="color:darkblue">Sub</SPAN> Test2()
    <SPAN style="color:darkblue">If</SPAN> MsgBox("Please <SPAN style="color:darkblue">Select</SPAN> the correct one !", vbOKCancel, "Error") = vbCancel <SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

In this case, the Select shouldn't be treated as a reserved word, because it is part of a string.

Juan,

An idea for both the apostrophes within a string and the reserved (blue) words in a string. I think I'm correct in saying that you should be able to identify if any character is within a string by counting the number of " to the left of the specified character. If the number is odd, then you know that the current character is part of a string. It may be a bit tricker when you have a line of code spread over more than one line in the VBE. You could use the same technique to get around the Asc("'") problem.

I haven't had a good look at the code yet so can't yet write the code to do this. However, if I get a spare couple of hours tomorrow I'll give it a go.

Bye,

Dan

EDIT : I forgot to mention. If anyone can see a flaw in my logic then please let me know. I'm going on the assumption that " must always be used in pairs.

 

[Juan Pablo González]

Hey Dan,

Yes, that is the basic approach. And you don't have to worry about "splitted" lines, I found a solution for that... You can see it at the top, where I replace " _" & vbNewLine with a strange character, so every line is ALWAYS one single line...

 

[Juan Pablo González]

Ok, this should be the hardest (I think... :P ), if we can get this one working, it's all ok...

<font face=Courier New>
<SPAN style="color:darkblue">Function</SPAN> Test(Ans <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Boolean</SPAN>
    <SPAN style="color:darkblue">If</SPAN> Ans > 2 <SPAN style="color:darkblue">Then</SPAN>
        MsgBox "Please <SPAN style="color:darkblue">Select</SPAN> either <SPAN style="color:green">'1' or '2' from the list", vbCritical  'This forces a good Select</SPAN>
    <SPAN style="color:darkblue">Else</SPAN>
        Test = <SPAN style="color:darkblue">True</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN>
</FONT>

 

[Todd Bardoni]

Hi, Juan. I've been trying to get these to work. No luck yet. I went through the list and nothing is marked as missing. Actrually, though, I can't even get the VB HTML Maker to load. I get a message at Excel startup that says, "Access to the VB Project is not trusted".

As for the Spreadsheet HTML Maker, it seems to work, but I just get that error message at startup.

I'm going to keep working on it, but any ideas will be welcomed.

Todd