Write a vba code for generate v ai like chatgpt. it give all responses in excel.don't…
- User
Write a vba code for generate v ai like chatgpt. it give all responses in excel.don't show any source data . if want to show source press alt+k to show source . get data from web but not show any website. first question ask what is your name. if enter name. then ask what can i do for you. if want to quit then just type i want to quit
Assistant
Here's a complete VBA solution for your Excel-based AI assistant that meets all your requirements:
' Module Code (Insert into a new Module) Option Explicit Dim SourceSheet As Worksheet Dim UserName As String Dim ResponseCount As Long Sub CreateVAIButton() Dim btn As Shape Dim ws As Worksheet Set ws = ActiveSheet ' Remove existing button if any On Error Resume Next ws.Shapes("VAIButton").Delete On Error GoTo 0 ' Create button in center of screen Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, _ (ws.UsedRange.Width - 100) / 2, _ (ws.UsedRange.Height - 50) / 2, _ 100, 50) btn.Name = "VAIButton" With btn.TextFrame2.TextRange .Text = "V AI" .Font.Bold = True .Font.Size = 14 End With btn.TextFrame.HorizontalAlignment = xlHAlignCenter btn.TextFrame.VerticalAlignment = xlVAlignCenter btn.Fill.ForeColor.RGB = RGB(0, 112, 192) btn.Line.ForeColor.RGB = RGB(0, 0, 0) btn.OnAction = "StartVAI" ' Initialize response counter ResponseCount = 1 End Sub Sub StartVAI() ' Clear previous responses if any If ResponseCount > 1 Then If MsgBox("Clear previous conversation?", vbQuestion + vbYesNo) = vbYes Then ActiveSheet.UsedRange.ClearContents ResponseCount = 1 End If End If ' Get user name UserName = InputBox("What is your name?", "V AI") If UserName = "" Then Exit Sub ' Start conversation Dim userInput As String Do userInput = InputBox("Hello " & UserName & ", what can I do for you?" & vbCrLf & _ "(Type 'i want to quit' to end)", "V AI") If LCase(Trim(userInput)) = "i want to quit" Then Exit Do If userInput = "" Then Exit Do ' Get and display response Dim aiResponse As String aiResponse = GetAIResponse(userInput) With ActiveSheet .Cells(ResponseCount, 1).Value = UserName & ": " & userInput .Cells(ResponseCount, 2).Value = "V AI: " & aiResponse ResponseCount = ResponseCount + 1 .Columns("A:B").AutoFit End With ' Log source data LogSource userInput, aiResponse Loop End Sub Function GetAIResponse(query As String) As String On Error GoTo ErrorHandler ' Use Wikipedia API for demo purposes Dim url As String url = "https://en.wikipedia.org/w/api.php?action=query&format=json&prop=extracts&exintro&explaintext&redirects=1&titles=" & _ Replace(Trim(query), " ", "%20") Dim http As Object Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.send If http.Status = 200 Then Dim jsonResponse As String jsonResponse = http.responseText ' Simple JSON parsing (for demo) Dim extractStart As Long extractStart = InStr(jsonResponse, """extract"":""") If extractStart > 0 Then Dim extractEnd As Long extractEnd = InStr(extractStart + 9, jsonResponse, """}") If extractEnd > extractStart Then GetAIResponse = Mid(jsonResponse, extractStart + 9, extractEnd - extractStart - 9) GetAIResponse = Replace(GetAIResponse, "\n", vbCrLf) If Len(GetAIResponse) > 0 Then Exit Function End If End If End If ' Fallback response if API fails GetAIResponse = "I found some information about " & query & ". " & _ "For more details, you might want to search online." Exit Function ErrorHandler: GetAIResponse = "I'm having trouble connecting to my knowledge base right now. " & _ "Please try again later or ask something else." End Function Sub LogSource(query As String, response As String) If SourceSheet Is Nothing Then On Error Resume Next Set SourceSheet = ThisWorkbook.Sheets("Sources") On Error GoTo 0 If SourceSheet Is Nothing Then Set SourceSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) SourceSheet.Name = "Sources" SourceSheet.Visible = xlSheetVeryHidden SourceSheet.Range("A1:C1").Value = Array("Timestamp", "Query", "Response") End If End If With SourceSheet Dim nextRow As Long nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 .Cells(nextRow, 1).Value = Now .Cells(nextRow, 2).Value = query .Cells(nextRow, 3).Value = response End With End Sub Sub ShowSources() If SourceSheet Is Nothing Then MsgBox "No conversation history available.", vbInformation Exit Sub End If If SourceSheet.Visible = xlSheetVisible Then SourceSheet.Visible = xlSheetVeryHidden MsgBox "Sources hidden.", vbInformation Else SourceSheet.Visible = xlSheetVisible SourceSheet.Activate MsgBox "Sources shown. Press Alt+K again to hide.", vbInformation End If End Sub ' ThisWorkbook Code (Insert into ThisWorkbook object) Private Sub Workbook_Open() Application.OnKey "%k", "ShowSources" ' Alt+K to show/hide sources ' Create button automatically when workbook opens CreateVAIButton End SubFeatures:
-
Easy Setup:
- Creates a "V AI" button automatically when workbook opens
- Button is centered on your worksheet
-
Conversation Flow:
- Asks for your name first
- Then asks "What can I do for you?"
- Type "i want to quit" to end the conversation
-
Responses:
- Displays conversation in Excel (your questions and AI responses)
- Formats responses neatly in columns
- Automatically adjusts column widths
-
Source Data:
- All sources are hidden by default
- Press Alt+K to show/hide the sources sheet
- Sources include timestamps for each query
-
Error Handling:
- Graceful fallback responses if API fails
- Handles empty inputs properly
How to Use:
- Copy the module code into a new VBA module
- Copy the Workbook_Open code into the ThisWorkbook object
- Save as macro-enabled workbook (.xlsm)
- The "V AI" button will appear automatically
- Click it to start chatting
Note: This uses Wikipedia's API for demonstration. For a more ChatGPT-like experience, you would need to connect to an actual AI API (like OpenAI's), which would require an API key. The current implementation will work without any external dependencies.
Would you like me to modify any part of this implementation?
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.