How do you do this in VBA ?

[QNJob]

The good news is, Percentage format already takes care of this. Try this: go to a blank cell with no number in it and format it as Percentage. Then, type in 9. It will automatically become 9%.

The bad news is that it works when you manually enter 9 into the cell. When you use VBA to populate that cell with value 9, it will be 900%.

Try this, pre-format cell A1 as percentage. Enter 9, it will show 9%. Now clear that cell and run this module

Sub test()
Worksheets("Sheet1").Range("A1").Value = "9"
End Sub

A1 will show as 900% :D

Maybe excel treats manual and vba generated input differently but it's interesting to know.

 

[charlesdwright]

The attached add-in will put up a two-button toolbar. The button on the left will prompt the user to enter a password in A1. The button on the right will "accept" the password (close the routine).

** This is NOT production code, for the following reasons:

a) In order to do this, you have to get into the keycodes. The nature of the password would be defined in advance and the code designed to handle all exceptions. This takes time. Time is money. We're all busy, aren't we?

b) This is just my way of saying, "there is always a way."

c) as others have already said, using a windows form allows for much greater flexibility and ease of key trapping, as well as for bullet-proofing the user interface. if you really wanted to do this, that would be the way to go. Forms are efficient.

Oh, I see I can't send an addin. So it's an .xls. It will load the buttons on opening, remove them on closing.

It has to be downloaded to run. Attempting to run it by clicking "open" on the download menu will generate errors. Download first, then open.

***

Just discovered this link. It looks like one of the better central vba/excel links I've seen. Colo's Excel Junk Room - Excel VBA Tips and Help Forum

 

[QNJob]

Anyone knows how to create Excel help documentation. when you use Function wizard, at the bottom left, there is "Help on this function". If you click on it, it will show details on how to use it.
When I create custom excel function, I like to provide some document on it. I googled a bit but haven't found any guide even on MSDN.

 

[moudesluys]

Hi,

Did you ever find out how to do this? I have a need to add help to my user defined functions as well, and I can't find any information on the web or in the Excel help documentation.

I'm very interested in figuring this out.

 

[Andy Nguyen]

Well, I got the rough idea of how to do it but haven't implemented it yet

[FONT=Verdana, Arial, Helvetica]To write the help file, you should use CHM instead of WinHelp (.hlp) which is outdated.

CHM is just a kind of archive containing HTML, images, and other files.
To create documentation in CHM format, you first need to convert it to HTML format. Then, you can use the HTML Help Compiler which converts your HTML and image files into CHM. The Help Compiler is available as a part of the "HTML Help Workshop" available for free from Microsoft. There are also many 3rd party tools making it much easier to create CHM files.

To link your UDF to CHM, you need to specify the help file name and the help topic number (separated by "!") when registering the UDF (I assume you use Excel() with xlfRegister). The help topic number is an integer number which is associated with some help file topic (which is just a separate HTML file inside the CHM "archive").

Thus, you need to create a separate help topic in a CHM file for each UDF your XLL exports, then assign a unique integer number to each topic, and specify the appropriate numbers when registering the functions inside the XLL.
There are many tutorials on the Internet describing how to create CHM files. To help you get started, I will just mention that you need to create the [ALIAS] and [MAP] sections in your HHP (HTML Help Project) file, for example:

---- MYHELP.HHP ----
[FILES]
index.html
myfunc1.html
myfunc2.html
myfunc3.html

[ALIAS]
IDH_topic_1 = myfunc1.html

[MAP]
#define IDH_topic_1 1001
-------------------------

When registering myfunc1 in your XLL, you should specify "MYHELP.CHM!1001". Unfortunately, neither can you specify the function title ("MYHELP.CHM!MyFuncTitle") nor the HTML file ("MYHELP.CHM!myfunc1.html") directly... at least I haven't been able to make this work. It appears that you have to use integer numbers and map them to HTML files. If you find a way to use strings instead of numbers, please let me know.
[/FONT]