Calling an Excel Function

I'm attempting to design a program that can call upon an Excel function, perform a find and replace, and then save the excel function under a specific, user entered name. Unfortunately, I am only knowledgeable in basic C++, which is unable to deal with Excel functions. I was wondering if anyone could give me some pointers on if and how this can be accomplished using Visual Basics.

Hi LuckyDrawers17 and welcome to the forum! :)

Please be sure to read the posting guidlines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

I'm sure that we'll be able to give you lots of pointers on this! So that we can give you the relevant information, it'd be a big help to us if you could expand a little on the outline of your project and, in particular, what you mean by 'Excel function'?

Also, it'd be great if you could confirm if you are looking to complete this project using VB .Net, VB6 or Excel VBA?

Colin

This is the program I have written in Excel VBA so far. It is done by a created macro. My main necessity is to be able to replace the bolded TEST words with user inserted values. I would like the program to request values that can then be entered by the user. Any help would be much appreciated...

Workbooks.Open Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheets\P4 thru P9\1-New Recipe Sheet P4-P9 New.xls" _
, UpdateLinks:=3
Cells.Replace What:="$3", Replacement:="$TEST", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Workbooks.Open Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheet Creator.xls"


Windows("1-New Recipe Sheet P4-P9 New.xls").Activate
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheets\P4 thru P9\TEST.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Windows("New Recipe Sheet Creator.xls").Activate
ActiveWindow.Close

The main idea of the program is to open an excel file, perform a find and replace with a user entered values, then save the file to a user entered name, and finish by closing the file. Sounds simple, but I'm having trouble with user entered numbers.

Hi LuckyDrawers17,

Look up the application object's inputbox method in your VBA helpfile.

You can assign the user's input into a string variable and then pass that variable into the relevant argument or concatenate it into a string.

Colin

Alright, so here's where I'm at so far

Sub RecipeCreator()
'
' Keyboard Shortcut: Ctrl+x
'
Dim lngSaveName
Dim lngReferenceNumber As Double
lngSaveName = Application.InputBox( _
Prompt:="Enter Name of Recipe", _
Title:="Enter Name of Recipe", _
Type:=2)
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter Row Number on Master Sheet", _
Title:="Enter Row Number on Master Sheet", _
Type:=1)
Workbooks.Open Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheets\P4 thru P9\1-New Recipe Sheet P4-P9 New.xls" _
, UpdateLinks:=3
Cells.Replace What:="3", Replacement:=lngReferenceNumber, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWorkbook.SaveAs Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheets\P4 thru P9\TEST.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

End Sub


I'm looking to take the user inputted value from the first inputbox, lngSaveName, and insert that into the save name where TEST is currently

I would suggest the following amendments:

Amend this declaration to declare the variable as a string:

Dim lngSaveName
'amend to
Dim strSaveName as String


and then just concatenate the value of the variable:

ActiveWorkbook.SaveAs Filename:= _
"P:\Recipe Sheets Intern\Recipe Sheets Intern\New Recipe Sheets\P4 thru P9\" & strSaveName & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


And another thought, why give a double variable an "lng" prefix? Pretty confusing? You only need a long variable here:
Dim lngReferenceNumber As Double
'amend to
Dim lngReferenceNumber as Long

Colin

Thanks Colin, the program is working incredibly...The only change I would like to make is making the font of some of the words in the input box bolded. The code I currently have is:


Dim strSaveName As String
Dim lngReferenceNumber As Long
Dim strSafety As String
Do
strSaveName = Application.InputBox( _
Prompt:="Enter Name of New Recipe", _
Title:="New Recipe Creator", _
Type:=1 + 2)
If strSaveName = "False" Then ActiveWorkbook.Close
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter Row Number of New Recipe on Recipe Master Sheet", _
Title:="New Recipe Creator", _
Type:=1)
If lngReferenceNumber = "0" Then ActiveWorkbook.Close
strSafety = Application.InputBox( _
Prompt:="Are You Sure You Want To Create New Recipe " & strSaveName & " from Row " & lngReferenceNumber & " of Recipe Master Sheet (Y/N)", _
Title:="Double Check", _
Default:="N")
If strSafety = "False" Then ActiveWorkbook.Close
If strSafety = "Yes" Then strSafety = "Y"
If strSafety = "y" Then strSafety = "Y"
If strSafety = "yes" Then strSafety = "Y"
Loop Until strSafety = "Y"


I'm looking to make the user inputted values (strSaveName and lngReferenceNumber) bolded in the third input box

Hi LuckyDrawers17,

Great to see you're making such good progress! :cool:

Changing the font of the inputbox isn't going to be an easy thing to do (if it can be done at all), and I do not know how to do it natively within Excel.

If you really want this effect then, instead of using an inputbox, you could create a userform with a label and commandbuttons on. You could then customise the formatting of the label as you require.

If you decide against going for a bold font, you might find that for the double check, you'd prefer to use just a simple messagebox, as in:

If MsgBox("Are you sure?", vbOKCancel, "Double Check") = vbOK Then
Debug.Print "The user pressed OK"
Else
Debug.Print "The user pressed Cancel"
End If

Colin