+ Reply to Thread
Results 1 to 12 of 12

vba error ambiguous name detected: RangetoHTML

  1. #1
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    vba error ambiguous name detected: RangetoHTML

    I am having an this error message come up: vba error ambiguous name detected: RangetoHTML, when I attempt to use the same code to create a email macro on the same sheet.
    I use a ActiveX control button with this code. I need both macros on the same sheet/tab as one email is a review email and the second email is to create the final email. I used the exact same code for both macros.

    From googling, I understand issue roots from the second use of the function needing to be named something different from the function used in the first macro--> which is where my question lies: how can resolve the ambiguous name detected error message with RangetoHTML?
    I have tried to rename the function myself, but that did not let the rest of the code work.
    Much help appreciated for a beginner in macros.

    Error occurs where: Function RangetoHTML_SelectionChange(rng As Range)
    [only second part of my code was pasted since part i could not submit]
    Last edited by defaultdefault; 12-06-2021 at 04:22 PM.

  2. #2
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    Re: vba error ambiguous name detected: RangetoHTML

    Function RangetoHTML_SelectionChange(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    'testing out replacing RangetoHTML with M to resolve ambiguous compile error.
    M = ts.readall
    ts.Close
    M = Replace(M, "align=center x:publishsource=", _
    "align=left x:publishsource=")
    RangetoHTML = M

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: vba error ambiguous name detected: RangetoHTML

    vba error ambiguous name detected: RangetoHTML … basically says you have more than one function called RangetoHTML somewhere in your VBA Project.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    Re: vba error ambiguous name detected: RangetoHTML

    How can I resolve that issue? while still retaining the function RangetoHTML? is there a way to make the code the same way with changing the name of RangetoHMTL? Help much appreciated!

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: vba error ambiguous name detected: RangetoHTML

    Find them both then, if they are the same, just delete one.
    Rory

  6. #6
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    Re: vba error ambiguous name detected: RangetoHTML

    I Need both sets of them as each one creates an email for different recipients. I need the two email macros to retain their functionality yet I need to have one of the range functions renamed-- how can this be done?

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: vba error ambiguous name detected: RangetoHTML

    You've misunderstood the code. RangeToHTML converts a range (that you pass as an argument, so the function is generic and does not need to be repeated) to HTML that can then be added to an email. It does not have anything to do with creating the email, so you do not need two copies of it.

  8. #8
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    Re: vba error ambiguous name detected: RangetoHTML

    I currently have the two sets of code separated under two different private sub command buttons'. If I delete the rangetohtml for the second macro -- how will it know to create the email with the chart in the range?

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: vba error ambiguous name detected: RangetoHTML

    Suggest you post a desensitised workbook with ALL the code so it can be seen in context.


    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: vba error ambiguous name detected: RangetoHTML

    As I mentioned, RangetoHTML is a generic function that converts the supplied range to HTML code. If it's called by two routines, all they need to do is supply different ranges as required. The RangeToHTML function does not create an email. So, one of your routines might call RangeToHTML(Range("A1:E20")) and use the returned HTML for one email, and the other might call RangeToHTML(Range("Z500:AG534")) and use that HTML for a different email, as an example.

  11. #11
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,223

    Re: vba error ambiguous name detected: RangetoHTML

    In post#2, the function declaration is strange:
    Please Login or Register  to view this content.
    However, the result is assigned to RangetoHTML:
    Please Login or Register  to view this content.
    Artik

  12. #12
    Registered User
    Join Date
    10-08-2021
    Location
    USA
    MS-Off Ver
    MS Office 11
    Posts
    7

    Re: vba error ambiguous name detected: RangetoHTML

    Below is the final code. I was blocked by my company internal site from uploading the document I created as a sample.
    This code is the first email 'review email' I am trying to replicate for another final email macro. the final macro looks the same apart from the 'To' and 'Subject'. My question is: how can I have correct the code to use the function RangetoHTML without the ambiguous error?
    I am not allowed to also paste code into the message thread unless i break up the code. So I am breaking up the second part of the code [the rangetohtml function] from the first part of the create email code in order to post it on this website.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need Help PLEASE Ambiguous name detected "RangetoHTML
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2021, 03:22 PM
  2. [SOLVED] Compile Error: Ambiguous name detected
    By trubio77 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-19-2017, 02:26 PM
  3. Compile Error : Ambiguous name detected
    By vasanthrs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2013, 09:58 AM
  4. VBA Compile Error: Ambiguous name detected
    By kieranoduill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2013, 05:31 PM
  5. [SOLVED] compile error ambiguous name detected!?
    By Margate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2013, 08:49 PM
  6. Ambiguous name detected error
    By jpxexcelforum in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-07-2011, 06:04 PM
  7. Compile error: Ambiguous name detected
    By Aletha in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2008, 05:48 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1