I want to be able to link a macro to an icon in excel so that I can run a function that I have in excel visual basic. I'm trying to use runcode to do this but it's not working. The code for the function is as follows:
Function ConvertCurrency ToUK(ByVal MyNumber)
Dim Temp
Dim Pounds, Pence
Dim DecimalPlace, count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumb er))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert pence
Temp = Left(Mid(MyNumb er, DecimalPlace + 1) & "00", 2)
Pence = ConvertTens(Tem p)
' Strip off pence from remainder to convert.
MyNumber = Trim(Left(MyNum ber, DecimalPlace - 1))
End If
count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English pounds.
Temp = ConvertHundreds (Right(MyNumber , 3))
If Temp <> "" Then Pounds = Temp & Place(count) & Pounds
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
count = count + 1
Loop
' Clean up pounds.
Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select
' Clean up Pence.
Select Case Pence
Case ""
Pence = " and No Pence"
Case "One"
Pence = " and One Penny"
Case Else
Pence = " and " & Pence & " Pence"
End Select
ConvertCurrency ToUK = Pounds & Pence
End Function
Private Function ConvertHundreds (ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Le ft(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid (MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mi d(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens(ByV al MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens , 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens , 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Ri ght(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(By Val MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
OK - so it works when I run it directly from the function list. How can I get this to run as a macro? I'm a complete novice when it comes to programming so you'll have to speak in words of one syllable so to speak!
1 2735
I want to be able to link a macro to an icon in excel so that I can run a function that I have in excel visual basic. I'm trying to use runcode to do this but it's not working. The code for the function is as follows:
Function ConvertCurrency ToUK(ByVal MyNumber)
Dim Temp
Dim Pounds, Pence
Dim DecimalPlace, count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumb er))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert pence
Temp = Left(Mid(MyNumb er, DecimalPlace + 1) & "00", 2)
Pence = ConvertTens(Tem p)
' Strip off pence from remainder to convert.
MyNumber = Trim(Left(MyNum ber, DecimalPlace - 1))
End If
count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English pounds.
Temp = ConvertHundreds (Right(MyNumber , 3))
If Temp <> "" Then Pounds = Temp & Place(count) & Pounds
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
count = count + 1
Loop
' Clean up pounds.
Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select
' Clean up Pence.
Select Case Pence
Case ""
Pence = " and No Pence"
Case "One"
Pence = " and One Penny"
Case Else
Pence = " and " & Pence & " Pence"
End Select
ConvertCurrency ToUK = Pounds & Pence
End Function
Private Function ConvertHundreds (ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Le ft(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid (MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mi d(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens(ByV al MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens , 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens , 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Ri ght(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(By Val MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
OK - so it works when I run it directly from the function list. How can I get this to run as a macro? I'm a complete novice when it comes to programming so you'll have to speak in words of one syllable so to speak!
hi
usually functions and/or subs will not start all by themselves and also in Exel, not by filling a cell.
So you need to create a button (right mousebutton over the menubar and select ' visual basic ')
from the next screen, select toolbox and designmode
then draw a button somewhere, give it a name like ' cmdConvert'
even may suppress the button getting printed... etc
after that doubleclick the button (still in designmode) to enter the code
have your function started from inside the created Private Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bill |
last post by:
I created a very simple DB with several forms and tables in chinese version
of Access XP running in Chinese Win XP. Now I try to run the same DB in
English version of Access XP running in English Win XP.
The DB opens just find excpet all buttons or event on froms I click or do it
gives me the following error.
______________
The expression On Click you entered as the event property setting produced
the following error: A problem...
|
by: kkrizl |
last post by:
I have an Access database that has a few huge tables. It was taking
about 20 minutes per table to import them from another application. I
used the upsize wizard to put them in a SQL server database. I created
a DTS package to import the files and it runs in less than 5 minutes.
I'd like to change my macro that used transfer text to import my files
to run my DTS package. I've seen several posts regarding this, but I
don't understand...
|
by: Newbie_sw2003 |
last post by:
Where should I use them?
I am giving you my understandings. Please correct me if I am wrong:
MACRO:
e.g.:#define ref-name 99
The code is substituted by the MACRO ref-name. So no overhead.
Execution is faster. Where will it be stotred?(Is it in bss/stack/?)
FUNCTION:
|
by: Yeounkun, Oh |
last post by:
Hello,
I want to know the name of function() in program.
In "gcc", I know that __FUNCTION__ macro is used for getting that.
but in "cc", __FUNCTION__ macro does not exist.
I can't find function to get the name of function running currently.
Could anybody help me?
Thanks.
ps. OS is SunOS 5.8
|
by: dick |
last post by:
I can't seem to get AutoExec to run a function. Per an article on this
group, I:
1) built a boolean function in a module that simply issues a MsgBox,
then returns True
2) in AutoExec macro, I wrote a condition "onOpen()=False" including
the dbl quotes. For the action I put out another MsgBox saying 'onOp is
False'.
The 2nd MsgBox always appears. I put a breakpoint in the function. It
never triggered, so my function is not being called.
| |
by: Bill |
last post by:
This database has no forms. I am viewing an Access table in datasheet
view. I'd like to execute a macro to execute a function (using
"runcode"). In the function, I'll reading data from the record the
cursor was on in the datasheet at the time I executed the macro.
So, the questions are: 1) In the macro, how to I get my hands on the
record key or record data of the record the cursor was on in the
datasheet at the time I executed the...
|
by: amit.man |
last post by:
Hello
i need to write a MACRO function that look something like this
"""
do_somthing();
for(int i=0;i<100;i++) {do_something_else())}
return(somthing_other_then_those()) // a double type
"""
|
by: harshal |
last post by:
Hi all,
Can we read the stack frame's of the current process.
as we know that whenever a function call is made in c new functions
stack frame
is created and pushed on to the stack. and when the function returns
it is popped out from the stack. i want to know the caller functions
name.
i mean i want something like this
|
by: ElPresidente |
last post by:
Hi everyone,
I had a macro I wrote in VBA which created several dozen spreadsheets
based off a master spreadsheet. Code runs smoothly in VBA, but trying
to port it over to VB caused significant performance issues. I decided
the best way would be to just run the VBA macro from VB.
The problem I'm having is that the macro takes some time to run and
there's no indication of progress on the VB form I created. I would
like to write...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |